Hej :)
Napisałem kod, który ma tworzyć (tylko) połączenia z innych plików excela przez query, który działa, ale tylko gdy zmienie cos w zrodle aby zrobic pętle dla wszystkich plików w folderze, kod jakby nie czyta zródła.
Sub add()
Dim mFormula As String
mFormula = "let" & Chr(13) & "" & Chr(10) & " Zródlo = Excel.Workbook(File.Contents(""C: \Users\blas\desktop\all\files\f17""), null, true)," & Chr(13) & "" & Chr(10) & " Sheet1_Sheet = Zródlo{[Item=""Sheet1"",Kind=""Sheet""]}[Data]," & Chr(13) & "" & Chr(10) & " #""Naglówki o podwyzszonym poziomie"" = Table.PromoteHeaders(Sheet1_Sheet, [PromoteAllScalars=true])," & Chr(13) & "" & Chr(10) & " #""Zmieniono typ"" = Table.TransformColumnTypes(#" & _
"""Naglówki o podwyzszonym poziomie"",{{""REGI"", type text}, {""Country"", type text}, {""Country Name"", type text}, {""Employee ID"", Int64.Type}, {""Employee Name"", type text}, {""Omega chair"", Int64.Type}, {""Company Code"", type text}, {""OMEGA Fiscal Month"", Int64.Type}, {""Pay Month"", type date}, {""OMEGA Payroll Code"", type text}, {""Local Payroll Code" & _
""", type text}, {""Payment_type"", type text}, {""Amount to be Paid"", type number}, {""Currency"", type text}, {""C"", type any}, {""Column16"", type any}, {""Column17"", type any}, {""Column18"", type any}, {""Column19"", type any}, {""Column20"", type any}})" & Chr(13) & "" & Chr(10) & "in" & Chr(13) & "" & Chr(10) & " #""Zmieniono typ"""
query1 = ActiveWorkbook.Queries.add("Sheet1", mFormula)
End Sub
Próbowałem jeszcze zrobić taką pętle jak poniżej, ale nie nadal nie czyta zródła. Czy mogłibyscie mi wskazać, gdzie móglby być tu błąd?
Z góry dziękuje za pomoc :D
Sub add()
Dim myConnection As WorkbookConnection
Dim mFormula As String
Dim MyFolder As String
Dim MyFile As String
MyFolder = "C:\Users\blaszczy\Desktop\Faza1\COF FILES 17\FY17"
MyFile = Dir(MyFolder & "\*.xlsx")
Do While MyFile <> ""
mFormula = "let" & Chr(13) & "" & Chr(10) & " Zródlo = Excel.Workbook(File.Contents(""MyFolder & " \ " & MyFile""), null, true)," & Chr(13) & "" & Chr(10) & " Sheet1_Sheet = Zródlo{[Item=""Sheet1"",Kind=""Sheet""]}[Data]," & Chr(13) & "" & Chr(10) & " #""Naglówki o podwyzszonym poziomie"" = Table.PromoteHeaders(Sheet1_Sheet, [PromoteAllScalars=true])," & Chr(13) & "" & Chr(10) & " #""Zmieniono typ"" = Table.TransformColumnTypes(#" & _
"""Naglówki o podwyzszonym poziomie"",{{""REGI"", type text}, {""Country"", type text}, {""Country Name"", type text}, {""Employee ID"", Int64.Type}, {""Employee Name"", type text}, {""Omega chair"", Int64.Type}, {""Company Code"", type text}, {""OMEGA Fiscal Month"", Int64.Type}, {""Pay Month"", type date}, {""OMEGA Payroll Code"", type text}, {""Local Payroll Code" & _
""", type text}, {""Payment_type"", type text}, {""Amount to be Paid"", type number}, {""Currency"", type text}, {""C"", type any}, {""Column16"", type any}, {""Column17"", type any}, {""Column18"", type any}, {""Column19"", type any}, {""Column20"", type any}})" & Chr(13) & "" & Chr(10) & "in" & Chr(13) & "" & Chr(10) & " #""Zmieniono typ"""
query1 = ActiveWorkbook.Queries.add("Sheet1", mFormula)
MyFile = Dir
Loop
End Sub