need to import merged workbook into access

[mpearce]

Hi,

I have 2 workbooks that I merged together and I need to import each sheet into tables in access. The workbook contains 10 sheets and the framework for the tables (ie fields, primary key, and relationships) are set. I just need to get the sheets into the right table.

Heres the layout

workbook:

Sheet 1 Sheet 2 Sheet 3 ..... Sheet10

So I need sheet 1 to be imported into table 1 and sheet 2 into table 2 and so on. I need something that will step through the workbook sheet by sheet and put the sheet's contents into its proper table in access.

So my question is what is the easiest way to do this?

Any help is appreciated
Thanks in advance

Mike

[CreganTur]

Welcome to the forum- it's always good to see new members.

Is this a one time process so you can setup your tables in Access, or is this something that's going to be ongoing?

[mpearce]

this will be ongoing. The tables are already set up. I just need something that directs each sheet to a different table.

[hansup]

this will be ongoing. The tables are already set up. I just need something that directs each sheet to a different table.

You can use DoCmd.TransferSpreadsheet from VBA. Here is an example, which works on my system, of a single worksheet import.
[vba]Public Sub DemoTransferSpreadsheet()
Const strWorkBook As String = "D:\wip\zstblListFields.xls"
Dim strTableName As String
Dim strSheetName As String

strSheetName = "stblDBs_Inventoried"
strTableName = "tblFromExcel"
DoCmd.TransferSpreadsheet acImport, , strTableName, strWorkBook, True, strSheetName

End Sub[/vba] In your case, you can use a For loop to designate the table name and sheet name for 10 TransferSpreadsheet operations. Something similar to this (untested):

[vba]Const strWorkBook As String = "D:\SomeFolder\YourWorkBook.xls"
Dim strTableName As String
Dim strSheetName As String
Dim intCounter As Integer

For intCounter = 1 To 10
strSheetName = "Sheet " & intCounter
strTableName = "Table_" & intCounter
DoCmd.TransferSpreadsheet acImport, , strTableName, strWorkBook, True, strSheetName
Next intCounter[/vba] You may need to adjust your TransferSpreadsheet options; look at the TransferSpreadsheet help topic.

Hans

[mpearce]

that looks like it would work. i should have stated this before, what would have to be modified if the sheet names are:

DecoOpen
DecoClosed
NotInDeco
EligibilityNotInHospital
BillingNotInHospital
DecoOpen (2)
DecoClosed (2)
NotInDeco (2)
EligibilityNotInHospital (2)
BillingNotInHospital (2)

and i need them to go into tables named:

DecoOpen
DecoClosed
NotInDeco
EligibilityNotInHospital
BillingNotInHospital
DecoOpen (2)
DecoClosed (2)
NotInDeco (2)
EligibilityNotInHospital (2)
BillingNotInHospital (2)

So the sheet named DecoOpen would go into the table named DecoOpen and so on down the list.

Thanks for those quick responses

[hansup]

that looks like it would work. i should have stated this before, what would have to be modified if the sheet names are:

DecoOpen
DecoClosed
NotInDeco
EligibilityNotInHospital
BillingNotInHospital
DecoOpen (2)
DecoClosed (2)
NotInDeco (2)
EligibilityNotInHospital (2)
BillingNotInHospital (2)

and i need them to go into tables named:

DecoOpen
DecoClosed
NotInDeco
EligibilityNotInHospital
BillingNotInHospital
DecoOpen (2)
DecoClosed (2)
NotInDeco (2)
EligibilityNotInHospital (2)
BillingNotInHospital (2)

So the sheet named DecoOpen would go into the table named DecoOpen and so on down the list.

Thanks for those quick responses

If you're comfortable working with VBA arrays, you could store the table and sheet names as array members and use intCounter to reference the corresponding array subscript in order to retrieve the names for each iteration of the For loop.

If you don't want to use arrays, try a "Select Case intCounter" approach in your For loop where:
Case 1
strSheetname = "DecoOpen"
strTableName = "whatever"
Case 2
...

