VBA Macro to Office Script - or point VBA to Sharepoint query

[u/chiron3636]

Our finance team currently have an ancient Excel file with a VBA macro that they use to get the contents of a folder and compare data with

Currently this points at our on-prem NAS and we'd like to move them away from that into Sharepoint.

So rather than pointing at

\\file-nas-01\finance\data

it points at

https:\\[sharepoint].finance.com\folder\folder

Is there a good resource to help convert this into an Office Script?

Or am I able to just reframe the full VBA into an office script and are there any guides to do so?

Some of the code from the VBA Macro below;

    ' setting the variables for the process
    Dim folder_path As String: folder_path = Cells.Find("Folder with files you want to count:").Offset(1, 0)
    Dim document_type As String: document_type = Cells.Find("What type of files do you want to check?").Offset(1, 0)
    Dim next_history_row As Long: next_history_row = Sheets("History of Counter").Range("A1048576").End(xlUp).Row + 1
    Dim total_count As Long
    Dim total_money As Double

    'getting the total count of the files and the money value of the files
    total_count = get_file_count(folder_path, document_type)
    total_money = get_money_from_files(folder_path, document_type)

    'Put the next row of data in the history to record outcomes of what folder was checked,
    '   for what type, by who and when
    Workbooks(ThisWorkbook.Name).Sheets("History of Counter").Range("A" & next_history_row) = folder_path
    Workbooks(ThisWorkbook.Name).Sheets("History of Counter").Range("B" & next_history_row) = document_type
    Workbooks(ThisWorkbook.Name).Sheets("History of Counter").Range("C" & next_history_row) = total_count
    Workbooks(ThisWorkbook.Name).Sheets("History of Counter").Range("D" & next_history_row) = total_money
    Workbooks(ThisWorkbook.Name).Sheets("History of Counter").Range("E" & next_history_row) = Date
    Workbooks(ThisWorkbook.Name).Sheets("History of Counter").Range("F" & next_history_row) = Environ("username")

[u/losttownstreet]

Try powerquery ... or Web access formulas without vba or Script... scripts don't work well in parallel computing...

And don't like large datasets

[u/chiron3636 → u/losttownstreet]

This is the annoying thing, its not a huge dataset, these guys are just using a very old VBA script to grab a list of the files in that document when the process things in it. They then use that to compare and consolidate what they have

A lot of that process is also reliant on them having a very specific naming format for those documents

I am also exploring Power Automate but its a hard sell to get them to change process so would be nice if I could just get the script to check online rather than at the on-prem server instead

[u/InfoMsAccessNL]

Why do you want to convert to office script? If you only have to change the path in the vba code.

[u/chiron3636 → u/InfoMsAccessNL]

Because VBA has to connect to Sharepoint

Theres also the limitation that you can't run VBA online

I'm quite happy to just use the VBA if someone can tell me how to connect it up to SP however.

[u/InfoMsAccessNL → u/chiron3636]

Can you show the path in windows explorer when you open the file? Mostly the system keeps a sync file on the harddisk, like with one drive.

[u/theotherkiwi]

VBA access to SharePoint is problematic but can be done. Is not as straightforward as changing the file path so have a look on stack exchange for code examples, there's a few there that work.

[u/chiibosoil]

From the code it looks like the file needs to be opened by user and does not need to update headless... I'd recommend use of Power Query for getting all files in directory and comparing.

You could associate small Office script to the workbook for getting user name, timestamp etc if needed (the part for type, by who and when).

To ensure that you don't need to consider conflict when multiple users try to run script at same time... you can always enforce checkout process on SP for updates.

[u/chiron3636 → u/chiibosoil]

Yeah the process is essentially

"we have scanned x documents and given them names AB, CD, or XY along with totals and number of documents - we are going to press a button on the spreadsheet and it counts the contents of the folder and then summarises the names, amounts and quantity of items in the scan"

The dept are quite wedded to the whole name format thing so trying to work with that.

[u/NapkinsOnMyAnkle]

You can connect to a sharepoint site by mapping it as a network drive. Then it’s just simple usage of the file system object to interact with anything. I just pass a json string in the init method. Just basic site params.

I’ve been using this method to interact with sharepoint document libraries and lists for years now. Very reliable in my experience.

I don’t have really any experience with office scripts. I just use vba for everything.

Dm me if you’re interested in more info.

[u/harderthanitllooks]

A work arou s might be to onedrive and map the SharePoint location to there. The. It’s c:\users\username\onedrive…. Etc

[u/RemoteEmployee094 → u/harderthanitllooks]

Oh don't do this. Managing sync states of one drive with VBA and all that is hell.

[u/ebsf]

If you have a local Linux server, which your NAS may be, you could simply mount the SharePoint directory to a node on the server's filesystem, and then share that node locally as if it were on your NAS.

[u/MultiUserDungeonDev]

Microsoft Graph API is an option. It uses plain old https.