ADODB connection question

[Paulsburbon]

I found code on the net for importing Excel to Access. The problem is it is code for the excel spreadsheet. I need it to be executed in access. So the following code in the spreadsheet of:

Dim cn As ADODB.Connection
Set cn = New ADODB.Connection
cn.Open "Provider=Microsoft.Jet.OLEDB.4.0; " & _
"Data Source=C:\FolderName\DatabaseName.mdb;"

What would change for me to connect to an excel spreadsheet. The file would be called spreadsheet with the path of C:\spreadsheet?

Does this make any sense?

Thank you for your time.

 

[pbaldy]

I've never tried to connect to Excel using ADO, but here's a good resource for connection strings and it includes Excel:

www.connectionstrings.com

 

[reclusivemonkey]

What would change for me to connect to an excel spreadsheet. The file would be called spreadsheet with the path of C:\spreadsheet?

Does this make any sense?

Thank you for your time.

Paul,

Is using the Access Import / Link feature an option? You don't have to do this with code.

 

[tkpstock]

You can also open the excel sheet and access the data using the excel DOM.

Dim oXL as New Excel.Application
Dim oWB as Excel.Workbook
Dim oWS as Excel.Worksheet
Set oWB = oXL.Workbooks.Open("C:\My Workbook.xls")
Set oWS = oWB.Sheets("My Sheet")

etc...

Or you can use the the following:

   DoCmd.TransferSpreadsheet acImport, , DestinationTableName, "c:\MyXLWorkbook.xls"

HTH

 

[modest]

I suggest using Tom's approach if you want to manipulate/retrieve specific data. Otherwise, there are already built in functions to retrieve tables of information. Just perform a search on TransferSrpeadsheet/TransferText


The following link provides a pretty good description of how to connect to Excel using ADO. I believe it will work with any VBA so long as you include the ADODB reference:
http://support.microsoft.com/default.aspx?scid=kb;EN-US;257819

 

[Paulsburbon]

Thanks for the reponse. I think I got a solution to my problem. I'll give it a try and post it here. I've been importing all of the excel data myself using import but I need to program a button to do that so the non computer users here can do it by pushing a button. Thank you guys all for your help.