Import Excel

[jmenche]

Howdy,

I have an Excel workbook with multiple sheets and want to import them all into a table. Assuming all of the sheets have the same fields, can someone help me with some code to help import?

Thanks

[matthewspatrick]

I have an Excel workbook with multiple sheets and want to import them all into a table.

Access table? A consolidated Excel worksheet? Some other kind of table?

[TheAntiGates]

Your question is legitimate, because the obvious choice (DoCmd.transferspreadsheet) is so lame that it presumes that Excel hasn't figured out how to have more than 1 worksheet in an .XLS. ( http://msdn.microsoft.com/library/default.asp?url=/library/en-us/vbaac11/html/acmthactTransferSpreadsheet_HV05186520.asp )

Researching this I have only seen solutions using ADO which I generally run away from (in favor of the IMO more compreshensible DAO). As I understand it, that is the way to specify range names for the import. You still must name a range for each relevant sheet, as I understand.

The truly wonderful Erlandsen site has code at
http://www.erlandsendata.no/english/index.php?d=envbadacimportwbado
Don't forget the user comments on that page which might be handy.

I haven't done this myself, but hope this gives you something to work from. You can continue asking here if you try this and get stuck.

[matthewspatrick]

Your question is legitimate, because the obvious choice (DoCmd.transferspreadsheet) is so lame that it presumes that Excel hasn't figured out how to have more than 1 worksheet in an .XLS.

With respect, I think your rhetoric is a little too much on this one. TransferSpreadsheet is not lame at all; you just need to know how to use it. The key thing here is that if you want to import >1 sheet, you need to set it up as a loop, and then use the Range argument for TransferSpreadsheet.

If you know ahead of time what the worksheets and ranges are, it's a very simple thing. If you do not know ahead of time, and you need to take in every worksheet, then it is lonly a little more complex, as you have to instantiate an Excel.Application object, create a Workbook object, and then enumerate its Worksheets collection.

That may sound complex, but IMHO it is no harder than the ADO suggestion.

[TheAntiGates]

Either of the two methods I found require range names on everything because sheet names don't seem to be part of the equation.

But I didn't consider your third approach - is this the general idea?Option Compare Database
Option Explicit
'requires reference to Microsoft Excel object library
Sub myImportSheets()
'DoCmd.TransferSpreadsheet , , "tblFoo", "c:\myfile.xls"
Dim xlApp As Object, xlBook As Workbook, xlRng As Range
Dim sht As worksheet

Set xlApp = CreateObject("Excel.Application")
Set xlBook = xlApp.Workbooks.Open("C:\myfile.xls")
For Each sht In xlBook.Worksheets
Set xlRng = sht.usedrange
Next sht
xlApp.Quit
Set xlRng = Nothing: Set sht = Nothing
Set xlBook = Nothing: Set xlApp = Nothing
End Sub
Note that the transferspreadsheet or Erlandsen approach leaves the workbook closed, whereas this opens it.

[Edited 7 minutes later for more polite indentation]

[matthewspatrick]

That's the general idea. I'd tweak the For...Each...Next loop thusly:


For Each sht In xlBook.Worksheets
Set xlRng = sht.usedrange
DoCmd.TransferSpreadsheet <insert other args here>, Range:="'" & sht.Name & "'!" & xlRng.Address
Next sht


The Range argument is looking for a string, so that is why I did not just pass the range object you set.

[TheAntiGates]

I liked the idea of usedrange better than specifying ranges - just one of the countless unrepayable items learned originally from Walkenbach.

Very nice followup with the string construction.

[matthewspatrick]

UsedRange can be very handy. Of course, sometimes stray formatting or a stray cell entry causes UsedRange to cover a wider area than you'd think...

[Norie]

Did anyone look at my response to the OP's other thread?

Or perhaps some of my posts elsewhere regarding this type of thing?

[TheAntiGates]

Isn't the other thread for exporting from Excel, vs. importing from Access?

As to your other posts, go ahead and point them out, to round this out.

[Norie]

Eh, I don't think so.

The code I posted on that thread imports to Access from Excel.

Mind you I was a bit confused about what the OP actually wanted to do, and haven't had any feedback/clarification.

Oh, apart from the original code I posted errored.:oops:

[TheAntiGates]

Oh, okay - with his thread title saying "export" and that he put it in the Excel VBA forum, I didn't expect to see the flipside solution there. Redundancy noted :)

[Norie]

Gates

No problem.:)

I've got a bad habit nowadays of using links like Since Last visit.

That'll pull up all new posts in all forums, but I keep on forgetting which forums they're in.:stars:

[stanl]

In my experience ADO bulk operations were a fast and convenient way to INSERT or SELECT INTO an Access Table from Excel. While DAO would ignore duplicate entries, ADO requires adding Jet OLEDB:Global Partial Bulk Ops=1 as part of Jet 4.0 connection string. Then one would issue SQL such as:



'create a new table
SELECT * INTO Temp FROM [Sales] IN "" [Excel 8.0;Database=%cXLS%] WHERE [Year]>1900;

'existing table
INSERT INTO SalesTemp SELECT * FROM [Sales] IN "" [Excel 8.0;Database=%cXLS%] WHERE [Year]>1900;



Note: I used a macro-expansion script code %cXLS%, in VBA you would concatenate the path/workbook name.

just .02
Stan

[asingh]

Just had a quick "QUERY" about the parameter... "%cXLS%"

Would %cXLS% be the full path/name of the data base. So it could be something like "c:\MyAccessFolder\MyAccessDataBase.mdb".....?

And what would happen to my Excel VBA code when the INSERT INTO statements are executing, if the INSERT INTO encounters a Primary Key Violation [obviously pre--defined]...on the source table SalesTemp. Would it just continue..or would I get an error msg...?

