[Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 388
  • Last Modified:

Appending Excel range to Access table

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
Stephen Byrom
Asked:
Stephen Byrom
  • 10
  • 4
  • 2
1 Solution
 
NorieCommented:
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
 
Stephen ByromAuthor Commented:
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
 
NorieCommented:
Is the name of the worksheet definitely 'SageDump', with no spaces?
0
Nothing ever in the clear!

This technical paper will help you implement VMware’s VM encryption as well as implement Veeam encryption which together will achieve the nothing ever in the clear goal. If a bad guy steals VMs, backups or traffic they get nothing.

 
Stephen ByromAuthor Commented:
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
 
Stephen ByromAuthor Commented:
I removed the Bang, (exclamation mark) from "SageDump!" and it removed the extra $, but still no joy,
same error message
0
 
Stephen ByromAuthor Commented:
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
 
Stephen ByromAuthor Commented:
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
 
Stephen ByromAuthor Commented:
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
 
Gustav BrockCIOCommented:
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
 
Stephen ByromAuthor Commented:
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
 
Gustav BrockCIOCommented:
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
 
Stephen ByromAuthor Commented:
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
 
Gustav BrockCIOCommented:
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
 
Stephen ByromAuthor Commented:
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
 
Stephen ByromAuthor Commented:
Brilliant!
0
 
Gustav BrockCIOCommented:
You are welcome!

/gustav
0

Featured Post

 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

  • 10
  • 4
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now