Solved

Import Workbook via code or macro

Posted on 2002-05-30
12
299 Views
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!
0
Comment
Question by:bellegigi
  • 6
  • 4
  • 2
12 Comments
 
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.

Thanks

Jell
0
 

Author Comment

by:bellegigi
ID: 7044739
Jell,

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
0
 
LVL 2

Accepted Solution

by:
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
 
skip:
   '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

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


End Function  
0
 

Author Comment

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

Any ideas?

Thanks
0
 
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

Thanks

Jell
0
 

Author Comment

by:bellegigi
ID: 7045143
Thanks!

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

Also, do I need to specify a range?
0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 2

Expert Comment

by:jack49a
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.

Jack
0
 

Author Comment

by:bellegigi
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.
0
 
LVL 2

Expert Comment

by:jack49a
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.

Jack
0
 

Author Comment

by:bellegigi
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!
0
 
LVL 2

Expert Comment

by:jack49a
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.

Jack
0
 

Author Comment

by:bellegigi
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!
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

It took me quite some time to sort out all the different properties of combo and list boxes available from Visual Basic at run-time. Not that the documentation is lacking: the help pages are quite thorough and well written. The problem was rather wh…
Today's users almost expect this to happen in all search boxes. After all, if their favourite search engine juggles with tens of thousand keywords while they type, and suggests matching phrases on the fly, why shouldn't they expect the same from you…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

744 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now