Access VBA not recognizing range in Excel spreadsh

问题:

I am having a most frustrating time time with the DoCmd.TransferSpreadsheet method. I have a workbook with multiple worksheets in which users are updating data and I have a script that puts all the records back into a single sheet, links the spreadsheet, and updates the data in my Access DB. My problem is in the Range parameter. I pass the following string and get the following error:

DoCmd.TransferSpreadsheet TransferType:=acLink, SpreadsheetType:=acSpreadsheetTypeExcel12Xml, _
                              TableName:=linkSheet, fileName:=Wb.Path & "\" & Wb.name, _
                              HasFieldNames:=True, Range:="AccessUpdate!updateTable"
The Microsoft Access database engine could not find the object 'AccessUpdate$updateTable'. Make sure the object exists and that you spell its name and the path name correctly. If 'Access_Update$updateTable' is not a local object, check your network connection or contact the server administrator.

I can't seem to understand why it substitutes the dollar sign for the bang. Any other help in understanding how to specify the range would also be appreciated.

Thanks!

回答1:

I know this is an year old question but it is an almost timeless problem.

I'm trying to do the same from the Excel side and bumping into the same problem. Access switching the sheet separator "!" for "$"

I found that this is a bug from Access 2000 that was never corrected. Or better, it was partially corrected at some point. So depending on your Access build and the size of the range [yes, size, since this is a bug from Access 2000] the solutions provided by Cisco or HansUp will work.

Another sources explaining the problem and a similar solution is provided by the MS$ themselves https://answers.microsoft.com/en-us/office/forum/office_2007-access/transferspreadsheet-error-3011-can-not-file-sheet/980b2dc1-9ee1-4b3e-9c3c-a810f1428496 with the help of Bob Larson Former Access MVP (2008-2010) [see his very last post]

Now, if your range is on a different sheet with more than 65536 rows, this bug will come back. See here for reference Funny enough, if this is Sheet1 [yes, index 1 of all sheets] it will work with any range size. But any other sheet it wil fail.

This was my original range: BASE!A2:X68506, named REF_ACCESS. BASE is my Sheet5. Access 2010 & Excel 2010

I tried ActivateSheet, assign to string inside command, assign to string outside command, replace(,"$","!""), nothing worked. Even on Office 2016 from a friend

If I use "BASE!A2:X64506", it works. If I use "A2:X68506", Access assumes Sheet1 and works. Attention that all ranges do not have "$", but I guess you already know that

My last test was something like this monster

DoCmd.TransferSpreadsheet TransferType:=acImport, SpreadsheetType:=9, TableName:="TEST", Filename:=ThisWorkbook.FullName, HasFieldNames:=False, Range:=Worksheets("BASE").Name & "!" & Replace(Left(Worksheets("BASE").Range("REF_ACCESS").Address, Len(Worksheets("BASE").Range("REF_ACCESS").Address) - 1), "$", "")

A test that using my range within the 65536 row limit [6553 to be precise] would work. And it did.

So I see solutions with only two options for now. Either copy your range to Sheet1 or another sheet, as RyanM did, or divide your range in multiple DoCmd with 65536 rows.

I know it is long. Sorry, this was 2 full days looking for an answer without any real solution. I hope this helps other people with the same problem.

回答2:

I tried multiple methods for getting around this without making major modifications to my code but with no avail. I did come up with a solution but it is rather resource intensive and messy. However, in case someone has a similar issue, I will post it here. I wound up separating my update sheet into it's own file from the rest of the workbook and linking that file. This prevented Access from trying to link a different sheet and got me around the whole Range issue. I know it's not elegant or efficient but it worked. If I figure out a cleaner way I'll post it here.

 Set xl = Wb.Parent
    xl.ScreenUpdating = False
    xl.DisplayAlerts = False
    strFile = mypath & "\TempIss.xlsx"
    For i = 1 To Wb.Worksheets.count
        If InStr(1, Wb.Worksheets(i).name, "Update", vbTextCompare) > 0 Then
            tableId = i
            Exit For
        End If
    Next i
    If tableId = 0 Then
        MsgBox "This workbook does not seem to have the necessary worksheet for updating " & _
               "the Participant Issues Log in Access.", vbInformation, "Uh oh..."
        Exit Function
    Else
        Set upWs = Wb.Worksheets(i)
        upWs.Select
        upWs.Copy
        xl.ActiveSheet.SaveAs fileName:=strFile
        xl.ActiveWorkbook.Close
        Call rmSheet(Wb, "AccessUpdate")
        xl.ScreenUpdating = True
        linkSheet = "tempIssLog"
    DoCmd.TransferSpreadsheet TransferType:=acImport, SpreadsheetType:=acSpreadsheetTypeExcel12Xml, _
                              TableName:=linkSheet, fileName:=strFile, _
                              HasFieldNames:=True
    Kill (strFile)

回答3:

If the range is a named range (in Excel) follow the instruction above (HansUp comment).

If the range is defined in MS-Access be sure to pass a string (something like "A1:G12") and not the control name.

Dim StrRange as variant
Dim NameofMySheet as string

NameofMySheet = "xxxxxx"   ' <- Put here the name of your Excel Sheet
StrRange = NameofMySheet & "!" & "A1:G12"

DoCmd.TransferSpreadsheet TransferType:=acLink, SpreadsheetType:=acSpreadsheetTypeExcel12Xml, _
                          TableName:=linkSheet, fileName:=Wb.Path & "\" & Wb.name, _
                          HasFieldNames:=True, Range:= StrRange

Note 1: StrRange with no quotes!