访问VBA无法识别Excel电子表格中的范围 [英] Access VBA not recognizing range in Excel spreadsheet

问题描述

我使用DoCmd.TransferSpreadsheet方法的时间最令人沮丧.我有一个包含多个工作表的工作簿，用户在其中更新数据，并且有一个脚本将所有记录放回到一个工作表中，链接电子表格，并更新Access DB中的数据.我的问题是在Range参数中.我传递以下字符串并得到以下错误:

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.

谢谢！

推荐答案

我尝试了多种方法来解决此问题，但未对我的代码进行重大修改，但无济于事.我确实提出了一个解决方案，但是它非常耗费资源且混乱.但是，如果有人遇到类似问题，我将在此处发布.我结束了将更新表从工作簿的其余部分分离到它自己的文件中并链接该文件的过程.这阻止了Access尝试链接其他工作表，并使我无法解决整个Range问​​题.我知道它不是优雅或高效的，但确实有效.如果我想出一种更简洁的方法，请在此处发布.

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)

这篇关于访问VBA无法识别Excel电子表格中的范围的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！