Change the TCD source with Excel VBA macro
Sam2021
-
yg_be Posted messages 23437 Registration date Status Contributor Last intervention -
yg_be Posted messages 23437 Registration date Status Contributor Last intervention -
Hello
With the macro recorder, I saw that to change the data source the code was as follows
the problem is that instead of the path "R:\10. REVENUES\10.12. REMAINDERS TO RECOVER\2020\10_RAR as of December 31, 2020\test vba\
I would like to use "ThisWorkbook.Path". which if I understood correctly, allows using the macro in any directory. but I can't manage to do it
Also, I would like to change the file name "Remaining to Recover_BAA_2021_DAJEP.xlsx" to a name that changes depending on the file name. because this macro I'm embedding into another that creates a lot of files. until now, I was using this cell.Value & ".xlsx" to create the file name
I run this macro in one file, and I want to change the data source of the pivot tables in another file....
Hoping this is clear..
thank you for your help
With the macro recorder, I saw that to change the data source the code was as follows
Windows("Remaining to Recover_BAA_2021_DAJEP.xlsx").Activate
Range("C14").Select
Sheets("Amount by Service").Select
Range("C8").Select
ActiveSheet.PivotTables("Pivot Table2").ChangePivotCache _
ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
"R:\10. REVENUES\10.12. REMAINDERS TO RECOVER\2021\01 BAA\[Remaining to Recover_BAA_2021_DAJEP.xlsx]Sheet1!C1:C27" _
, Version:=6) the problem is that instead of the path "R:\10. REVENUES\10.12. REMAINDERS TO RECOVER\2020\10_RAR as of December 31, 2020\test vba\
I would like to use "ThisWorkbook.Path". which if I understood correctly, allows using the macro in any directory. but I can't manage to do it
Also, I would like to change the file name "Remaining to Recover_BAA_2021_DAJEP.xlsx" to a name that changes depending on the file name. because this macro I'm embedding into another that creates a lot of files. until now, I was using this cell.Value & ".xlsx" to create the file name
I run this macro in one file, and I want to change the data source of the pivot tables in another file....
Hoping this is clear..
thank you for your help
7 answers
-
yg_be Posted messages 23437 Registration date Status Contributor Last intervention Ambassadeur 1 588
Hello,
is the source data file in the same folder as the file containing the macro?
If so, maybe:ActiveSheet.PivotTables("PivotTable2").ChangePivotCache _ ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, _ SourceData:= _ ThisWorkbook.Path + "\[" + CStr(cell.Value) + ".xlsx]Sheet1!C1:C27" _ , Version:=6) -
Hello
I'm sorry for the delayed response...
It's working great, thank you
Would it be possible instead of Feuil1!C1:C27 to indicate selecting up to the last non-empty cell?
Best regards
Sandie -
c1:c27 corresponds to column A to AA (27 columns). C:C won't help me because I want the selection to be made from A1 to AA with the last non-empty rows.
Is that clearer? Can someone help me? -
The file is too large
I did this but it gives me a compilation error from the line ActiveSheet.PivotTable. Where is my mistake?Workbooks(cell.Value & ".xlsx").Activate
Dim lastRow As Long,
lastRow = Worksheets("Feuil1").Range("A" & Rows.Count).End(xlUp).Row
MsgBox lastRow
Workbooks(cell.Value & ".xlsx").Sheets("Montant par service").Select
ActiveSheet.PivotTables("Tableau croisé dynamique2").ChangePivotCache _
ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, _
SourceData:= _
ThisWorkbook.Path + "\[" + CStr(cell.Value) + ".xlsx]Feuil1(" A1:AA" & lastRow)
, Version:=6) -
-
A1: AA doesn't seem to work. So I tried this
Workbooks(cell.Value & ".xlsx").Activate
Dim lastRow As Long, data As Worksheet
lastRow = Worksheets("Sheet1").Range("A" & Rows.Count).End(xlUp).Row
MsgBox lastRow
Workbooks(cell.Value & ".xlsx").Sheets("Amount by service").Select
ActiveSheet.PivotTables("PivotTable2").ChangePivotCache _
ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, _
SourceData:= _
ThisWorkbook.Path + "\[" + CStr(cell.Value) + ".xlsx]Sheet1!R1C1:R & lastRow & C27" _
, Version:=6)
but I get an error code saying the reference is not valid.-
Is there anyone who can tell me what it means "unable to read the pivotTable property of the worksheet class"?
- montre ton code, en utilisant les balises de code (https://codes-sources.commentcamarche.net/faq/11288-les-balises-de-code), et indique à quelle ligne se produit l'erreur.
-
-
Here is my code:
Workbooks(cell.Value & ".xlsx").Activate
Dim lastRow As Integer
lastRow = Worksheets("Sheet1").Range("A" & Rows.Count).End(xlUp).Row
Workbooks(cell.Value & ".xlsx").Sheets("Amount by service").Select
ActiveSheet.PivotTables("PivotTable2").ChangePivotCache _
ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, _
SourceData:= _
ThisWorkbook.Path + "\[" + CStr(cell.Value) + ".xlsx]Sheet1!R1C1:R & lastRow & C27" _
, Version:=6)
the error occurs from ActiveSheet.PivotTables
Thank you