Or, if you have the same name for the sheet and table, you only need one variable instead of two.

Hans

[CreganTur]

Since you have multiple worksheets within each workbook, the best option would be to use ADO recordsets.

In the example below I've created an array of all of the worksheets in your workbook. Within a For...Next loop, a recordset is opened to grab the records from each of your worksheets. Then it uses a connection to your database to execute a SQL INSERT INTO statement that will load the contents of the recordset into your table for every record in the recordset.

This code is untested, and may require a little tweaking, but should work for you. You mainly need to setup the SQL statement.

[vba]Dim strSheetName As Varaint
Dim strFilePath As String
Dim conn As ADODB.Connection
Dim rst As ADODB.Recordset
Dim i As Integer
Dim connToUpdate As ADODB.Connection
Dim strSQL As String

'change strFilePath to filepath to your spreadsheet
conn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" _
& "Data Source=" & strFilepath & ";" _
& "Extended Properties=""Excel 8.0;HDR=Yes;IMEX=1"";"

'array of all of your worksheet names
strSheetName = Array(DecoOpen,DecoClosed,NotInDeco,EligibilityNotInHospital,BillingNotInHospital , _
DecoOpen (2),DecoClosed (2),NotInDeco (2),EligibilityNotInHospital (2), _
BillingNotInHospital (2))
'creates a connection to your database
connToUpdate = CurrentProject.Connection

'this loop will go through each worksheet name in the array
For i = LBound(strSheetName) To UBound(strSheetName)
Set rst = New ADODB.Recordset
'create a recordset for the currently selected worksheet
rst.Open "SELECT * FROM [" & strSheetName(i) & "];", conn, adOpenStatic, adLockOptimistic
rst.MoveFirst '<<<explicitly move to the first record in the recordset

'this loop will update your table one record at a time
'the execute method is one of the fastest ways to update your table
Do Until rst.EOF '<<<Loop runs until end of recordset is reached
'this is the SQL Update statement you need to adjust
strSQL = "INSERT INTO " & strSheetName(i) & "(**Fields to update**) " _
& "'VALUES (rst![FieldName].Value & "')"

'run the SQL statement
connToUpdate.Execute strSQL
rst.movenext '<<<Move to next record
Loop

rst.close
Next

connToUpdate.close
rst.close
conn.close
Set connToUpdate = Nothing
Set rst = Nothing
Set conn = Nothing
[/vba]

Now, if you cannot use a single master SQL statement for all of your worksheets because the fields are setup different, then use a SELECT CASE to select the correct SQL statement based on which worksheet is currently being worked.

HTH

[hansup]

Since you have multiple worksheets within each workbook, the best option would be to use ADO recordsets.

Hey Randy!

Can you expand on that point please. I don't see the advantage of ADO over TransferSpreadsheet here.

Regards,
Hans

[CreganTur]

Hey Randy!

Can you expand on that point please. I don't see the advantage of ADO over TransferSpreadsheet here.

Regards,
Hans

The main reason I suggest not using the TransferSpreadsheet method is because of possible problems with the method's range parameter. Yes, you can set a range to cover all of the columns you need: for all of A - L : "A1:L65536". If the destination table has a Primary Key relationship setup, it should ignore all of the blank rows between the last real row and 65536... but there's also a possibility that it won't ignore them.

When creating a recordset from a worksheet, it automatically discounts any blank rows, which leaves you with nothing more than the data you're actually wanted, as defined by the SQL expression that's a part of the recordset's Open method. It just seems a lot cleaner and more acurate to me. also, I haven't found any appreciable difference in importing speed when comparing TransferSpreadsheet with a connection's Execute method.

So it all really comes down to personal preference, I think, since I haven't seen either method touted as 'best practice.'

Feel free to disagree with me. As the wise House, MD. says: "through conflict comes creativity."

[hansup]

