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.
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 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):

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 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.

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,BillingNotInHo spital, _
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


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:thumb

[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." :thumb

[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." :thumb Thanx, Randy.

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

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! :thumb

[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. :bow:

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...:think:

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.

[mpearce]

sure no problem. Here it is:

Dim strSheetName As Variant
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

filepath = "C:\Documents and Settings\mpearce\My Documents\merged recon files.xls"
'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"";"

'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
Set 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

[hansup]

Hmmm...:think:

Is ADO kicking them out, even when the names w/ parentheses are wrapped in brackets?
I get that error when I attempt to conn.Execute a SQL statement which references a sheet whose name includes parentheses. Within the SQL statement, the name is surrounded by square brackets.

Hans

[hansup]

sure no problem. Here it is:

Dim strSheetName As Variant
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

filepath = "C:\Documents and Settings\mpearce\My Documents\merged recon files.xls"
'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"";"

You never assigned a value to your strFilePath variable ... so your connection string includes nothing for Data Source.

However you did assign a file path to another variable, "filepath". But you didn't declare it (Dim) first. If you add Option Explicit to the declarations section of your module, it will save you from this kind of grief.

Hans

[mpearce]

ah gotcha on that one. I modified filepath to strfilepath and that error is fixed. But now when it comes to:

strSheetName = Array(DecoOpen, DecoClosed, NotInDeco, EligibilityNotInHospital, BillingNotInHospital, _
DecoOpen(2), DecoClosed(2), NotInDeco(2), EligibilityNotInHospital(2), _
BillingNotInHospital(2))

i get a 'type mismatch' run time error and all the elements in the array are empty at that point.

[mpearce]

side note that error occurs with the option explicit line
when i add it i get variable not defined and i am pointed to the first element in the array. This doesnt make sense.

[CreganTur]

side note that error occurs with the option explicit line
when i add it i get variable not defined and i am pointed to the first element in the array. This doesnt make sense. You need to wrap the worksheet names with double-quotes to make them into string values. VBA think that they are undefined variables otherwise.

Always, ALWAYS, use Option Explicit. You can make this happen automatically by clicking Tools->Options->Require Variable Declaration. This enforces good coding practice by forcing you to declare all variables that you use.

[CreganTur]

I get that error when I attempt to conn.Execute a SQL statement which references a sheet whose name includes parentheses. Within the SQL statement, the name is surrounded by square brackets.

Hans

If you create a regular query and run it within Access, will it work with object names that have parentheses? If it does, then it shows that ADO has a restriction that's causing problems. If this is the case, then one of two things needs to happen:

1) parentheses need to be removed from all table/field names, and removed from the worksheets in the workbook.

-or-

2) if mpearce says that the parentheses cannot be removed because of some sort of process restriction, then we'll have to use the TransferSpreadsheet method as an alternative.

[hansup]

I get that error when I attempt to conn.Execute a SQL statement which references a sheet whose name includes parentheses. Within the SQL statement, the name is surrounded by square brackets.

Hans Sorry, Randy. I stated that wrong. Let me show you a stripped down code example instead:

Public Sub ParenthesesProblem()
Const strWorkbook As String = "Import2Access.xls"
Dim strFilePath As String
Dim varSheetNames As Variant
Dim varSheet As Variant
Dim cnExcel As ADODB.Connection
Dim strSQL As String
Dim strCnString As String
Dim rst As ADODB.Recordset

strFilePath = CurrentProject.Path & "\" & strWorkbook
Debug.Print "Workbook path name: " & strFilePath

'set up connection to Excel workbook
Set cnExcel = New ADODB.Connection
strCnString = "Provider=Microsoft.Jet.OLEDB.4.0;" _
& "Data Source='" & strFilePath & "';" _
& "Extended Properties='Excel 8.0;HDR=Yes;IMEX=1';"
Debug.Print "Connection string: " & strCnString
cnExcel.Open strCnString

'array of all of your worksheet names
'varSheetNames = Array("DecoOpen", "DecoClosed", "EligibilityNotInHospital", _
'& "DecoOpen(2)", "DecoClosed(2)")
varSheetNames = Array("DecoOpen", "DecoClosed", "EligibilityNotInHospital")

Set rst = New ADODB.Recordset
'this loop will go through each worksheet name in the array
For Each varSheet In varSheetNames
'create a recordset for the currently selected worksheet
rst.Open "SELECT * FROM [" & varSheet & "$];", cnExcel, adOpenStatic, adLockOptimistic

rst.Close
Next varSheet

Set rst = Nothing
cnExcel.Close
Set cnExcel = Nothing
ExitHere:
Exit Sub
End Sub
As written, it runs without error. But when I attempt to use the commented-out version of the varSheetNames array, it fails on the rst.Open step with error messages like this:

'DecoOpen[2]$' is not a valid name. Make sure that it does not include invalid characters or punctuation and that it is not too long.

Without the dollar sign, I couldn't get it to accept a specific worksheet name in a workbook which contains more than one sheet. I've tried placing the dollar sign in different positions with no joy.

I'm attaching my workbook in case you willing to look at this.

Hans

[hansup]

If you create a regular query and run it within Access, will it work with object names that have parentheses? I didn't get that far --- my code attempts failed before I got to any step which referenced an Access table name which includes parentheses. But I'll give it a try.

If it does, then it shows that ADO has a restriction that's causing problems. If this is the case, then one of two things needs to happen:

1) parentheses need to be removed from all table/field names, and removed from the worksheets in the workbook.

-or-

2) if mpearce says that the parentheses cannot be removed because of some sort of process restriction, then we'll have to use the TransferSpreadsheet method as an alternative.I vote for #1!
I can't get TransferSpreadsheet to work with sheet names containing parantheses.

[hansup]

If you create a regular query and run it within Access, will it work with object names that have parentheses? Yes, this one works:

SELECT *
FROM [DecoClosed (2)];

[mpearce]

The sheets could really be called anything. i just need a way to know what sheets came from what workbook. now it looks like there is an issue with the line of code in red. I get another run time error:

The microsoft jet database engine could not find the object 'DecoOpen'. Make sure the object exists and that you spell its name and path name correctly.

Here is the full code that i am working with.


Option Compare Database
Option Explicit

Private Sub Command0_Click()
Dim strSheetName As Variant
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

strFilePath = "C:\Documents and Settings\mpearce\My Documents\merged recon files.xls"
'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"";"

'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
Set 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
End Sub

It seems that sheet name is taking a value.

[hansup]

The sheets could really be called anything. i just need a way to know what sheets came from what workbook. Good. I'd like to request your worksheet names start with a letter and include only letters, numbers, and underscore characters.

Meanwhile, I'm curious about a couple points.

Why do you merge 2 workbooks into 1 before starting this process? Your worksheets all have unique names. So do the tables. We could import from WorkbookA.xls and import again from WorkbookB.xls

What will you do with the data after importing to Access? Are the DecoOpen and DecoOpen2 tables forever segregated?


now it looks like there is an issue with the line of code in red. I get another run time error:

The microsoft jet database engine could not find the object 'DecoOpen'. Make sure the object exists and that you spell its name and path name correctly.
rst.Open "SELECT * FROM [" & strSheetName(i) & "];", conn, adOpenStatic, adLockOptimistic
I ran into a similar issue. Adding a dollar sign before the second square bracket cured that error (but I don't know if that's really Kosher):
rst.Open "SELECT * FROM [" & strSheetName(i) & "$];", conn, adOpenStatic, adLockOptimistic

You still have a lot of work left. You need to customize "**Fields to update**" and the VALUES list in the SQL INSERT statement. If your tables each contain dozens of fields, that's a big job. And if your tables have different fields, you need to construct a separate INSERT statement for each unique set of fields --- and match each table/sheet pair to the appropriate INSERT statement. Are you up for that?

Wouldn't it be easier to just say "grab the fields from this sheet and put them in that table" without the need to explicitly list each and every field by name?

See if this code works for you:
Public Sub mpearce2()
Dim strFilePath As String
Dim varSheetNames As Variant
Dim varSheet As Variant
Dim cnExcel As ADODB.Connection
Dim strSQL As String
Dim strCnString As String

'strFilePath = CurrentProject.Path & "\" & "Import2Access.xls"
'this is where your Excel file lives --- change as needed
strFilePath = "C:\Documents and Settings\mpearce\My Documents\merged recon files.xls"
Debug.Print "Workbook path name: " & strFilePath

'set up connection to Excel workbook
Set cnExcel = New ADODB.Connection
strCnString = "Provider=Microsoft.Jet.OLEDB.4.0;" _
& "Data Source='" & strFilePath & "';" _
& "Extended Properties='Excel 8.0;HDR=Yes;IMEX=1';"
Debug.Print "Connection string: " & strCnString
cnExcel.Open strCnString

'array of all of your worksheet names
varSheetNames = Array("DecoOpen", "DecoClosed", "EligibilityNotInHospital")

'this loop will go through each worksheet name in the array
For Each varSheet In varSheetNames

strSQL = "INSERT INTO [" & varSheet & "] IN '" & CurrentProject.FullName _
& "' " & "SELECT * FROM [" & varSheet & "$];"
Debug.Print strSQL
cnExcel.Execute strSQL

Next varSheet

cnExcel.Close
Set cnExcel = Nothing
End Sub

[mpearce]

the excel workbooks i am working with are generated by another utility. That utility also names the sheets in the workbooks to DecoOpen, etc.

My company works with hospitals so there is patient information that goes back and forth with account updates. the goal is to compare 2 of these workbooks to see what accounts havent been updated from month to month.

So my thought was to import the excel information into access and then establish relationships between tables and run join queries to show what accounts havent been updated.

so i thought by combining the two workbooks together and only having to import 1 workbook would be the best way to do this. I will not be the end user on this utility I the easiest possible way to make this happen. The end users that will be using this are not very tech saavy.

to summarize i have two workbooks with 5 sheets each. I need to compare DecoOpen in book1 to DecoOpen in book2 to see what patient accounts exist in both workbooks.

So what ever way is easiest to do that, that is the solution i would like to implement.

[CreganTur]

I forgot about the $ sign- it is required at the end of the sheet name.

This code will grab the worksheet name of the active worksheet. It includes any special symbols, like the $, that the name requires. If there's a worksheet name that you can't get to work- save the workbook with that worksheet selected and run this code- it should work.

Dim conn As ADODB.Connection
Dim rsData As ADODB.Recordset
Dim strFilePath as String
Dim strSheetName As String

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"";"
Set rsData = conn.OpenSchema(adSchemaTables)
strSheetName = rsData.Fields("TABLE_NAME").Value
debug.print strSheetName

rsData.close
conn.close
Set rsData = Nothing
Set conn = Nothing

[mpearce]

the two workbook's sheet names and column headings are identical. maybe access isnt the best way of doing this. I just need a simple way to look at the account numbers in each sheet in the workbook from the previous month, and compare those account numbers in each sheet of the current month's workbook and have a report created showing those "duplicates".

let me start off by saying that my company helps people get medical benefits to help pay hospital bills. so lets take the sheet DecoClosed as an example. Here we have a list of patients whose accounts are showing closed in my company's system (meaning that the medical bill has been paid) but open (meaning there is a balance on the account) in the hospital system . If that account appears in book1 and in book2 I need a report showing that. I need a report showing that for each sheet of the two workbooks.

Thanks so much for all the help. This was over my head to do from scratch. It is nice to know that there are people out there like you all to help with something like this.

[hansup]

I forgot about the $ sign- it is required at the end of the sheet name.

This code will grab the worksheet name of the active worksheet. It includes any special symbols, like the $, that the name requires. If there's a worksheet name that you can't get to work- save the workbook with that worksheet selected and run this code- it should work. Slick! Thanks.

Your code allowed me to finally see the problem. I created my sample tables and sheets from the first list posted. That list included a space before the opening parenthesis like so: "DecoOpen (2)". Later on, the space disappeared from posted VBA code which loads the array of sheet names. I was using that code, and naturally failing to match with the actual sheets in the workbook. Gack!

Putting the space back in allowed ADO to find and use the sheets regardless of parantheses. It also didn't care about parentheses in the Access tables names. As long a I had the dollar sign tacked on the end of the name, ADO was happy.

Incidentally, DoCmd.TransferSpreadsheet was equally happy with the sheet names ADO accepted.

I'm really sorry about the wild goose chase. :(

Hans

[hansup]

the two workbook's sheet names and column headings are identical. maybe access isnt the best way of doing this. I just need a simple way to look at the account numbers in each sheet in the workbook from the previous month, and compare those account numbers in each sheet of the current month's workbook and have a report created showing those "duplicates".

let me start off by saying that my company helps people get medical benefits to help pay hospital bills. so lets take the sheet DecoClosed as an example. Here we have a list of patients whose accounts are showing closed in my company's system (meaning that the medical bill has been paid) but open (meaning there is a balance on the account) in the hospital system . If that account appears in book1 and in book2 I need a report showing that. I need a report showing that for each sheet of the two workbooks.

Thanks so much for all the help. This was over my head to do from scratch. It is nice to know that there are people out there like you all to help with something like this.
This is interesting. We were struggling to import Excel data into Access. I suspect that problem may now be resolved, as long a we put space in sheet names where they're needed. :eek: However, if you're using Access only to query/report (not modify) the data, you may not need to import it into Access. You could link the Excel sheets to Access and treat them as virtual tables for querying and reporting. When you have a new batch of data to examine, just replace the old workbook with the new one --- it could work fine as long as the names of the workbook, sheets, and fields remain the same. You could skip the workbook merge step as well --- link DecoClosed_yours to DecoClosed in one workbook, and DecoClosed_theirs to DecoClosed in the other workbook.

What approach will work best for your non-technical users?

Hans

[mpearce]

hans,

I am not really familiar with either method, so i really cant tell give an answer on that.

I mean some of these people have difficulty copying and pasting, or copying an excel sheet to a new workbook.

Just to give you an idea of intelligence level.

[hansup]

I am not really familiar with either method, so i really cant tell give an answer on that.

I mean some of these people have difficulty copying and pasting, or copying an excel sheet to a new workbook. OK. Thanks for giving me a clearer look at the big picture.

How do you want to proceed from here? Have you had a chance to try the mpearce2 subroutine I posted earlier? Or do you prefer the version we were working with before that?

Hans

[mpearce]

OK. Thanks for giving me a clearer look at the big picture.

How do you want to proceed from here? Have you had a chance to try the mpearce2 subroutine I posted earlier? Or do you prefer the version we were working with before that?

Hans

with regard to the mpearce() routine i get an error saying:

The field is too small to accept the amount of data you attempted to add. Try inserting or pasting less data.

I just need to get something up and running for management to use. So I guees I want to proceed with something that can be coded quickly and easily.

I would like to get either the mpearce() routine working or if not that one than the one before it.

Thanks again guys.

[CreganTur]

The field is too small to accept the amount of data you attempted to add. Try inserting or pasting less data.

Each field data type has its own constraints on how many bytes of data it can hold. It sounds like some of your Excel cells may be violating these constraints.

[mpearce]

Each field data type has its own constraints on how many bytes of data it can hold. It sounds like some of your Excel cells may be violating these constraints.

Data within the cells in excel is small probably under 50 characters so a text data type should be fine, right? anyway for testing i changed the data type to memo and that seemed to work. but somehow blank rows get inserted as either the first or last record which was throwing an error due to a primary key constraint violation.

basically i need a primary key so i can relate the tables on account number so i can run the join query and find the duplicate account numbers in DecoOpen and DecoOpen (2) for example and then put the results of that query into a report.

[hansup]

with regard to the mpearce() routine i get an error saying:

The field is too small to accept the amount of data you attempted to add. Try inserting or pasting less data.

I just need to get something up and running for management to use. So I guees I want to proceed with something that can be coded quickly and easily.

I would like to get either the mpearce() routine working or if not that one than the one before it.

Thanks again guys. OK. Let's expedite this project!

Please make copies of your Access database and Excel workbook. Discard any database objects which don't pertain to this project. Delete any records from those tables which are used to hold the imported Excel data. Compact the database (for Access 2003, you would choose Tools -> Database Utilities -> Compact and Repair Database ...).

Delete all but a few data rows from your Excel sheets. In the remaining data rows, replace any PHI or other confidential/proprietary information with make-believe values.

Then add the database file and the workbook file to a Zip file. Attach the Zip file to a message back here.

Hans

[CreganTur]

It's stange that it's pulling in blank records, but you can easily counter this by adding in a WHERE clause to the SQL that creates your recordset- use something like WHERE PrimaryField <> Null or PrimaryField <> ""

[hansup]

Data within the cells in excel is small probably under 50 characters so a text data type should be fine, right? anyway for testing i changed the data type to memo and that seemed to work. but somehow blank rows get inserted as either the first or last record which was throwing an error due to a primary key constraint violation.

basically i need a primary key so i can relate the tables on account number so i can run the join query and find the duplicate account numbers in DecoOpen and DecoOpen (2) for example and then put the results of that query into a report. No. This query returns all account_num values which exist in both DecoOpen and DecoOpen (2). I don't have any relationships defined, and account_num is not a primary key for either table:

SELECT a.account_num
FROM DecoClosed AS a INNER JOIN [DecoClosed (2)] AS b ON a.account_num = b.account_num;

Depending on the specifics of your project it might make more sense to drop relationship, primary key, or other constraints before importing from Excel. Then do any needed cleansing on the imported data and programatically re-create necessary constraints.

It will be a whole lot easier to figure all this stuff out if we can see samples of the database and spreadsheets you're working with!

Hans

[mpearce]

I forgot to clear out some of the tables. So i cleared them and reposted the file.

[mpearce]

the only thing with dropping the primary key is that with each click of the button more records are appended to the end of the table.

so my understanding is that the primary key would stop that from happening. So that if an import is already done and the import is run again access will realize that these records already exist and wont import tham again.

[hansup]

Alright here is a copy of the access database and the excel file
Thanks. That was quick. I'll get on it today.

Hans

[hansup]

the only thing with dropping the primary key is that with each click of the button more records are appended to the end of the table.

so my understanding is that the primary key would stop that from happening. So that if an import is already done and the import is run again access will realize that these records already exist and wont import them again.
I wondered about that. Sounds like we should throw away all records from those 10 Access tables before importing from Excel. That's easy to automate. Let me know if that's not what you want.

Hans

[mpearce]

originally i had the import as taking each workbook and making each sheet a separate file and then importing the 10 sheets into the table. but there was a bit of overhead with that so i wanted to dumb it down into less steps and make that as automated as possible.

In the other project the transferspreadsheet method is used for the import. There are primary keys defined in each table and relationships based on those keys. Then there are join queries that run to generate the duplicates. There are reports based on those queries that display the results of the query. Then there is a delete button to clear all tables that is protected by a password.

So really all i need to redo is the import step, everything else is already in place.

[mpearce]

originally i had the import as taking each workbook and making each sheet a separate file and then importing the 10 sheets into the table.

meaning that the user would have to manually parse the sheets from the two workbooks into 10 different excel files.

[hansup]

In the other project the transferspreadsheet method is used for the import. There are primary keys defined in each table and relationships based on those keys. Then there are join queries that run to generate the duplicates. There are reports based on those queries that display the results of the query. Then there is a delete button to clear all tables that is protected by a password.

So really all i need to redo is the import step, everything else is already in place. I'm not sure if I did the right thing. I thought each table should include only the rows from the current Excel import. So I coded it to empty out the tables immediately before importing from Excel. If that's wrong, we can easily disable the "empty out" step.

