CODEWITHSUNDEEP
vbascripting
Witloofdc
Witloofdc

Reputation: 81

Using excel as inputfile in vba (scripting - non MS OFFICE)

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.

Upvotes: 0

Views: 159

Answers (3)

omegastripes
omegastripes

Reputation: 12612

Consider the VBScript code below as an example, save it as .vbs file to launch:

Option Explicit
Dim strSourceFilePath, strSourceSheetName, strSourceRange, arrData, strResult, i, j

' set initial data
strSourceFilePath = "C:\Test\data.xlsx"
strSourceSheetName = "input"
strSourceRange = "A1:B11"

' get 2-dimensional array from Excel sheet
With CreateObject("Excel.Application")
    .Visible = False
    .DisplayAlerts = False
    With .Workbooks.Open(strSourceFilePath)
        arrData = .Sheets(strSourceSheetName).Range(strSourceRange).Value
        .Close
    End With
    .Quit
End With

' array output for debug purposes
strResult = ""
For j = 1 To UBound(arrData, 1)
    For i = 1 To UBound(arrData, 2)
        strResult = strResult & arrData(j, i) & ";" & vbTab
    Next
    strResult = strResult & vbCrLf
Next
WScript.Echo strResult

Upvotes: 0

Paul Ogilvie
Paul Ogilvie

Reputation: 25286

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:

Sub ExcelTestInWord()
    Dim oExcel As Object
    Dim wb As Object
    Dim v

    '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
            wb.Close
        Next vrtSelectedItem
    End If

End Sub

Upvotes: 0

vacip
vacip

Reputation: 5416

This is confusing. VBA = MS Office VBA, as far as I know. Do you mean VB.NET? VBS? Anyway, to extract data from an xlsx file without opening it in Excel, you either need to unzip it and dig through the XML files inside, or use Office Open XML SDK or any other package like that.

Open XML SDK

Office Open XML fileformat

Upvotes: 1

Related Questions