The main reason I suggest not using the TransferSpreadsheet method is because of possible problems with the method's range parameter. Yes, you can set a range to cover all of the columns you need: for all of A - L : "A1:L65536". If the destination table has a Primary Key relationship setup, it should ignore all of the blank rows between the last real row and 65536... but there's also a possibility that it won't ignore them.

When creating a recordset from a worksheet, it automatically discounts any blank rows, which leaves you with nothing more than the data you're actually wanted, as defined by the SQL expression that's a part of the recordset's Open method. It just seems a lot cleaner and more acurate to me. also, I haven't found any appreciable difference in importing speed when comparing TransferSpreadsheet with a connection's Execute method.

So it all really comes down to personal preference, I think, since I haven't seen either method touted as 'best practice.'

Feel free to disagree with me. As the wise House, MD. says: "through conflict comes creativity."

Thanx, Randy.

That Greg House sure is an irascible SOB. Still, he's mostly right.

Just to settle any possible doubts, I wasn't looking to argue. I wanted to pick your brain.

I haven't actually used TransferSpreadsheet much. And the spreadsheets I imported must not have contained blank rows within my target data range. So thanks for the heads up on that.

Talk to ya later,
Hans

[mpearce]

Thanks a lot for getting back to me on this. I like this method i just have a slight problem.

I changed strfilepath to the path of the spreadsheet as shown below

strFilePath = "C:\Documents and Settings\mpearce\My Documents\merged recon files.xls"
'change strFilePath to filepath to your spreadsheet
conn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & "Data Source=" & strFilePath & ";" _
& "Extended Properties=""Excel 8.0;HDR=Yes;IMEX=1"";"

when I run it i get:

run-time error '91'

Object variable or With block variable not set

Any ideas?
For now i have left the rest of the code alone. My only other question at this point is with this statement:

strSQL = "INSERT INTO " & strSheetName(i) & "(**Fields to update**) " _
& "'VALUES (rst![FieldName].Value & " ')"

how do i separate the fields that need to be updated?

[CreganTur]

Just to settle any possible doubts, I wasn't looking to argue. I wanted to pick your brain.

There aren't any doubts: I know you weren't trying to argue- I just wanted to make sure that you knew I was open to differing opinions.

I haven't actually used TransferSpreadsheet much. And the spreadsheets I imported must not have contained blank rows within my target data range. So thanks for the heads up on that.

That's possible- or perhaps your table was correctly setup to ignore any blank rows.

Just to clarify, I use TransferSpreadsheet to import whenever I'm pulling from a single sheet in a workbook. I only go the ADO route when more complexity is involved.

I also thought of another point: with the TransferSpreadsheet method, you have to reference each sheet using its code name (Sheet1, Sheet2, etc) when you're dealing with anything other than the currently active sheet (doing TransferSpreadsheet without selecting a range pulls everything from the currently active worskheet in a workbook). In order to have the sheet designation (and possibly ranges) setup correctly for each worksheet, you would need either another array that is in the same index order as the array of worksheets, or you would need to create a 2 dimensional array with worksheet and matching range. It's a level of complexity I choose to avoid.

[CreganTur]

@mpearce

I'm really sorry, but I'm leaving for the day in... right now. Hopefully someone else can help you before tomorrow, but if not I'll gladly help!

[hansup]

Thanks a lot for getting back to me on this. I like this method i just have a slight problem.

I changed strfilepath to the path of the spreadsheet as shown below

strFilePath = "C:\Documents and Settings\mpearce\My Documents\merged recon files.xls"
'change strFilePath to filepath to your spreadsheet
conn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & "Data Source=" & strFilePath & ";" _
& "Extended Properties=""Excel 8.0;HDR=Yes;IMEX=1"";"

when I run it i get:

run-time error '91'

Object variable or With block variable not set

Any ideas?

You need to assign your conn variable to a new instance of an ADODB connection object before you can do conn.Open. If you insert this line before the conn.Open line, your error will go away:

Set conn = New ADODB.Connection

Later on you have this line:

connToUpdate = CurrentProject.Connection

