Import Workbook via code or macro

Posted on 2002-05-30
Last Modified: 2008-02-01
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!
Question by:bellegigi
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 4
  • 2
LVL 12

Expert Comment

by:James Elliott
ID: 7044716
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.



Author Comment

ID: 7044739

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

Accepted Solution

jack49a earned 50 total points
ID: 7045009
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  
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)


Author Comment

ID: 7045110
Hi. My code is halting at objWorkbook As Excel.Workbook

Any ideas?

LVL 12

Expert Comment

by:James Elliott
ID: 7045129
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



Author Comment

ID: 7045143

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

Also, do I need to specify a range?

Expert Comment

ID: 7045389
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.


Author Comment

ID: 7045409
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.

Expert Comment

ID: 7046791
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.


Author Comment

ID: 7047044
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!

Expert Comment

ID: 7047087
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.


Author Comment

ID: 7047105
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!

Featured Post

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
Phishing attempts can come in all forms, shapes and sizes. No matter how familiar you think you are with them, always remember to take extra precaution when opening an email with attachments or links.
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

756 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question