vbaXray is a single VBA class module that extracts VBA source code straight out of Office files.

I posted about v1.0 a few months back, with an update a few weeks back outlining improved performance and file format support, and now the current version addresses that annoying elephant in the room - FRX files.

Now, just a short note to let you know that vbaXray now exports valid, importable FRX files alongside their corresponding FRM sibling files. Both files are required for importing Userforms into projects.

And for anyone with a burning desire to know what projects are referenced in a file, this information is now available too (except for Access files, at present).

Sub XrayDemo()   
  Dim xray As New clsVBAXray   
  If xray.LoadFromFile("C:\ShowMeTheCode\ThisIsYourWorkbookName.xlsm") Then       
    Debug.Print "Project: " & xray.ProjectName     
    Debug.Print "Modules: " & xray.ModuleCount     
    xray.ExportAll "C:\OutputCodeHere\ExtractedCode\"     
    xray.DebugDumpStorageTree   
  Else     
    Debug.Print "Load failed: " & xray.LastError   
  End If 
End Sub  

I've also applied various fixes to make exported modules/classes from Access files actually importable again.

The code, some basic documentation, and a (very simple) demo workbook are already on GitHub:

https://github.com/KallunWillock/vbaXray/

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")

Hi r/vba,

Interestingly, if you password-protect a VBA project but not the database itself, only Access will ask for that password. But the module texts won't be encrypted, and you can extract them using the thirdparty library.

I recently updated my online database comparison tool and added Access support. You can compare VBA of forms, reports, and modules. You can also compare table definitions, queries, macros, and table data. And yes, if you don't have a password for the database but do have a password for the VBA project, you don't need it.

Everything works entirely in the browser. Uploaded files are only stored in the page's memory and never go to the server. Basically, after opening the page, you can disconnect from the internet and it will still work. Access is not required, works on Windows, Linux, and Mac. A side benefit is that you can open the A97 mdb format, which is not even supported by recent Office versions.

A huge thanks to the jetdb project and its predecessors. To get this all working, I had to make several fixes. They're all available in my fork, and if the author allows, they'll be merged into the main project; the first pull request is awaiting.

AI usage - intensive (for code, not for this post). The previous .NET UNO-platform-based version for SQLite was too heavy, and I wanted to rewrite it using something more compact for a long time. But I spent pretty much time on reviewing and testing the changes. To verify some of the fixes, I even had to find a Win98 image with Access 97 to ensure the fixes were valid for its mdb format. I haven't seen Clippy for about 25 years!

Link: https://ksdbmerge.tools/for-msaccess-online

I'd be happy if this will be useful for anyone.