Excel to Access ADO

[swtrader]

I am trying to transfer data from several spreadsheets in a workbook to different Access tables (in the same database).

Here's a snippet:

' open a recordset
Set rs = New ADODB.Recordset
rs.Open "c:\Data\Co_A_Datafiles\Spreadsheet1.xls", cn, adOpenKeyset, adLockOptimistic, adCmdTable
' all records in a table
r = 2 ' the start row in the worksheet
Do While Len(Range("A" & r).Formula) > 0

1. I don't know what the syntax is for opening a worksheet within a workbook (Say, WorkbookA; Spreadsheet1)

and,

2. I would like to use the value from a cell (say D15) in the CodeWorkbook to reference the spreadsheet that is to open. I would include a concatenation to include the static path and the dynamic workbook and spreadsheet names.

Thanks.

swt

 

[MIKElaw]

I normally import the Excel table and then process it from there. Experience has shown that XLS are not normally large
and VB poking around in them tends to be complex and not particularly reliable

This is where I normally start from

Locat is typically c:\temp\myxls.xls
tmpTableName5 is the resultant Access table
then delete the table when done

I normally check that the XLS is there and not in use before I import


Hope that helps
e.g.
DoCmd.TransferSpreadsheet transfertype:=acImport, _
TableName:="tmpTableName5", _
FileName:=Locat, Hasfieldnames:=True, _
SpreadsheetType:=8 ', Range:="All"

 

[swtrader]

Set rs = New ADODB.Recordset
rs.Open "c:\Data\Co_A_Datafiles\Spreadsheet1.xls",

I thought I should open the Excel spreadsheet / I needed to set it to open the Access table. Re-done and works.

For whatever reason, I have not been able to get TransferSpreadsheet (import or export) to work.

Thanks for your help, MIKElaw

swtrader
-- If you don't know where you're going, you'll always know when you're not there.

 

[NYFashionToGo]

I do something Like this However I run It and control it from excel and not access. It all goes into same database as yours is.

Dim appAcc As Object
Set appAcc = CreateObject("Access.Application")
appAcc.Visible = False


appAcc.OpenCurrentDatabase ("C:\NewYorkFashionToGo\NYFOrderProcessingNewEdition.mdb")


appAcc.DoCmd.RunMacro "DeleteOrders"
appAcc.DoCmd.TransferSpreadsheet acImport, 8, "AccessTableName", "C:\NewYorkFashionToGo\ExcelTools\In A New York Minute Processing New Edition3.xls", True, "ExcelSheetName!A1:AE5000"
appAcc.DoCmd.TransferSpreadsheet acImport, 8, "AccessTableName2", "C:\NewYorkFashionToGo\ExcelTools\In A New York Minute Processing New Edition3.xls", True, "ExcelSheetName2!A15000"
appAcc.DoCmd.TransferSpreadsheet acImport, 8, "AccessTableName3", "C:\NewYorkFashionToGo\ExcelTools\In A New York Minute Processing New Edition3.xls", True, "ExcelSheetName3!A1:AF5000"
appAcc.DoCmd.RunMacro "ShippingMacro"
appAcc.Quit