Solved

Appending Excel range to Access table

Posted on 2014-03-12
16
377 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
Comment Utility
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
Comment Utility
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
Comment Utility
Is the name of the worksheet definitely 'SageDump', with no spaces?
0
 
LVL 1

Author Comment

by:Stephen Byrom
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

 
LVL 49

Expert Comment

by:Gustav Brock
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
Brilliant!
0
 
LVL 49

Expert Comment

by:Gustav Brock
Comment Utility
You are welcome!

/gustav
0

Featured Post

Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

Join & Write a Comment

Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.
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…

771 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

11 Experts available now in Live!

Get 1:1 Help Now