Access Database Import Of Excel Worksheet Data..

Hi - I have been trying to use Patrick Mathews solution (posted a long time ago) for importing worksheet data to an Access table.  I have created the module in an Access database with the following code:

Sub GrabData()

Dim xlApp As Object
Dim xlWb As Object
Dim xlWs As Object
Dim LastRow As Long
Dim Addr As String
Dim Nam As Object
Dim Nams As Collection
Dim Counter As Long

Const SourcePath As String = "C:\Users\tom.farrar\Desktop\MergeSheets.xlsm" 'change as needed
Const DestTable As String = "Table1" 'change as needed

Set Nams = New Collection

With DoCmd
.SetWarnings False
.RunSQL "DELETE * FROM " & DestTable

Set xlApp = CreateObject("Excel.Application")
Set xlWb = xlApp.Workbooks.Open(SourcePath)

For Each xlWs In xlWb.Worksheets
LastRow = xlWs.[a65536].End(-4162).Row - 2
Counter = Counter + 1
On Error Resume Next
xlWb.Names("xxx" & Counter).Delete
On Error GoTo 0
Set Nam = xlWb.Names.Add(Name:="xxx" & Counter, RefersTo:="='" & xlWs.Name & "'!" & _
xlWs.Range(xlWs.Cells(2, 1), xlWs.Cells(LastRow, 4)).Address)
Nams.Add Nam.Name
Next

Set Nam = Nothing
Set xlWs = Nothing
xlWb.Save
xlWb.Close False
Set xlWb = Nothing
xlApp.Quit
Set xlApp = Nothing

For Counter = 1 To Nams.Count
.TransferSpreadsheet TransferType:=acImport, SpreadsheetType:=acSpreadsheetTypeExcel12, _
TableName:=DestTable, FileName:=SourcePath, HasFieldNames:=True, _
Range:=Nams(Counter)
Next

.SetWarnings True

End With

MsgBox "Done"

End Sub


I have only (at this point changed the folder source for the data and the table name in the Access DB.  See SourcePath and DestTable.  The module looks like it is gathering the data from the Excel spreadsheet, but I get this attached message when it begins the transfer of data to Access (I think).  

When using the debugging tool it shows the error occurring at:  

For Counter = 1 To Nams.Count
.TransferSpreadsheet TransferType:=acImport, SpreadsheetType:=acSpreadsheetTypeExcel12, _
TableName:=DestTable, FileName:=SourcePath, HasFieldNames:=True, _
Range:=Nams(Counter)
Next

I am stumped at this point.  Any thoughts on where my error is?  Thanks.  -
Capture.PNG
LVL 7
tomfarrarAsked:
Who is Participating?

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

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

[ fanpages ]IT Services ConsultantCommented:
Hi Tom,

Are you able to post a sample workbook that matches the layout of the data in your "MergeSheets.xlsm" workbook?  It does not necessarily have to be the same file, or you can mask the data if it is sensitive, but just the same number of columns (with the same column headings, if applicable), & a few examples rows, please.

Also, please can you confirm the structure of the [Table1] table where you are attempting to import the data?

Finally, please can you confirm which version of MS-Excel & which version of MS-Access you are using?

Thanks.
tomfarrarAuthor Commented:
The sample Excel file is attached.  The structure of the Access table is identical to the columns in the three tabs in the Excel workbook:

Column1
Column2
Column3
Column4

All the columns are defined as Short Text.  The Access Table is named "Table1".

The Microsoft Office version is 365 ProPlus.  Thanks.  - Tom
MergeSheets.xlsm
tomfarrarAuthor Commented:
Is the problem in this line?

xlWb.Names("xxx" & Counter).Delete
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

Patrick MatthewsCommented:
From the posted error message screencap, it appears the problem is that your collection has the *names* of the various named ranges, rather than the range addresses.  If this is the cause, here is the line at fault:

Nams.Add Nam.Name

Open in new window


Change that line to this, and tell us what happens:

Nams.Add Mid(Nam.RefersTo, 2)

Open in new window


(The Mid expression is to strip off the leading equal symbol.)
[ fanpages ]IT Services ConsultantCommented:
I'll let Patrick continue as he obviously has a better understanding of this (rather than me coming it at it completely new to the whole process), but just for reference; using MS-Access 2013, & MS-Excel 2013, when I run the code (using the sample workbook attached above), I receive the following message:

---
Run-time error: '2391':

Field 'TIME' doesn't exist in destination table 'Table1.'
---
tomfarrarAuthor Commented:
Here is the new error message after changing the line.
Capture2.PNG
[ fanpages ]IT Services ConsultantCommented:
* coming it at it = coming at it

:)
tomfarrarAuthor Commented:
TIME is not a field name, but sample data.  Field names in Access are:

