Import unknown multiple Excel worksheets into Access

[deejay_totoro]

Hello,

I would like to import a spreadsheet that contains a number of woksheets, into separate Access tables.

I don’t know what the field names will be on the spreadsheets.

My question is: does anyone know if it is possible to import an unknown number of worksheets into separate tables in an Access database without knowing the field names? (and without the tables already existing.)

Many thanks,

dj_T

 

[Guus2005]

Yes, you can.

Create an Excel object and open the workbook.

    Dim appExcel as Excel.Application
    Set appExcel = New Excel.Application
    With appExcel
        .Visible = blnVisible
        Set wbExcel = .Workbooks.Open("c:\temp\someExcelFile.xls")
    End With

Now you can list all worksheets and in each worksheets, each Cell.
Or just get the contents of cell A1 of the first Sheet:

    Dim wsExcel as Worksheet
    Dim strCellA1 as string

    Set wsExcel = wbExcel.Worksheets(0) 'first worksheet
    strCellA1 = ws.cells(1,1) 'A1

Enjoy!

 

[The_Doc_Man]

Guus is absolutely right. However, it might help you to know what you are looking at when you do this. Go into Excel (not Access) and look up the help files. Find the "Programming with VBA" help and look up "Collections" to see the structure that will be visible with an Excel Application Object. This discussion, though under Excel help, also applies to Access VBA environments.