how to import an excel file into a table in access using vbs?

[barny2006]

hi,
i have an app that requires importing an excel table into access as a table. currently we do this using access app and menu-import. is there anyway to do this using vb script?
thanks!

 

[Remou]

You can use

Docmd.TransferSpreadsheet

 

[CautionMP]

barny2006,
Short answer is yes, you can use VBS to make a table in Access using Excel as your source data.

You can create the recordset object in VBS, just like VBA, so you can choose between the ADO or DAO object model (keep in mind that DAO is optimized for the JET DB engine.)

A good starting point would be search the forums for [tt]ADODB.Recordset[/tt] or [tt]DAO.Recordset[/tt], this should give an idea of how the routine needs to be structured and would give you a starting point on converting it to VBS.

Hope this helps,
CMP

[small]For the best results do what I'm thinking, not what I'm saying.[/small]
(GMT-07:00) Mountain Time (US & Canada)

 

[barny2006]

thanks mp. i tried this code. but it's getting an error:

Set objConnection = CreateObject("ADODB.Connection")
  
objConnection.Open _
   "Provider = Microsoft.Jet.OLEDB.4.0; " & _
   "Data Source = c:\a_temp\db1.mdb"

DoCmd.TransferSpreadsheet acImport, 5, "zzz", "c:\a_temp\november.xls", false

 

[barny2006]

i need to use vbs not vba.

 

[Remou]

I think that there is a difference between CautionMP's idea and mine, so the two cannot be mixed. I was thinking on the lines of:

Option Explicit
'ImportXL.vbs

Const conDatabaseToOpen = "C:\Docs\Tek-Tips.mdb" 
Const conXLFileToImport = "C:\Docs\Tek-Tips.xls"

Dim AcApp 

Set AcApp = CreateObject("Access.Application")
AcApp.Visible = True
AcApp.OpenCurrentDatabase conDatabaseToOpen
'0=acImport
'9=acSpreadsheetTypeExcel9
AcApp.DoCmd.TransferSpreadsheet 0, 8, "XLTable", conXLFileToImport
AcApp.UserControl = True

But this is, as it were, a cross between VBA and VB Script. Do you wish to avoid using Access altogether?

 

[barny2006]

thanks remou
that's right. i want to avoid using access altogether. since this will be done inside the script. i have got the code to work. it's like this:

commandstring = "SELECT * INTO newtbl FROM [Excel 8.0;HDR=Yes; Database=c:\a_temp\newhear.xls].sheet1"       
objConnection.Execute commandstring

but the sheet1 inside the excel is not being found. if i use another excel file, and if the sheet is sheet1, it works. but in this instance, it says can't find the object sheet1 inside the excel file.

 

[barny2006]

i guess, where i'm stuck, is how do you find out what the "true" name of an excel sheet is?

 

[Remou]

I think the sheet may be in the wrong place. Have you tried (?):

[tt]commandstring = "SELECT * INTO newtbl FROM [Sheet1$] IN '' 'Excel 8.0;database=c:\a_temp\newhear.xls';"[/tt]