Column1
Column2
Column3
Column4

Thanks..
[ fanpages ]IT Services ConsultantCommented:
Yes, I have my table [Table1] with four columns; [Column1], [Column2], [Column3], & [Column4] (as you advised above).
Dale FyeOwner, Developing Solutions LLCCommented:
1.  The following bad technique:

.SetWarnings False
.RunSQL "DELETE * FROM " & DestTable

If you are not extremely careful and don't have really good error handling, using .SetWarnings False can cause serious problems.  The more recent "best practice" is to use the Execute method:

Currentdb.Execute "DELETE FROM " & DestTable, dbfailonerror

The Execute method does not popup the warning messages, but does provide you with a method to trap errors encountered during the Execute process.  You just need to add sections of your error handler to accomodate those errors and continue processing in the appropriate way.

2.  The error you are getting obviously has to do with using the named range in your transferspreadsheet command.  
Personally, I normally just use the sheet name. I cannot recall ever trying that.  What I would recommend is some testing in the immediate window and searches on TransferSpreadsheet and Named Range.
tomfarrarAuthor Commented:
My Excel data has field names, but the

HasFieldNames:= False

in next to last line shows false.

Problem?  Thanks.
[ fanpages ]IT Services ConsultantCommented:
My Excel data has field names, but the

HasFieldNames:= False

in next to last line shows false.

Problem?  Thanks.


Sorry, I am lost.

Your code above states:

---
For Counter = 1 To Nams.Count
.TransferSpreadsheet TransferType:=acImport, SpreadsheetType:=acSpreadsheetTypeExcel12, _
TableName:=DestTable, FileName:=SourcePath, HasFieldNames:=True, _
Range:=Nams(Counter)
Next
---
Robert ShermanOwnerCommented:
From what I can gather, the code should be adding named ranges to the Excel workbook, in the pattern of "xxx" + counter.   This is why there is the xlWb.Save command later in the code.  For whatever reason, the range names are not being created or saved, thus the error message.

On a local copy I am working with, it DOES create and save the named ranges correctly, as after running the code given, I can open up the spreadsheet and then I see that there are named ranges in there of "xxx1", "xxx2", "xxx3".    The problem I get at this point is an error about "6" not being a field name in the destination table.  THAT is due to the fact the the xlWs.Range(...) line starts each range at row 2 and therefore the first row WITH the field names is not a part of each range.