In that case, you are not creating a new object instance, but rather creating a reference to an existing connection object. But you still need to use "Set" when assigning an object reference, so change that line to this:

Set connToUpdate = CurrentProject.Connection

For now i have left the rest of the code alone. My only other question at this point is with this statement:

strSQL = "INSERT INTO " & strSheetName(i) & "(**Fields to update**) " _
& "'VALUES (rst![FieldName].Value & " ')"

how do i separate the fields that need to be updated?

List the field names separated by commas. You also need to edit the stuff in the parentheses following VALUES. Here is an example with 3 fields (db_id, db_name, and db_path):

strSQL = "INSERT INTO " & strSheetName(i) & " (db_id, db_name, db_path) " _
& "VALUES (" & rst![db_id].Value & ", '" & rst![db_name].Value & "', '" & rst![db_path].Value & "')"

Notice that db_name and db_path are text data types, so I included single quotes before and after their values. However db_id is numeric, so that value does not require the extra quotes. A date data type value would need to be surrounded by # characters.

Also this approach assumes you have the field names included in your spreadsheets. If you don't, you will get an error "Item cannot be found in the collection corresponding to the requested name or ordinal". In that case, you need to refer to the field values by their index (ordinal positions) like this:

strSQL = "INSERT INTO " & strSheetName(i) & " (db_id, db_name, db_path) " _
& "VALUES (" & rst.Fields(0).Value & ", '" & rst.Fields(1).Value & "', '" & rst.Fields(2).Value & "')"

And either way, if your spreadsheets don't all match the same table structure, you will need to create separate SQL INSERT statements for each of the different structures.

Good luck,
Hans

[hansup]

strSQL = "INSERT INTO " & strSheetName(i) & " (db_id, db_name, db_path) " _
& "VALUES (" & rst![db_id].Value & ", '" & rst![db_name].Value & "', '" & rst![db_path].Value & "')"

Rats! I forgot about the issue with your table names. You need to surround them with square brackets, as Randy showed you earlier.

strSQL = "INSERT INTO [" & strSheetName(i) & "] (db_id, db_name, db_path) " _
& "VALUES (" & rst![db_id].Value & ", '" & rst![db_name].Value & "', '" & rst![db_path].Value & "')"

[mpearce]

Hans,

Here is the line i added:

'change strFilePath to filepath to your spreadsheet
Set conn = New ADODB.Connection
conn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" _
& "Data Source=" & strFilePath & ";" _
& "Extended Properties=""Excel 8.0;HDR=Yes;IMEX=1"";"

I now get a new error. Was I supposed to put that line there? or somewhere else?

run time error '-2147467259 (80004005)':
Invalid argument

It points back to that snippet of code.
I know I went wrong somewhere. Any ideas?

[CreganTur]

Do you have a reference (Tools->References) set to Microsoft ActiveX Object Library?

[mpearce]

yes i do

[hansup]

Hans,

Here is the line i added:

'change strFilePath to filepath to your spreadsheet
Set conn = New ADODB.Connection
conn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" _
& "Data Source=" & strFilePath & ";" _
& "Extended Properties=""Excel 8.0;HDR=Yes;IMEX=1"";"

I now get a new error. Was I supposed to put that line there? or somewhere else?

run time error '-2147467259 (80004005)':
Invalid argument

It points back to that snippet of code.
I know I went wrong somewhere. Any ideas?

I see Randy already answered.

I wanted to give you a heads up about another problem I ran into. I can't find any way to make ADO accept a worksheet name which includes parentheses. (It complains about "illegal characters".) I don't know how to get around it.

Good luck,
Hans

[CreganTur]

I wanted to give you a heads up about another problem I ran into. I can't find any way to make ADO accept a worksheet name which includes parentheses. (It complains about "illegal characters".) I don't know how to get around it.

Hmmm...

Is ADO kicking them out, even when the names w/ parentheses are wrapped in brackets?


@mpearce
Since you have the correct reference and you're still getting this error, can you please post your complete code? The error's cause may be somewhere else within it.