对话框文件选择器中文件的VBA路径，其中包括:特定工作表+此工作表上的单元格范围

Question

下午好!我需要创建一个VBA代码，它会打开一个对话框拿起一个文件，然后它会得到文件路径，其中将包括特定的工作表，这是固定的，称为"新利率";单元格的范围也是固定的-从A:AP或R2C1:R1048576C42。为了消除用户错误，工作表和范围应固定。此外，该文件路径将用作数据透视表的数据透视源。

现在，我已经创建了一个VBA宏，它允许我获得一个没有工作表和单元格范围的文件路径。示例如下:

"https://sharepoint.com/sites/SharedDocuments/2021/08_Aug/Name_08_2021.xlsb">

所以，因为它不包括工作表+单元格的范围，它不能用于枢轴源，这就是为什么我需要得到这样的输出:

"https://sharepoint.com/sites/SharedDocuments/2021/08_Aug/[Name_08_2021.xlsb] name_of_the_worksheet ! R2C1: R1048576C42"

下面是我设法写的代码。非常感谢您的帮助!提前谢谢你。

Sub PivotSource()
Dim ws As Worksheet, pivot As PivotTable, cache As PivotCache
Dim FileToOpen As Variant, OpenBook As Workbook

FileToOpen = Application.GetOpenFilename(Title:="Choose file to for pivot tables", MultiSelect:=False, FileFilter:="Excel Files(*.xls*),*xls*")
If FileToOpen <> False Then
Set OpenBook = Application.Workbooks.Open(FileToOpen)
Else
MsgBox "File is not selected"
End If

MsgBox "Workbook was opened, starting to update source data...", vbInformation

ThisWorkbook.Activate
Set cache = ThisWorkbook.PivotCaches.Create(XlPivotTableSourceType.xlDatabase, SourceData:=*link will be here*) 
For Each ws In ThisWorkbook.Worksheets
For Each pivot In ws.PivotTables
If cache.Index Then
pivot.CacheIndex = cache.Index
Else
pivot.ChangePivotCache cache
End If
Next
Next
cache.Refresh

MsgBox "Source was changed for all pivot tables", vbInformation

End Sub

Answer 1

如果预先知道工作表名称和源范围，那么您应该能够这样做:

FileToOpen = Application.GetOpenFilename(Title:="Choose file to for pivot tables", _
MultiSelect:=False, FileFilter:="Excel Files(*.xls*),*xls*")
If FileToOpen <> False Then
Set OpenBook = Application.Workbooks.Open(FileToOpen)
Else
MsgBox "File is not selected"
Exit Sub
End If

MsgBox "Workbook was opened, starting to update source data...", vbInformation

ThisWorkbook.Activate
Set cache = ThisWorkbook.PivotCaches.Create( _
XlPivotTableSourceType.xlDatabase, _
SourceData:=OpenBook.Worksheets("DataSheet").Range("A2:AP1048576"))