J
JP in Denver
I need to import data from multiple worksheets within a single Excel workbook
I have a procedure that works but is very slow
I link to the first worksheet and run an append query to import the data
I then reset the link to the second worksheet and run the append query again
My code loops through every worksheet that needs to be imported
My code I am having problems with is as follows
Dim catDB As ADOX.Catalo
Dim tblLink As ADOX.tabl
Set catDB = New ADOX.Catalo
catDB.ActiveConnection = CurrentProject.Connectio
Set tblLink = catDB.Tables(linkName
Set tblLink.ParentCatalog = catD
tblLink.Properties("Jet OLEDB:Link Datasource") = curSheetName & "$
tblLink.Properties("Jet OLEDB:Create Link") = Tru
Why does "Set tblLink = catDB.Tables(linkName)" take so long (3-4 minutes)
It seems like any reference to the catDB.Tables collection takes a long time
Is there another way to return a table object
I've tried using the Docmd.Transferspreadsheet to import the worksheet into an existing table but sometimes that fails because it thinks the worksheet has more columns than the table does fields so it returns an error saying that field "F33" could not be found. I've tried giving the specific range of the data I want to import but I have 32 columns and close to 60,000 records to import so I give a range of wkshtName & "!" & "A1:AF60000" but it doesn't like that either
The data I need to import is maintained by somebody outside of my company and occasionally they will add a new column to the worksheets. I am only interested in specific columns in the worksheet and so by using a linked table and an append query I do not need to worry about when that happens
I appreciate any suggestions
I have a procedure that works but is very slow
I link to the first worksheet and run an append query to import the data
I then reset the link to the second worksheet and run the append query again
My code loops through every worksheet that needs to be imported
My code I am having problems with is as follows
Dim catDB As ADOX.Catalo
Dim tblLink As ADOX.tabl
Set catDB = New ADOX.Catalo
catDB.ActiveConnection = CurrentProject.Connectio
Set tblLink = catDB.Tables(linkName
Set tblLink.ParentCatalog = catD
tblLink.Properties("Jet OLEDB:Link Datasource") = curSheetName & "$
tblLink.Properties("Jet OLEDB:Create Link") = Tru
Why does "Set tblLink = catDB.Tables(linkName)" take so long (3-4 minutes)
It seems like any reference to the catDB.Tables collection takes a long time
Is there another way to return a table object
I've tried using the Docmd.Transferspreadsheet to import the worksheet into an existing table but sometimes that fails because it thinks the worksheet has more columns than the table does fields so it returns an error saying that field "F33" could not be found. I've tried giving the specific range of the data I want to import but I have 32 columns and close to 60,000 records to import so I give a range of wkshtName & "!" & "A1:AF60000" but it doesn't like that either
The data I need to import is maintained by somebody outside of my company and occasionally they will add a new column to the worksheets. I am only interested in specific columns in the worksheet and so by using a linked table and an append query I do not need to worry about when that happens
I appreciate any suggestions