Rename Table Name to First Column Heading

[jadown]

I am importing different excel sheets into Access dB using a file dialog. The importing works fine however, I would like to rename the tables once they are imported to the name of the first column heading. Where exactly would I ad the name change at in this code?

#' Open the EXCEL file and read the worksheet names into a collection
Set colWorksheets = New Collection
Set objWorkbook = objExcel.Workbooks.Open(StrFileName, , blnReadOnly, , _
strPassword)
For lngCount = 1 To objWorkbook.Worksheets.Count
colWorksheets.Add objWorkbook.Worksheets(lngCount).Name

Next lngCount
' Close the EXCEL file without saving the file, and clean up the EXCEL objects
objWorkbook.Close False
Set objWorkbook = Nothing
If blnEXCEL = True Then objExcel.Quit
Set objExcel = Nothing
' Import the data from each worksheet into a separate table
For lngCount = colWorksheets.Count To 1 Step -1
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, _
"tbl" & colWorksheets(lngCount), StrFileName, blnHasFieldNames, _
colWorksheets(lngCount) & "$"#

 

[spikepl]

How about explaining what all this is about in layman's terms?

Your desire smells fishy in db terms. If you need to rename that means the first column name varies and thus is data rather than a fixed feature. Renaming tables after instances of data is a nono, because it encodes data into structure and violates normalization rules (which in turn means additional and superfluous work in creation, maintenance and updates).

 

[jadown]

I'll see if I can explain it better. Ok the user will import worksheets that will create new tables every time. The thing is that each import will be different and instead of having the table name to be the same as the tab I want the table name to be the same as the first column heading.

Is it possible to open the Excel file as the code does read the worksheets and change the name of the tabs, then import them into the DB? Or will I have to format the names of the worksheets save and then import them?

I hope this makes sense.