rename Excel columns and import

[qwertyjjj]

I have a spreadsheet that needs to be regularly imported into Access. Unfortunately some of the columns are called 1 - 10, 10 - 20, etc.

Access doesn't seem to like these because of the hypen -.
So, in the Access database I have named these columns 1To10, 10To20, etc.

Is there a way to either rename the Excel columns or map columns while importing into Access ?

 

[Pat Hartman]

You can link to the spreadsheet and create an append query.

 

[qwertyjjj]

Okay, that's an option.
How do you link to spreadsheets from VBA code ?

And, out of interest, what happens if the spreadsheet is deleted at a later date, will the links remain in the Access database?

 

[Ron_dK]

How do you link to spreadsheets from VBA code ?

From VB help :
DoCmd.TransferDatabase acImport, "Microsoft Access", _
"C:\My Documents\NWSales.mdb", acReport, "NW Sales for April", _
"Corporate Sales for April"

The next example links the ODBC database table Authors to the current database:

DoCmd.TransferDatabase acLink, "ODBC Database", _
"ODBC;DSN=DataSource1;UID=User2;PWD=www;LANGUAGE=us_english;" _
& "DATABASE=pubs", acTable, "Authors", "dboAuthors"

The following example exports the contents of the Customers table to a new list named Customer List on the "http://example/WSSSite" Windows SharePoint Services site.

DoCmd.TransferDatabase transfertype:=acExport, databasetype:="WSS", _
databasename:="http://example/WSSSite", _
objecttype:=acTable, Source:="Customers", _
Destination:="Customer List", structureonly:=False

And, out of interest, what happens if the spreadsheet is deleted at a later date, will the links remain in the Access database?

If you delete the spreadsheet, the link is broken and you will not be able to read data from the original spreadsheet.

 

[qwertyjjj]

Thanks. Works well !

 

[qwertyjjj]

If I have multiple worksheets, what's the best way of getting each one?
Do I specify a range or can I name the worksheets directly ?
Code so far:
DoCmd.TransferSpreadsheet acLink, acSpreadsheetTypeExcel97, "Tester", "C:\Downloads.xls", True

 

[Ron_dK]

add something like :


Dim filepath As String
Dim Filename As String
Dim sheetname As String
Dim lngRows As Long

filepath = "C:"
Filename = "myFile.xls"
sheetname = "sheet1"
lngRows = GetXlRows(filepath, Filename, sheetname)

 

[WrHn85]

thanks, rak - you might be getting me out of import table hell (Excel2003-Access2000)