The good news is the attached version of the database imports successfully from the spreadsheets. And it ignores blank rows in the spreadsheets. I didn't get that error about "field too small ..." that bit you. I'm curious to see what happens when you run it with your full scale spreadsheets.

This thing probably needs refinement, but I thought I'd let you take a look before I put more effort into it.

Hans

[mpearce]

i ran the updated version against the full spreadsheets and some of the tables have data. However i still get the 'field too small' error with eligibilitynotinhospital.

[mpearce]

sorry that was an easy one. I had to change the field size on some of the text fields to something greater than 50.

[hansup]

sorry that was an easy one. I had to change the field size on some of the text fields to something greater than 50.
Cool. I was working on a routine to examine the max width for each of the fields in that table. Don't need it now.

So is the rest of the import operation working?

Hans

[mpearce]

The rest of the import seems to be working fine :) i am excited about that. However there have been a few time where I will get, i think its a runtime error saying "the database has been placed in a locked state" (or something like that).

There is one small thing i need to do. Originally there is a summary sheet as the 1st sheet in the workbook. Since that sheet isn't needed for analysis I have code that opens each workbook and deletes that sheet before the merge is done and saves the workbook without the summary sheet. That part works fine. But if i try to merge two workbooks and the summary sheet is already deleted the code breaks because it is looking for a sheet that doesnt exist. So I am thinking this is just a simple if...then statement. Something like:

If not object.worksheets("Summary") is nothing then
object.worksheets("Summary").delete
else
msgbox "Summary sheet deleted"
end if

i tried to implement this and i get 'subscript out of range'. so that makes me think its something with one of the elements in the array. Could someone confirm this and give a little more detail on the logic here?

Thanks again for all the help.

[hansup]

The rest of the import seems to be working fine i am excited about that. However there have been a few time where I will get, i think its a runtime error saying "the database has been placed in a locked state" (or something like that). When developing with ADO, I get this error occasionally:

Error -2147467259 The database has been placed in a state by user 'Admin' on machine 'VM2003' that prevents it from being opened or locked.

I'm not sure about the cause; it may be code changes not saved/compiled. If I compact the database, the error goes away ... until I make more changes.

So, I think this may be an error your users won't see. If you find a situation where it does show up for your regular (non-developer) users, or if you were talking about a different error, let me know.

Meanwhile I added minimal error-handling to Command1_Click. However, I really don't know what more to do. Everything we've done so far is basically the onclick event for a command button on a form in a larger application. (Right?) I think any error handling for spreadsheet import should be integrated with the error handling strategy in the parent application; I have no idea what you're doing there.

Also, from your users' perspective, what should happen if a spreadsheet import fails? And what about the Access tables: some might have newly imported data; others old data or empty?


There is one small thing i need to do. Originally there is a summary sheet as the 1st sheet in the workbook. Since that sheet isn't needed for analysis I have code that opens each workbook and deletes that sheet before the merge is done and saves the workbook without the summary sheet. That part works fine. But if i try to merge two workbooks and the summary sheet is already deleted the code breaks because it is looking for a sheet that doesnt exist.
What business value do you add by deleting the Summary sheets? I'm thinking "do the simplest thing which could possibly work". The import code is happy if it finds the sheets it's looking for; it doesn't care whether any additional sheets are present in the workbook.

In other words, your problem figuring out how to delete a sheet which may not be there ... that problem goes away if you don't try to delete the sheet.

If you really need the sheet gone, you could just attempt the delete and ignore the error you get when the sheet isn't there:

On Error Resume Next
oBook.Worksheets("Summary").Delete
If Err.Number = 0 Then
MsgBox "Summary sheet deleted"
Else
MsgBox "Summary not sheet deleted"
End If
Err.Clear
On Error GoTo 0 'or your subroutine's error-handler
Why tell anyone whether or not the Summary sheet is deleted?

I attached a Zip of the database with my latest changes. Beware, I don't know which fields you changed to cure the "The field is too small to accept the amount of data you attempted to add" problem.

Hans