Solved

Appending Excel range to Access table

Posted on 2014-03-12
16
380 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
Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

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

Live: Real-Time Solutions, Start Here

Receive instant 1:1 support from technology experts, using our real-time conversation and whiteboard interface. Your first 5 minutes are always free.

Question has a verified solution.

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

As tax season makes its return, so does the increase in cyber crime and tax refund phishing that comes with it
Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
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…
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

786 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