Changing that to begin with xlWs.Range(xlWs.Cells(1, 1) [...]

At this point, I get through to the "Done" msgbox and the table has all the data populated.  

Will upload a working sample in just a minute...
tomfarrarAuthor Commented:
I am not grasping the named ranges comment within the TransferSpreadsheet.  As I mentioned I did start with Patrick Mathews original code from a while back (found in a search here).  My data will not have any named ranges in the Excel worksheets.  Thanks.
tomfarrarAuthor Commented:
My apology fanpages, I did at one time change that to "True".  The original code had it as false.  Sorry......  Still didn't seem to matter.
[ fanpages ]IT Services ConsultantCommented:
I am not grasping the named ranges comment within the TransferSpreadsheet.  As I mentioned I did start with Patrick Mathews original code from a while back (found in a search here).  My data will not have any named ranges in the Excel worksheets.  Thanks.

As Robert advised, & also as I found whilst testing locally, the named ranges are created as the code executes (after first being removed).

The TransferSpreadsheet statement requires the named ranges to be present in order to function correctly.

Please refer to Richard's comment for further guidance, especially the note about extended the range to include the first row (that contains the column/field names).

Also, please note Dale's warning; more valuable information.
Robert ShermanOwnerCommented:
This sample database contains pretty much the same module, except that I made the change to the xlWs.Range line as I indicated in my previous comment.  

I saved the sample excel sheet provided by the Asker as "Copy of MergeSheets.xlsm" on my desktop, and the line that has the path needs to be changed accordingly to point to your local copy.

For me, this works as expected.

(EDIT: I also agree about the SetWarnings issue that Dale mentioned as a general rule.. and suggest that once you get this working beyond the error message you were receiving, you consider this)
SampleDB.accdb
Robert ShermanOwnerCommented:
One additional thought just came to mind...   Because the code sets up named ranges in the Excel workbook, it needs to be both NOT read-only for any reason, AND not currently open in Excel.  

(At least I think having it open in Excel the first time I tried caused it to not work for me, as the named ranges weren't there after I ran the code with the file open.)
Patrick MatthewsCommented:
I got the following to work:

Sub GrabData()

Dim xlApp As Object
Dim xlWb As Object
Dim xlWs As Object
Dim LastRow As Long
Dim Addr As String
Dim Nam As Object
Dim Nams As Collection
Dim Counter As Long

Const SourcePath As String = "C:\Users\pmatthews\Documents\Personal\Experts Exchange\Question Files\Q_28696903.xlsm" 'change as needed
Const DestTable As String = "Table1" 'change as needed

Set Nams = New Collection

With DoCmd
.SetWarnings False
.RunSQL "DELETE * FROM " & DestTable

Set xlApp = CreateObject("Excel.Application")
Set xlWb = xlApp.Workbooks.Open(SourcePath)

For Each xlWs In xlWb.Worksheets
LastRow = xlWs.[a65536].End(-4162).Row - 2
Counter = Counter + 1
On Error Resume Next
xlWb.Names("xxx" & Counter).Delete
On Error GoTo 0
Set Nam = xlWb.Names.Add(Name:="xxx" & Counter, RefersTo:="='" & xlWs.Name & "'!" & _
xlWs.Range(xlWs.Cells(2, 1), xlWs.Cells(LastRow, 4)).Address)
Nams.Add Mid(Nam.RefersTo, 2)
Next

Set Nam = Nothing
Set xlWs = Nothing
xlWb.Save
xlWb.Close False
Set xlWb = Nothing
xlApp.Quit
Set xlApp = Nothing

For Counter = 1 To Nams.Count
.TransferSpreadsheet TransferType:=acImport, SpreadsheetType:=acSpreadsheetTypeExcel12, _
TableName:=DestTable, FileName:=SourcePath, HasFieldNames:=False, _
Range:=Replace(Nams(Counter), "$", "")
Next

.SetWarnings True

End With

MsgBox "Done"

End Sub

Open in new window


Note that I switched the HasFieldNames to False--since you indicated you wanted to start on the second row, it appears you wanted to exclude headers.

Apparently passing in the $ in the range (which Excel uses to indicate an absolute range reference) was causing Access some heartache, so I used an extra Replace to strip that.
[ fanpages ]IT Services ConsultantCommented:
Thanks Patrick.
Patrick MatthewsCommented:
Oh yeah, and please update the constant with the path and name of the source file :)
tomfarrarAuthor Commented:
Still working with it.....
Robert ShermanOwnerCommented:
Note that I did not have to do any replacement with the $ -- an issue I think that came about as a result of NOT using the named references?

If you want to exclude the fieldnames and, thus, the first row, changing the range back to its original (2, 1 [...] version and setting the HasFieldNames to false in the transfertext will give you the same result.

(Edit: provided the table already exists, which your code assumes, and the field names will be in the Excel sheet and will always match the field names as they are in the table, using the fieldnames from the spreadsheet should not be a problem and may be a good way to catch potential issues if the spreadsheets are coming in from some external source and all-of-a-sudden have a different column structure.)

(Edit2: so basically, if your table definition is already going to exist and your importing into this existing table, I think using the first row of field names would be the way to go, WHEREAS if you were importing into a non-existing new table you could go either way... it really depends on the source of the spreadsheets and what the table you are importing to is going to be used for afterwards..  would different field names screw up processing further down the pipeline, etc..)

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
tomfarrarAuthor Commented:
Yes, I believe it is working now.  I'd like to test it more extensively tomorrow with real data and an actual Access database.  

As for Dale's comment I assume he meant:

Replace :

.SetWarnings False
 .RunSQL "DELETE * FROM " & DestTable

With :

Currentdb.Execute "DELETE FROM " & DestTable, dbfailonerror

Right?
Robert ShermanOwnerCommented:
And yet one more thought...  my gut feeling on using the Replace to "fix" the range is that this could be a source of problems at some point.   Totally a gut feeling and I can't provide an example off the top of my head, but I think the original code is more resilient because it ends up pointing to actual range objects that Excel has essentially provided as "valid".

I could be completely wrong about this, but if there's any case where removing dollar signs would break the range string, that would be an issue.  

On that note, I will step away and eat my dinner before it gets cold to avoid the dirty looks I will get at the table.  :D
tomfarrarAuthor Commented:
Hmmmm.... When I use data similar to what is real data (can't share real data), the module errors out.  I have attached the sample database (Robert provided), and the new data I am trying to run through the process.  

Debugging shows problem in:

Set Nam = xlWb.Names.Add(Name:="xxx" & Counter, RefersTo:="='" & xlWs.Name & "'!" & _
xlWs.Range(xlWs.Cells(1, 1), xlWs.Cells(LastRow, 4)).Address)
Nams.Add Nam.Name

Wish I better understood what was going on here, and eventually I will with your help.  Then maybe next time I can get there myself.  Thanks.  - Tom
SampleDB-2.accdb
MergeDatabase.xlsx
tomfarrarAuthor Commented:
Seems to be working now, though I am not sure what the problem(s) was/were.  Thanks.
Robert ShermanOwnerCommented:
I still suspect that you were hitting some kind of issue where the excel file was not saving after the code went in and set those named ranges.   Any chance you had Excel open while you ran it the times you got the error?
tomfarrarAuthor Commented:
Hi Robert - That is a possibility.  But I believe it is working well now.  As a quick follow-up, can you explain this coding from the module?

For Each xlWs In xlWb.Worksheets
LastRow = xlWs.[a65536].End(-4162).Row - 2

I imagine the code was written when Excel only had 65,536 rows.  I assume the "a" is for column A, but I am not sure.  I'd like to update the coding for the newer version of Excel but I don't understand the LastRow = .....   Particularly (-4162).Row-2.  Thanks
tomfarrarAuthor Commented:
Thanks Patrick for the original code.  Thanks Richard for getting me to the end on this question.  Also thanks [fan pages] for moderating and Dale Fye for good input.  I appreciate all of your help.  - Tom
[ fanpages ]IT Services ConsultantCommented:
Glad you/we got there in the end, Tom.

Good luck with the rest of your project.
Robert ShermanOwnerCommented:
To be honest, Excel programming is often a head-scratcher for me...  From what I can tell, calling the .End method performs the equivalent of the keyboard sequence of pressing the End key followed by a directional arrow key.   So, in the code above, Lastrow is being set by going to cell a65536 and then using the End, Up-Arrow sequence to move to the last row in that column that contains data.  The -4162 value is equal to the Microsoft constant xlUp, and just indicates the direction to move in when searching for the "end" of a range.

It's worth noting that the code is subtracting 2 rows.  Without knowing the original source of the code, I can only assume this was to take into account summary rows at the bottom of the sheet and exclude them from the import.  You might want to take a closer look at that, just to be sure that you are not losing the last couple of rows as a result of this.  

(you might be right about why the original code author picked 65536, this probably was in the past an effective way of saying "the bottom of the sheet")
tomfarrarAuthor Commented:
Thanks, Robert.  And thanks [ fanpages ] for the encouragement.  I hope I get the opportunity to work with you again soon.  - Tom
[ fanpages ]IT Services ConsultantCommented:
:)

It is probably safer not to assume a specific number of rows or columns, but use;

Rows.Count
and
Columns.Count

In MS-Excel 2007-2013, Rows.Count will be 1,048,576, & Columns.Count will be 16,384.

I suspect, as discussed above, that MS-Excel 97-2003 was assumed in the original code listing, where the total number of Rows is 65,536 & the total number of Columns is 256.

Prior to MS-Excel 97, the number of Rows was 16,384.

Since MS-Excel 2007 has been available, I have also begun "future-proofing" my code by prefixing (fully-qualifying) Rows.Count & Columns.Count with a reference to a worksheet, for example:

ActiveSheet.Rows.Count
or
Worksheets("Sheet1").Columns.Count
etc.

If MS-Excel ever allows different worksheets to have a differing total number of rows &/or columns, then my code will continue to function as intended.  This may never be implemented, of course, but at least I can rest easy in the knowledge that my code will not need updating if the rows &/or columns increase again.
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.