Solved

Appending Excel range to Access table

Posted on 2014-03-12
16
378 Views
Last Modified: 2014-03-13
Hi again,
Still struggling to get this right.

Managed to get the Excel macro to run while in Access, but can't get the correct syntax to import the "usedrange" from sheet "SageDump" to my table.
Here's the code
Dim objXL As Excel.Application
Dim objWkb As Excel.Workbook
Dim objSht As Excel.Worksheet
Dim strFileName As String
Dim ImportRange As Range

strFileName = (GetMyPath() & "Interface2.xlsm")

    Set objXL = New Excel.Application
    Set objWkb = objXL.Workbooks.Open(strFileName, , False, , "password")

'run excel macro
    objXL.Run "Upload2Dbase"

'import the data from sheet named "SageDump"
     ImportRange = objWkb.Worksheets("SageDump").UsedRange
     DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, _
     "tblDeliveriesDue", strFileName, True, ImportRange
            
'destroy objects
Set objSht = Nothing
Set objWkb = Nothing
Set objXL = Nothing

Open in new window

As I say, still struggling with Object variable or With block not set.
As always, any help is much appreciated.
0
Comment
Question by:Stephen Byrom
  • 10
  • 4
  • 2
16 Comments
 
LVL 33

Expert Comment

by:Norie
ID: 39924850
In that code ImportRange is going to be an array, what you actually need for TransferSpreadsheet is a range reference.

This should set ImportRange to the range reference of the used range on 'SageDump'.
ImportRange = "SageDump!" & objWkb.Worksheets("SageDump").UsedRange.Address

Open in new window


PS You'll need to declare ImportRange as String instead of Range.
0
 
LVL 1

Author Comment

by:Stephen Byrom
ID: 39924879
Thanks for the advice Imnorie,
I get an error when trying to run it, see attached image.
It looks like there are two $$ symbols instead of one, but I can't see where to amend it in the code.

It gets the used range but there are two "$$" before the A1
error.png
0
 
LVL 33

Expert Comment

by:Norie
ID: 39924914
Is the name of the worksheet definitely 'SageDump', with no spaces?
0
 
LVL 1

Author Comment

by:Stephen Byrom
ID: 39924920
Yes definitely.
I steer clear of spaces with names of sheets etc
As I say, it gets the correct used range, but I think it's the double $$ before the A1
0
 
LVL 1

Author Comment

by:Stephen Byrom
ID: 39924935
I removed the Bang, (exclamation mark) from "SageDump!" and it removed the extra $, but still no joy,
same error message
0
 
LVL 1

Author Comment

by:Stephen Byrom
ID: 39925630
Tried this (among many others)
Dim strFileName As String
Dim ImportRange As String
Dim LR As Long
strFileName = (GetMyPath() & "Interface2.xlsm")
    Set objXL = New Excel.Application
    Set objWkb = objXL.Workbooks.Open(strFileName, , False, , "thisismergonspassword")
        objXL.Run "Upload2Dbase"
        Set objSht = objWkb.Worksheets("SageDump")
            LR = objSht.Range("A1048576").End(xlUp).Row
            ImportRange = objSht.Range("A2:G" & LR)
DoCmd.TransferSpreadsheet , acImport, acSpreadsheetTypeExcel9, _
    "tblDeliveriesDue", objWkb, True, ImportRange

    'destroy objects
    Set objSht = Nothing
    Set objWkb = Nothing
    Set objXL = Nothing

Open in new window

But still to no avail
0
 
LVL 1

Author Comment

by:Stephen Byrom
ID: 39925645
Now I'm getting a "typeMismatch" and I've double-checked the 7 fields in the table and the 7 columns in the sheet.
Dim strFileName As String
Dim ImportRange As String
Dim LR As Long
strFileName = (GetMyPath() & "Interface2.xlsm")
    Set objXL = New Excel.Application
    Set objWkb = objXL.Workbooks.Open(strFileName, , False, , "thisismergonspassword")
        objXL.Run "Upload2Dbase"
        Set objSht = objWkb.Worksheets("SageDump")
            LR = objSht.Range("A1048576").End(xlUp).Row
            ImportRange = objSht.Range("A2:G" & LR)
DoCmd.TransferSpreadsheet , acImport, acSpreadsheetTypeExcel9, _
    "tblDeliveriesDue", objWkb, True, ImportRange

Open in new window

0
 
LVL 1

Author Comment

by:Stephen Byrom
ID: 39925774
Variations on a theme
Dim strFileName As String
strFileName = (GetMyPath() & "Interface2.xlsm")
    Set objXL = New Excel.Application
    With objXL
        Set objWkb = .Workbooks.Open(strFileName, , False, , "password")
                objXL.Run "Upload2Dbase"
        Set objSht = objWkb.Worksheets("SageDump")
               DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12, _
               "tblDeliveriesDue", objWkb, True, (objSht!NewDump)
     End With
     
    'destroy objects
    Set objSht = Nothing
    Set objWkb = Nothing
    Set objXL = Nothing