[stanl]

Just had a quick "QUERY" about the parameter... "%cXLS%"

Would %cXLS% be the full path/name of the data base. So it could be something like "c:\MyAccessFolder\MyAccessDataBase.mdb".....?


yes, but more like c:\MyExcelFolder\MyExcel.xls



And what would happen to my Excel VBA code when the INSERT INTO statements are executing, if the INSERT INTO encounters a Primary Key Violation [obviously pre--defined]...on the source table SalesTemp. Would it just continue..or would I get an error msg...?

That is what the bulk ops parameter is for, to avoid key or duplicate violations. Also, your Excel version could be Excel 10.0 or 11.0.

[asingh]

In the above mentioned example...arent we INSERTING data to an access data base...So where do we specify the access data base locations...in the SQL...query...?

[stanl]

In the above mentioned example...arent we INSERTING data to an access data base...So where do we specify the access data base locations...in the SQL...query...?

That would be in the Jet 4.0 Provider String. Are you familiar with how ADO and OLEDB operate?

[asingh]

Would it be something like this..I guess I am probably incorrect, I use this method to INSERT DATA from one Excel Sheet to another Excel Sheet....I have "logically" -- tweaked it for Excell (Source) to Access (destination) SQL.



Dim strConnect as string 'connection string
Dim dbPath as string 'full path of Access Database
Dim strSQL as string 'the SQL that will fire
Dim recordset as object 'active recordset


dbPath = "c:\MyAccessFolder\MyAccessDB.mdb"

strConnect = "DRIVER=Microsoft Access Driver (*.mdb);" & "DBQ=" & dbPATH & ";ReadOnly = 1"

strSQL = "SELECT * FROM Salestemp"


Set recordset = CreateObject("ADODB.Recordset")

recordset.Open strSQL, strConnect 'fires the actual SQL

set recordset = Nothing
recordset.Close

[stanl]

No, more like



cMDB = "c:\MyAccessFolder\MyAccessDB.mdb"
cXLS = "c:\MyExcelFolder\MyExcel.xls"
cConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & cMDB & ";Jet OLEDB:Global Partial Bulk Ops=1;"
oConn = CreateObject("ADODB.Connection")
oConn.Open cConn
cSQL = 'INSERT INTO SalesTemp SELECT * FROM [Sales] IN "" [Excel 8.0;Database=\' & cXLS & "WHERE [Year]>1900;"
cConn.Execute(cSQL)
oConn.Close
oConn = Nothing



in this case [Sales] refers to a named range of an Excel DB; [Sales$] would refer to a worksheet, [Sales$A1:B10] would refer to a specific range. SalesTemp is a Table in Access. You can also INSERT specific 'fields' by expanding the SQL. The nice thing about ADO is it is not necessary to have Access or Excel on a client machine, just MDAC and you can manipulate (create,update,import) a number of ISAMS [Foxpro, dbase, Paradox, Text].

This is getting way off topic for this thread....:bug:

[XL-Dennis]

Stan

Thanks for reminding me about the 'Bulk' variable :)

Kind regards,
Dennis

[stanl]

You are welcome, Dennis. Hope you are doing well:hi:

[asingh]

Thanks Stanl..on the brief about ADO..and moving data from Excel to an Access Environment.


regards,
asingh

[OBP]

MatthewsPatrick, I like your code in post #6, especially the usedrange.
I have inserted a

DoCmd.TransferSpreadsheet

in to that for/next loop and the sht.name, sht.usedrange and xlRng.address all work great when verified independently.
But when it comes to the actual DoCmd.TransferSpreadsheet function it causes the most strange error.
Access, Excel or the VB Editor can't handle this part of the code
Range:="'" & sht.Name & "'!" & xlRng.AddressFor some reason one of them transforms this
"' !"
in to this
$
Which causes the worksheet and range address to become un-identifiable.
It will not work without the "' !" either.
The code works great if you substitute a normal range like "a1:d20" etc for the xlRng.Address.
Can you provide any clarification as to why it does this?

[XL-Dennis]

Stan - I'm doing slightly better but it's a long way :)

Kind regards,
Dennis

[matthewspatrick]

OBP,

That kind of construction generally works for me. The dollar signs have nothing to do with it.

Do you perhaps have worksheet names that themselves have single or double quotes, or other punctuation, in them? I imagine that might bollix things up...

[OBP]

Patrick, thanks for quick response, I will test it on a different spreadsheet and see if makes any difference.
What it is doing is adding an extra dollar sign so that you get $$a$:$d$2

[OBP]

Patrick I have just tried it with an Excel workbook called "Testtrans" with the original sheet1, sheet 2 etc and it still does the same thing.
It finds the used range OK but adds that extra dollar sign in the address.
I am using Office 2002, so it Access 2002 and Excel 2002 (v 10) is that likely to make any difference?

[Norie]

OBP

You actually don't need to specify a range if you only want to import the used range from a worksheet.


For Each ws In wb.Worksheets
DoCmd.TransferSpreadsheet <insert other args here>, Range:="'" & ws.Name & "'!"
Next ws

[OBP]

Norie, thanks for that, I tested that and it worked fine.

[OBP]

Norie, my mistake, I used
Range:=ws.Name & "!"
in error and it worked although the sheets must have exactly the same ranges in use.
I have just used
Range:="'" & ws.Name & "'!"And it caused exactly the same error as the original code, it adds in an extra $ causing the transfer to fail.

[Norie]

OBP

It's been some time since I've written code for importing multiple worksheets into Access but I seem to recall having the same problem.

I'll see if I can dig out the code that finally worked.