I'm developing a script in VBA (inside a workspace, not MS office program) that needs to use a small excel file as input. I want to use the data in the excel file and load it into a 2D array so that I can make decisions within my script based on this data. I've tried to google this problem but generally I find problems within excel, but I haven't had any luck finding anything for vba scripting outside MS office.
Can I just use a .Xlsx as inputfile? How do I put this data into a 2d array? My file only has 11 rows & 2 columns.
Thank you for your time.
Best How To :
I think this will also work for Visual Basic [for Applications]; I tried it in Excel VBA and Word VBA:
In VBA Development Environment, select Tools, References. In the list that now appears, check the Object Model you need. In your case you will want to check the Microsoft Excel 12.0 Object Library.
You now have access to the complete Excel Object Model in your application and can open and manipulate an Excel spreadsheet file as you need.
The following code in VB for Word opens an Excel Spreadsheet and gets the value of the first cell:
Dim oExcel As Object
Dim wb As Object
'Declare a variable as a FileDialog object.
Dim fd As FileDialog
Dim vrtSelectedItem As Variant
Set oExcel = CreateObject("Excel.Application")
'Create a FileDialog object as a File Picker dialog box.
Set fd = Application.FileDialog(msoFileDialogFilePicker)
If (fd.Show = -1) Then
For Each vrtSelectedItem In fd.SelectedItems
Set wb = oExcel.Workbooks.Open(vrtSelectedItem)
v = wb.Sheets(1).Cells(1).Value