Open in new window

I have added a routine that names the required range in the excel macro "upload2dbase" with the line "WBK1.Names.Add Name:="NewDump", RefersTo:=Range("A2:G" & LR)" and tried to refer to that range in the transferspreadsheet, but still no luck
0
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

 
LVL 49

Expert Comment

by:Gustav Brock
ID: 39925784
First, you should not run the import inside the code that runs the macro.
You could do so, but then you should read from the worksheet directly as you already have it opened.

Second, the "range" is a string. It's the name of the range, not the range:

http://www.experts-exchange.com/Database/MS_Access/Q_28380321.html

/gustav
0
 
LVL 1

Author Comment

by:Stephen Byrom
ID: 39925869
thanks Gustav
I have amended the code to
Dim strFileName As String
Dim ImportRange As String
strFileName = (GetMyPath() & "Interface2.xlsm")
    Set objXL = New Excel.Application
        With objXL
            Set objWkb = .Workbooks.Open(strFileName, , False, , "thisismergonspassword")
                objXL.Run "Upload2Dbase"
            Set objSht = objWkb.Worksheets("SageDump")
            ImportRange = objSht.Range("NewDump")
        End With

DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, _
"tblDeliveriesDue", strFileName, True, ImportRange

Open in new window

and I added the closing few lines to the excel macro
WS1.Range("E2:E" & LR).NumberFormat = "dd/mm/yyyy"
WS1.Range("G2:G" & LR).NumberFormat = "@"
WBK1.Names.Add Name:="NewDump", RefersTo:=Range("A1:G" & LR)

Open in new window

so that it wouldn't have any data type conflicts with the table fields, but it still won't run. I get a "typemismatch error.
I am thinking it may be easier to import the excel range with a separate module using the select into sql. I have been searching on google and it may be an answer if I can work it out.
0
 
LVL 49

Expert Comment

by:Gustav Brock
ID: 39925882
As you name the range "NewDump", you should use that Named Range as the parameter:

DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, _
"tblDeliveriesDue", strFileName, True, "NewDump"

However, it will not appear before you save the worksheet.

/gustav
0
 
LVL 1

Author Comment

by:Stephen Byrom
ID: 39925899
Thanks for sticking with me on this Gustav,
But I'm still struggling.
I added the line WBK1.Save to the excel macro and changed my Access procedure to this
Dim strFileName As String
'Dim ImportRange As String
strFileName = (GetMyPath() & "Interface2.xlsm")
    Set objXL = New Excel.Application
        With objXL
            Set objWkb = .Workbooks.Open(strFileName, , False, , "thisismergonspassword")
                objXL.Run "Upload2Dbase"
        End With

'destroy objects
    Set objSht = Nothing
    Set objWkb = Nothing
    Set objXL = Nothing
    Exit Sub
    
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, _
"tblDeliveriesDue", strFileName, True, "NewDump"

Open in new window

and I still get nothing, but this time Excel "hangs" and I have to close it using the task manager.
I also tried importing the range manually to make sure there were no name conflicts by using the Access ribbon "Import Excel" and that worked fine.
0
 
LVL 49

Accepted Solution

by:
Gustav Brock earned 500 total points
ID: 39925919
You should be able to run the DoCmd.TransferSpreadsheet command manually after you have saved and closed the worksheet.

It is intended to be called on its own, not within some automation code.

/gustav
0
 
LVL 1

Author Comment

by:Stephen Byrom
ID: 39925962
OH MY GOD!!!!!!!!!!!!!!!

It's finally working!
it was the saving and closing of the workbook from within the access procedure that did the trick. I was trying to save and close the workbook from within the Excel macro, which didn't work.
the code
Dim strFileName As String
strFileName = (GetMyPath() & "Interface2.xlsm")
    Set objXL = New Excel.Application
        With objXL
            Set objWkb = .Workbooks.Open(strFileName, , False, , "thisismergonspassword")
                objXL.Run "Upload2Dbase"
                objWkb.Save
                objWkb.Close
        End With

'destroy objects
    Set objSht = Nothing
    Set objWkb = Nothing
    Set objXL = Nothing

DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, _
"tblDeliveriesDue", strFileName, True, "NewDump"

Open in new window

WORKS!!! Thank you so much Gustav
0
 
LVL 1

Author Closing Comment

by:Stephen Byrom
ID: 39925964
Brilliant!
0
 
LVL 49

Expert Comment

by:Gustav Brock
ID: 39925967
You are welcome!

/gustav
0

Featured Post

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

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

Suggested Solutions

A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…

910 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

20 Experts available now in Live!

Get 1:1 Help Now