Excel to Access using Named Range 1

[bam720]

I have a spreadsheet and a named Range called "menu" which is defined as

=OFFSET(Sheet1!$B$10,0,0,COUNTA(Sheet1!$B:$B),6)

Which starts at B10 and then next 5 columns and is dynamic in length. this works and properly selects the desired information. My trouble is that when I open access to try and import this data it doesn't find it as a named range. If you know how this can work please help. My other idea was to just copy only this data into a second sheet. I'm not sure how to do this either. Thanks in advance.

 

[firefytr]

Not sure, but Access may not see dynamic ranges correctly. You could use VBA to copy the (current) dynamic range to a blank sheet in the workbook...

Sub CopyDynamicRange()
    Dim ws as worksheet, wsNew as worksheet
    Set ws = Sheets("Sheet1") 'sheet with named range
    Set wsNew = Sheets.Add
    ws.range("YourRangeNameHere").Copy wsNew.Range("A1")
    Application.Cutcopymode = False
End Sub

The range should be contiguous. Then just import from the new sheet instead of a named range. I'm not as familiar with Access as I am with Excel, so that may not be optimal for import, but an option.

HTH

-----------
Regards,
Zack Barresse

 

[fneily]

I created a little list in Excel, there columns that were EmmployeeID, FirstName, LastName.
I created a table in an Access database with the same field names as the Excel Column names. I made EmployeeID an autonumber.
I then created a named range like yours and named it menu.
=OFFSET(Sheet1!$A$1,0,0,COUNTA(Sheet1!$A:$A),3)

Then I created the following sheet macro in Excel:
Sub ExceltoAccess()
Dim appAccess As New Access.Application
Dim strPath As String
Dim strFile As String
Set appAccess = Access.Application
strPath = "c:\"
strFile = "test2.mdb"
strDBName = strPath & strFile
appAccess.OpenCurrentDatabase strDBName
DoCmd.TransferSpreadsheet acImport, 8, "tblEmployeeTransfer", "C:\transfertest.xls", True, Menu
End Sub

As you can see, I used the macro name in the TransferSpreadsheet command. And it worked.

Note: Access had to be closed.

 

[fneily]

I forgot to mentioned that you must check off the references to Microsoft Access library(latest version) and DAO 3.6 library under Tools - References in VBA for the code to work.

 

[bam720]

Ok So I tried using your macro, but stumbled upon an error. I have everything renamed to macth my settings. It's opening the database just fine, but for some reason it won't import. I have all the fields the matching the columns in excel. I appreciate the help and more of a start in the right direction. I will keep working on it. Thanks.

 

[fneily]

What is the error message? Do you know how to step through the code in VB Editor? Make sure you have the references checked off.(Tools-References)
The table fields and Excel column headings are spelled the same?

 

[bam720]

Here are my changes.

Sub ExceltoAccess()
Dim appAccess As New Access.Application
Dim strPath As String
Dim strFile As String
Set appAccess = Access.Application
strPath = "c:\Database\"
strFile = "MenusTest.mdb"
strDBName = strPath & strFile
appAccess.OpenCurrentDatabase strDBName
DoCmd.TransferSpreadsheet acImport, 8, "MenuImport", "C:\test.xls", True, Menu
End Sub

as you cna see just the minor things.

My Excel header names are:
Group 1
Group 2
Group 3
Item Name
Price
Printer Group
ID
Type

My "MenuImport" table fields are:
Group 1
Group 2
Group 3
Item Name
Price
Printer Group
ID (Primary Key)
Type

The Error I am getting is "Run-time error '2391':
Application-defined or object-defined error"

It is opening Access and its obening the correct database, but it isn't importing anything. Thanks for the help.

 

[bam720]

I got it to work by maiing field names in my table
F1
F2
F3
F4
F5
F6
F7
F8
F9
I am still reworking it to get it exactly perfect but I will get there. Thanks for the help and star for you.

 

[bam720]

I've got a finished version so I thought I'd share.

Sub ExceltoAccess()
Dim appAccess As New Access.Application
Dim strPath As String
Dim strFile As String
Set appAccess = Access.Application
strPath = "c:\Database\"
strFile = "MenusTest.mdb"
strDBName = strPath & strFile
appAccess.OpenCurrentDatabase strDBName
DoCmd.TransferSpreadsheet acImport, 8, "MenuImport", "C:\test.xls", True, "Sheet2!A:H" 'Named Range Wasn't working becuase I copied it to another sheet and was tyring to use another named range.  This just copies all the columns
appAccess.CloseCurrentDatabase 'Access put a lock on the db.  Don't forget to close.
MsgBox "Export Successful!"
End Sub

 

[fneily]

Oh yeah, I forgot to close the database - thanks. I also noticed you did the range A:H so it can handle any length - very good. I haven't tried this yet, but your original column/field headings had spaces in them. I'm wondering if that could effect the transfer? Test it later.