Import Spreadsheet with multiple tabs into Access

[vusi_dube]

As it says on the tin. Need some help getting these into Access from an excel spreadsheet (located on sharepoint). I'm using the spreadsheet fields to create the table fields in Access.
Thanks in advance.

 

[DavidAtWork]

Is the source spreadsheet always going to be in a consistent format ie same number of tabs, always with the same sheetname and consistent number and name of fields OR is this likely to be variable

David

 

[vusi_dube]

Is the source spreadsheet always going to be in a consistent format ie same number of tabs, always with the same sheetname and consistent number and name of fields OR is this likely to be variable

David

Hey David,

Yes they will be, consistent headers tab names etc. The only thing that changes is the spreadsheet names.

Thanks

 

[DavidAtWork]

I suggest initially you set up a table with all the sheet tab names (exactly matching as in the source spreadsheet) and this can be used to loop through a recordset of the sheet tab names and use the
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, sheetTabName, _
vaFileName, False, sheetTabName

vaFileName is a variable that holds your the name of the file you are using (you can use the Application.FileDialog(msoFileDialogFilePicker) to get the user to browse for the spreadsheet file)
sheetTabName is the variable that holds the name of the current tab name from your recordset collection and you can use it also to name the table
Hope this all makes sense

David

 

[vusi_dube]

I suggest initially you set up a table with all the sheet tab names (exactly matching as in the source spreadsheet) and this can be used to loop through a recordset of the sheet tab names and use the
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, sheetTabName, _
vaFileName, False, sheetTabName

vaFileName is a variable that holds your the name of the file you are using (you can use the Application.FileDialog(msoFileDialogFilePicker) to get the user to browse for the spreadsheet file)
sheetTabName is the variable that holds the name of the current tab name from your recordset collection and you can use it also to name the table
Hope this all makes sense

David

Dave,

I've created the tables with the exact field names etc But i have two problems

1. The field names have spaces(Thou shalt name thine fields with no spaces etc) and I'd like them to map to the excel ones as part of the code that loops and brings through the data. I have added "_" in my tables to combat the spaces issue but have created a mapping issue as result. Unfortunately I can not change the format of field names at source

2. Been trying to write the code to loop through (i'm rubbish at writing code) would write it for me?

Kind Regards,

Vusi