Import Workbook via code or macro

Hi. I have an Excel 97 workbook that contains several spreadsheets. I need to import all of the spreadsheets into an Access table. Does anyone know how to do this? When I create a macro and use transfer spreadsheet, it only imports the first spreadsheet in the workbook. I have tried using the "range" criteria, however, I don't know what the end of the range would be for the spreadsheet. I also only to import those records that have the column CurrentDate value of today's date.

Thank you for any help!
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

James ElliottManaging DirectorCommented:
You would have to link to excel and transpose the different worksheets accross into different tables. Quite a tricky task.

Let me know if you would like me to look into it.


bellegigiAuthor Commented:

I could really benefit from the help. I am making some progress with it via a macro. However, I am getting type conversion errors on a couple of the fields and I can't quite figure that out because they are text types in the Excel spreadsheet and in Access. I think that the idea of linking sounds great. How do we go about it?

Thank you, Ellen
You might want to think about going with code. I do something like you describe on  a regular basis.  I have a form set for importing data and code behind the form.  As things progress info is passed back to the form to keep the user informed of progress.

This code does what you want.  I gets a bit trickier if you want to capture the spreadsheet name in
the table or if your spreadsheets can be of variable length or contain records you don't want.

You might also want to add better error error handling and more feedback to user.  But this should start
you in the right direction.

It also assumes you have  a table with the same column names as in the excel spreadsheet.

Hope this helps, Jack

Function GetWorkbook()
Dim objWorkbook As Excel.Workbook
Dim objSheet As Worksheet
Dim strWBname As String
Dim strWSname As String
Dim i As Integer
Dim intCount As Integer
Dim strFilename As String

On Error GoTo GetWorkbook_Err
strFilename = "D:\access\test.xls"
Set objWorkbook = GetObject("" & strFilename & "")
strWBname = objWorkbook.Name
intCount = (objWorkbook.Sheets.Count - 1)
  'for some reason this returns 1 more than I want
   'it might be different with your data

For i = 1 To (intCount)
  'Debug.Print i; intCount; strAnal; strWSname
Set objSheet = objWorkbook.Sheets(i)
  strWSname = objSheet.Name
If (strWSname = "XXX" Or strWSname = "YYY") Then GoTo skip
   Debug.Print i; intCount; strWSname

   DoCmd.TransferSpreadsheet acImport, 5, "tTEST", strFilename, True, "" & strWSname & "!A5:D20"
'arguments here 5 = excel version
'   tablename filename True means has headers
'   last name of sheet & range
'This is import table which I empty and use over for each sheet.  Data in import table can be updated and then appended to real table.   So you call otehr rountines here to do this
   'Do skip label if any  you wish to skip
Next i

objWorkbook.Application.Quit    ' When you finish, use the Quit method to close
Set objWorkbook = Nothing    ' the application, then release the reference.
Set objSheet = Nothing

   Exit Function
    Debug.Print i; intCount; strWSname
   MsgBox Err.Number & Err.Description
   Resume GetWorkbook_Exit

End Function  

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Active Protection takes the fight to cryptojacking

While there were several headline-grabbing ransomware attacks during in 2017, another big threat started appearing at the same time that didn’t get the same coverage – illicit cryptomining.

bellegigiAuthor Commented:
Hi. My code is halting at objWorkbook As Excel.Workbook

Any ideas?

James ElliottManaging DirectorCommented:
You need to reference you excel object library.

to do this goto your VB Editor. Click on the tools menu and select references. Put a tick next to the one that says Microsoft Excel 9.0 Library (or something like that.

BTW the code above will not work on your machine straight away. You must adapt it to suit the name of your spreadsheet and worksheets.

Just shout if you need help


bellegigiAuthor Commented:

Where do I need to change the workbook and worksheet names?

Also, do I need to specify a range?
If workbook is always the same, just specify as
strFilename = "????D:\access\test.xls"

If it varies you can pass the variable.  I use a named range but I'm not sure it is needed if you are doing the whole worksheet.

DoCmd.TransferSpreadsheet acImport, 5, "tTEST", strFilename, True, "" & strWSname & "!A5:D20"

This is within a loop that will identify each worksheet (WS)  in your workbook and pass it as strWSname to the Do.Cmd.  The end of the line is where you specify range(A5 to D20 in this case).

In this case tTEST is the import table.  I call another sub at this point to do anything needed  in the import table (delete lines not matching date, delete empty lines, add something to indicate WS name if need)  When you complete the called sub, the action returns to your loop where it picks the next WS.  Also an option in there is you have certain WS names you always want to skip.

You will probably need to change quite abit for your app but I hope this starts you in a useful direction.

bellegigiAuthor Commented:
yes, it helps quite a bit. I am still messing around with it. I have it working to a certain extent. The workbook always is the same. I assumed this was the only place in which I need to specify a name. I will try this without specifying a range to see if that works. Thanks!

I am wrestling with Import Errors - I receive Type Conversion Errors on numbers that have leading 0's. I receive this even when I change the columns in .xls to a text format. Don't know why? So, I am stuck on that for now.

Thanks for your help.
I get type conversion errors too.  I think mine are due to Access and Excel having different ideas about numbers.  To Access a number must be a number.  To excel a numeric seems to mean that is it is a number it is in a certain numerci format but it seems to be OK to have text like "not found".

You could force all field to text by importing headers (True arguement in Do.Cmd above) and just deleting them in the import table.  (Use False if you don't want headers) In my case I want the full numbers (text seems to import only what is shown but numeric captures the full number), so I have error tables but I delete them before the import program runs again.  That way I can check the error tables if wanted (but they do not seem helpful in most cases)  and they just get deleted next time.

If some of your fields in Access are numeric it may be the True argument that is causing trouble when the headers (text) are imported.  If that is not clear or you have other questions, let me know.

bellegigiAuthor Commented:
Hi. Thanks! I changed the argument to True and then tried False, I still get the type conversion errors.

Do you have any more ideas? The only thing that I've been able to determine is that it does it on the values that lead with zero.

I am going to award points because you have been so great and the code does the job, I just need to isolate what is causing the conversion errors.

Thanks again!
I don't know.  Excel does odd thing with numbers.  If a look at a csv with a text editor and a filed has 01-01 in it, it will show up as 01-01.  If I open it in Excel it will say Jan 1.  There are probably options but I don't know enough about excel to change things.

I think both excel and access will drop leading zeroes from numbers and if it is anumber, it probably doesn't matter.  But if you need the zeroes you might consider making the field text.  The problem is if a field is text and access sees numbers in the first so many records (I don't know the number here), it will think it is a number.  And then when it runs into text it create an error.  There may be a way around this but I don't kow an elegant solution.

If you set to true and import headers (and then delete them), Access will think of the field as text and it should keep your leading zeros.  This could cause some other problmes.  If a number is really 1.5768 and excel only displays 1.57 you will lose the last 2 digits on a text import.  I think a numeric import will take all 4 decimals.

Since I want all digits I do a numeric import and tehn delete the error tables produced in the records where there is text in the field.  If you take that approach I can show you how I do that in code.

bellegigiAuthor Commented:
Hi! you know what? I had the range set so that it would only import starting at the actual data. So, even though I had headers as true, the first thing that it saw was a number. So, I changed the range to start at A1 and I no longer receive the conversion errors. I just have a lot of unwanted text at the beginning of my table now. :) But, now at least I know why it was happening. Thanks for all of your help! It's been great!
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.