Results 1 to 11 of 11
  1. #1
    Kluaoha is offline Novice
    Windows 10 Access 2013 32bit
    Join Date
    Jul 2017
    Posts
    20

    Issue Importing with TransferSpreadsheet

    I'm getting an issue when using the 'range' argument of TransferSpreadsheet. Basically, there is a named range called 'Data' that extends to the maximum row, but only the first few hundred rows are filled. So, to avoid importing a million blank rows, I found the last non-empty cell and used that to create the 'range' argument:
    Code:
    Dim XLsheet As Excel.Worksheet
    Dim XLrange As Range
    Dim newRange As String
    Dim ImportPath As String
    ...
    With XLsheet
        Set XLrange = .Range("Data")
        newRange = "'" & .name & "'!R" & XLrange.Row & "C" & XLrange.Column & ":R" & _
            .cells(.Rows.count, XLrange.Column).End(xlUp).Row & "C" & .cells(XLrange.Row, .Columns.count).End(xlToLeft).Column
    End With
    Debug.Print newRange
    DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12Xml, "TempImportTable", ImportPath, False, newRange
    The Debug.Print shows
    'Sheet1'!R5C7:R214C23
    which I can manually confirm to be correct, but I am getting the error:


    3011
    The Microsoft Access database engine could not find the object ''Sheet1'$R5C7:R214C23'. Make sure the object exists and that you spell its name and the path name correctly. If ''Sheet1'$R5C7:R214C23' is not a local object, check your network connection or contact the server administrator.
    So it seems like Access it changing the exclamation point to a dollar sign.. what is going on?
    Thanks in advance.

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    Link the spreadsheet as an external table,
    then run an append query to 'import' and ignore the blank rows.

  3. #3
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    I haven't used Excel automation in a few years, so it is time for troubleshooting.

    First, "Sheet1" must be the active sheet. Then try commenting out the transferspreadsheet line and use a hard coded range:
    Code:
    DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12Xml, "TempImportTable", ImportPath, False, "R5C7:R214C23"
    If that works, change the code to (again, the code should make "Sheet1" the active worksheet):
    Code:
    Dim XLsheet As Excel.Worksheet
    Dim XLrange As Range
    Dim newRange As String
    Dim ImportPath As String
    ...
    With XLsheet
        Set XLrange = .Range("Data")
    '    newRange = "'" & .name & "'!R" & XLrange.Row & "C" & XLrange.Column & ":R" & _
            .cells(.Rows.count, XLrange.Column).End(xlUp).Row & "C" & .cells(XLrange.Row, .Columns.count).End(xlToLeft).Column
    
        'sheet reference removed
        newRange = "R" & XLrange.Row & "C" & XLrange.Column & ":R" & _
            .cells(.Rows.count, XLrange.Column).End(xlUp).Row & "C" & .cells(XLrange.Row, .Columns.count).End(xlToLeft).Column
    
    End With
    Debug.Print newRange
    DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12Xml, "TempImportTable", ImportPath, False, newRange




    You might also find inspiration at Ken Snell's site

  4. #4
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    I didn't know you could use the RC syntax to define a range on the Access vba side of things.

    I don't have the answer to your current issue, but I can make a suggestion. Create a dynamic range in the sheet and then it won't matter how many rows contain data. The named range is as short or tall as there is data. I haven't done this with one whose columns can fluctuate, just the rows. I suppose it's possible to go both ways if you have to. Then your range reference is simpler.

    I suppose you could set at least 1 target table field to be required and not allow empty strings, which from what I've read, should prevent empty rows - although you might have to turn off warnings temporarily Then there is the option expressed in post 2
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  5. #5
    Kluaoha is offline Novice
    Windows 10 Access 2013 32bit
    Join Date
    Jul 2017
    Posts
    20
    Thanks. It turns out that even when I do XLapp.ReferenceStyle = xlR1C1, it doesn't recognize R1C1 ranges, but I worked when I changed it to A1.

  6. #6
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Great! Happy you figured it out.
    And it is good to know that you have to use A1 reference style for the import option.

  7. #7
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Quote Originally Posted by Kluaoha View Post
    worked when I changed it to A1.
    That's almost unfortunate. Once I learned dynamic ranges I was hooked. You may not bother with that now

  8. #8
    Kluaoha is offline Novice
    Windows 10 Access 2013 32bit
    Join Date
    Jul 2017
    Posts
    20
    Sorry to open this back up, but I'm having issues when using dynamic named ranges. The ranges themselves are working fine, but TransferSpreadsheet is not cooperating.
    Code:
    DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12Xml, "TempLanguageImport", ImportPath, False, Range:="Data"
    Which resulted in the same error as above, the database engine not being able to find "Data". I also tried, among others:
    Code:
    DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12Xml, "TempImportTable", ImportPath, False, Range:="'Sheet1'!" & XLsheet.Range("Data").Address
    DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12Xml, "TempImportTable", ImportPath, False, Range:="'" & XLsheet.name & "'!" & XLsheet.Range("Data").Address
    Both resulting in the database engine being unable to find the object ''Sheet1'$$A$5:$AG$2384', seemingly changing my exclamation point into a dollar sign. I only resolved this in the first place because it worked when I put the literal string address in the code, but obviously that won't get me very far.

  9. #9
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Admittedly I've only used the dynamic range in conjunction with Automation (from the Access side). Given that M$ states the named range parameter should work, I'm at a loss as to why a DR would be an issue, especially since no version of what you've tried seems to work. The only things I can think of at the moment is that
    - your dr is at the workbook level and not the sheet itself. Named ranges an be at either level, but I believe only a named range at the sheet level would work.
    - or there is something else inherently wrong in your code.

    Possibly posting your entire procedure would help (a few lines here and there don't shed much light on what you've got) or post a zipped copy of the db and workbook or reasonable facsimile thereof, with some dummy data in both files if privacy is an issue. We can't tell if what you have is even remotely close to post 3.

  10. #10
    Kluaoha is offline Novice
    Windows 10 Access 2013 32bit
    Join Date
    Jul 2017
    Posts
    20
    Here's the full code. A few things have been changed for privacy, but nothing that would effect the functionality.
    Code:
    Option Compare Database
    Private Const ImportFile As String = "[my excel file name]"
    Private Sub Button_Click()
        Dim XLapp As Excel.Application
        Dim XLwbk As Excel.Workbook
        Dim XLsheet As Excel.Worksheet
        Dim XLrange As Range
        Dim newRange As String
        
        On Error GoTo ImportError
        DoCmd.Hourglass True
        ImportPath = CurrentProject.Path & "\" & ImportFile
        If Len(Dir(ImportPath, vbNormal + vbReadOnly + vbHidden + vbSystem)) <= 0 Then ' Import file not found
            MsgBox "file not found: " & ImportPath, vbCritical
            GoTo ImportCleanup
        End If
        Set XLapp = New Excel.Application
        Set XLwbk = XLapp.Workbooks.Open(ImportPath)
        Set XLsheet = XLwbk.Sheets(1) ' Testing with the first sheet for now
        DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12Xml, "TempImportTable", ImportPath, False, Range:="'" & XLsheet.name & "'!" & XLsheet.Range("Data").Address
        DoCmd.Hourglass False
        MsgBox "Import successful"
        XLwbk.Close False
        GoTo ImportCleanup
    ImportError:
        DoCmd.Hourglass False
        Debug.Print Err.number, Err.Description
    Import Cleanup:
        Set XLapp = Nothing: Set XLwbk = Nothing: Set XLsheet = Nothing: Set XLrange = Nothing
    End Sub

  11. #11
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    The only 'issues' I might have with your code is
    - I don't see Option Explicit, which means "Require Variable Declaration" option isn't checked. Some wiser than I have stated if you don't have this, you deserve what you get.
    - you declare a Range variable but don't use it. If the worksheet has a named dynamic range, then why not just provide it as the range parameter for the TransferSpreadsheet
    function as in

    Set XLrange = XLsheet.Range("Data")
    ...."TempImportTable", ImportPath, False, "Data"

    rather than
    ...."TempImportTable", ImportPath, False, Range:="'" & XLsheet.name & "'!" & XLsheet.Range("Data").Address
    (which may be the issue, but I'm rusty on the exact syntax using your method).

    Another method of referencing the range object using late binding and what I believe is an Excel library object defined by the cells that hold data would look like
    Dim rng as Object
    Set rng = XLsheet.UsedRange

    Possibly none of that will help. Unless someone else chimes in, I guess you are at the point of uploading files. Gotta say when I reply raising possibilities that aren't addressed, I lean towards working with file objects instead of dancing around the unknown. F'rinstance, I still don't know where the dynamic range resides. Don't take it personally - you are not the first. It happens a lot, and maybe that's the rub.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Issue in Importing datatabse-VBA
    By ajay.2613 in forum Access
    Replies: 3
    Last Post: 03-16-2016, 10:34 AM
  2. TransferSpreadsheet issue
    By Xarkath in forum Programming
    Replies: 6
    Last Post: 12-03-2015, 01:35 PM
  3. Importing Excel file: ADODB, DAO or DoCmd.TransferSpreadsheet
    By Monterey_Manzer in forum Import/Export Data
    Replies: 3
    Last Post: 08-13-2013, 11:34 AM
  4. Importing Spreadsheet Field Names Issue
    By collekt in forum Import/Export Data
    Replies: 2
    Last Post: 02-27-2013, 01:43 PM
  5. Issue with importing excel data
    By Jrbeene86 in forum Import/Export Data
    Replies: 0
    Last Post: 03-27-2012, 08:32 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums