#1
|
|||
|
|||
Changing Absolute path to Relative in a Macro
Hi all,
I recorded a macro to import a .csv file - the file name will always be named 'x', so x.csv But as I want the worksheet to be used by others, I went in and attempted to edit the absolute path to the file, to a relative path. The x.csv file will always be located in the same folder as the Excel workbook. So this is the macro, with the edited path - File.Contents(.\x.csv) -, but it produces an error: Runtime Error 1004 [Expression Error] The name 'Source' wasn't recognized. What have I done wrong? Sub Import1() ' ' Import1 Macro ' ' Application.CutCopyMode = False ActiveWorkbook.Queries.Add Name:="x", Formula:= _ "let" & Chr(13) & "" & Chr(10) & " Source = Csv.Document(File.Contents(.\x.csv),[Delimiter="","", Columns=10, Encoding=1252, QuoteStyle=QuoteStyle.None])," & Chr(13) & "" & Chr(10) & " #""Promoted Headers"" = Table.PromoteHeaders(Source, [PromoteAllScalars=true])," & Chr(13) & "" & Chr(10) & " #""Changed Type"" = Table.TransformColumnTypes(#""Promoted " & _ "Headers"",{{""Lot_No"", Int64.Type}, {""Unit"", type text}, {""Cont_UE"", Int64.Type}, {""Int_UE"", Int64.Type}, {""Owners_Name"", type text}, {""Owners_Address"", type text}, {""Owners_Phone"", type text}, {""Owners_Email"", type text}, {""Committee_Member"", type text}, {""Balance"", type number}})" & Chr(13) & "" & Chr(10) & "in" & Chr(13) & "" & Chr(10) & " #""Changed Type""" With ActiveSheet.ListObjects.Add(SourceType:=0, Source:= _ "OLEDB;Provider=Microsoft.Mashup.OleDb.1;Data Source=$Workbook$;Location=x;Extended Properties=""""" _ , Destination:=Range("$A$10")).QueryTable .CommandType = xlCmdSql .CommandText = Array("SELECT * FROM [x]") .RowNumbers = False .FillAdjacentFormulas = False .PreserveFormatting = True .RefreshOnFileOpen = False .BackgroundQuery = True .RefreshStyle = xlInsertDeleteCells .SavePassword = False .SaveData = True .AdjustColumnWidth = True .RefreshPeriod = 0 .PreserveColumnInfo = True .ListObject.DisplayName = "x" .Refresh BackgroundQuery:=False End With ActiveWorkbook.Save End Sub |
#2
|
|||
|
|||
Code:
MsgBox ActiveWorkbook.path & "\x.csv" |
#3
|
|||
|
|||
Can you pls advise exactly where I insert that into the macro so as to get the syntax correct?
|
#4
|
|||
|
|||
It is immaterial but I don't know what steps you did to get that code. The usual method is to use a Sheet's Queries method.
Since you did not paste code between code tags, it is hard to see structure. Click # on toolbar to insert tags. Change File.Contents(.\x.csv) to: Code:
File.Contents(ActiveWorkbook.Path & "\x.csv") |
#5
|
|||
|
|||
Data Import Test ver25thJan2019.xlsm
x.xls Still having grief. Below is the path-modified code. I'm attaching test file. The 'x' csv test data is also attached as an excel file as I can't upload a .csv file here, so that will need to be renamed. Code:
Sub DataImportTest25th_1() ' ' DataImportTest25th_1 Macro ' ' Application.CutCopyMode = False ActiveWorkbook.Queries.Add Name:="x", Formula:= _ "let" & Chr(13) & "" & Chr(10) & " Source = Csv.Document(File.Contents(ActiveWorkbook.Path & ""\x.csv""),[Delimiter="","", Columns=10, Encoding=1252, QuoteStyle=QuoteStyle.None])," & Chr(13) & "" & Chr(10) & " #""Promoted Headers"" = Table.PromoteHeaders(Source, [PromoteAllScalars=true])," & Chr(13) & "" & Chr(10) & " #""Changed Type"" = Table.TransformColumnTypes(#""Promoted " & _ "Headers"",{{""Lot_No"", Int64.Type}, {""Unit"", type text}, {""Cont_UE"", Int64.Type}, {""Int_UE"", Int64.Type}, {""Owners_Name"", type text}, {""Owners_Address"", type text}, {""Owners_Phone"", type text}, {""Owners_Email"", type text}, {""Committee_Member"", type text}, {""Balance"", type text}})" & Chr(13) & "" & Chr(10) & "in" & Chr(13) & "" & Chr(10) & " #""Changed Type""" With ActiveSheet.ListObjects.Add(SourceType:=0, Source:= _ "OLEDB;Provider=Microsoft.Mashup.OleDb.1;Data Source=$Workbook$;Location=x;Extended Properties=""""" _ , Destination:=Range("$B$8")).QueryTable .CommandType = xlCmdSql .CommandText = Array("SELECT * FROM [x]") .RowNumbers = False .FillAdjacentFormulas = False .PreserveFormatting = True .RefreshOnFileOpen = False .BackgroundQuery = True .RefreshStyle = xlInsertDeleteCells .SavePassword = False .SaveData = True .AdjustColumnWidth = True .RefreshPeriod = 0 .PreserveColumnInfo = True .ListObject.DisplayName = "x" .Refresh BackgroundQuery:=False End With ActiveWindow.SmallScroll Down:=-3 Application.CommandBars("Queries and Connections").Visible = False ActiveWorkbook.Save End Sub |
#6
|
|||
|
|||
I forgot to add, that to record the Macro, I'm clicking on the Data/Get from Text/CSV button, then getting the file, then choosing to Load to Table into the worksheet. Then saving, then stopping the Recording.
The data showing in the worksheet resulted from the Macro Recording run. THEN, I went into the macro and edited the Path as suggested. |
#7
|
|||
|
|||
This boils down to a simple string concatenation issue. It is a bit more complicated because of the long string with embedded double quote characters.
When dealing with quote characters like that, one can use the double quotes method and/or the q method as shown. I used both here. Notice that I also broke down the long string to something more manageable. Note how I also used Debug.Print to show the built string in the VBE Immediate Window after the run. This is a handy way to see if the string was built right. Code:
Sub Main() Dim q$, f$ q = """" 'ActiveWorkbook.Queries.Add Name:="x (2)", Formula:= _ "let" & Chr(13) & "" & Chr(10) & " Source = Csv.Document(File.Contents(""C:\Users\lenovo1\Dropbox\_Excel\CSV\41469-changing-absolute-path-relative-macro\x.csv""),[Delimiter="","", Columns=10, Encoding=65001, QuoteStyle=QuoteStyle.None])," & Chr(13) & "" & Chr(10) & " #""Promoted Headers"" = Table.PromoteHeaders(Source, [PromoteAllScalars=true])," & Chr(13) & "" & Chr(10) & " #""Changed Type"" = Table.TransformColumnTypes(#""Promoted Headers" & _ """,{{""Lot_No"", Int64.Type}, {""Unit"", type text}, {""Cont_UE"", Int64.Type}, {""Int_UE"", Int64.Type}, {""Owners_Name"", type text}, {""Owners_Address"", type text}, {""Owners_Phone"", type text}, {""Owners_Email"", type text}, {""Committee_Member"", type text}, {""Balance"", type text}})" & Chr(13) & "" & Chr(10) & "in" & Chr(13) & "" & Chr(10) & " #""Changed Type""" 'q & "C:\ken\x.csv" & q & ")," f = "let" & vbCrLf & "Source = Csv.Document(File.Contents(" & _ q & ThisWorkbook.Path & "\x.csv" & _ "[Delimiter="","", Columns=10, Encoding=65001, QuoteStyle=QuoteStyle.None])," & _ vbCrLf & "#""Promoted Headers"" = Table.PromoteHeaders(" & _ "Source, [PromoteAllScalars=true])," & vbCrLf & _ "#""Changed Type"" = Table.TransformColumnTypes(" & _ "#""Promoted Headers" & _ """,{{""Lot_No"", Int64.Type}, {""Unit"", type text}, " & _ "{""Cont_UE"", Int64.Type}, {""Int_UE"", Int64.Type}, " & _ "{""Owners_Name"", type text}, {""Owners_Address"", type text}, " & _ "{""Owners_Phone"", type text}, {""Owners_Email"", type text}, " & _ "{""Committee_Member"", type text}, {""Balance"", type text}})" & vbCrLf & _ "in" & vbCrLf & "#""Changed Type""" Debug.Print f ActiveWorkbook.Queries.Add , "x", f ActiveWorkbook.Worksheets.Add With ActiveSheet.ListObjects.Add(SourceType:=0, Source:= _ "OLEDB;Provider=Microsoft.Mashup.OleDb.1;Data Source=$Workbook$;Location=""x"";Extended Properties=""""" _ , Destination:=Range("$A$1")).QueryTable .CommandType = xlCmdSql .CommandText = Array("SELECT * FROM [x]") .RowNumbers = False .FillAdjacentFormulas = False .PreserveFormatting = True .RefreshOnFileOpen = False .BackgroundQuery = True .RefreshStyle = xlInsertDeleteCells .SavePassword = False .SaveData = True .AdjustColumnWidth = True .RefreshPeriod = 0 .PreserveColumnInfo = True .ListObject.DisplayName = "x" .Refresh BackgroundQuery:=False End With End Sub |
#8
|
|||
|
|||
Ken, when I pasted that code into my file and ran it, I got "Compile error. Arguement not optional. - referring to this line: (and see attached screenshot)
ActiveWorkbook.Queries.Add , "x", f Screenshot1.jpg |
#9
|
|||
|
|||
Code:
ActiveWorkbook.Queries.Add "x", f |
#10
|
|||
|
|||
Now I get Runtime error 1004
Application defined or object defined error. And this line highlited: .ListObject.DisplayName = "x" Screenshot2.jpg Also it looks like it's going to open up a new sheet to load the import into, but I wanted it on the same sheet as the user is presented with at the start, and where they trigger the macro. |
#11
|
|||
|
|||
I did some testing but since I had knee surgery today, I was not able to finish it.
The yellow error is due to maybe running it before? Simply delete the Data > Query and Connection and the ListObject. I select all the sheet and Clear for ListObject. For the extra sheet, that is due to ActiveWorkbook.Workbooks.Add. Comment out or delete that line. The last thing is getting the f string right. I worked a bit on it but more is needed. I don't like the long string used in the recorder. If Structured right, you can see more easily how to fix it and see what it does. In the Query, it will error. Open the Query dialog to give you clues to debugging. Since you are adding to the ActiveSheet, why not do the Data > for worksheets import rather than ActiveWorkbook? The sheet method does not have the explicit data types so your formula string will just include and array of integer data types rather than explicit one. The field/column names is shorter too. I most test code. On one computer it worked fine. On this other, it is showing some issues that you ran into. I will have a solution for you tomorrow if you have not tweaked it to work fully by then. |
#12
|
|||
|
|||
Many thanks Ken, but let that knee heal in peace!
As you might have guessed, I'm feeling this is getting waaay harder than I first imagined, and as this is about .1% of the project, I'm now thinking I'll engage some dedicated assistance. And for that, you should all give a little sigh of relief, otherwise my posts would be choking this board |
#13
|
|||
|
|||
Change start range, and name for query and listobject to suit. This makes it easier to do more than one import into a workbook.
So the difference here is going beyond the macro recorder. For future code like this, you can more readily see how it is structured rather than long lines of code. Code:
Sub Main() Dim q$, f$, nm$, r As Range q = """" nm = "B" Set r = Range("B8") f = "let" & vbCrLf & "Source = Csv.Document(File.Contents(" & _ q & ThisWorkbook.Path & "\x.csv" & q & ")," & _ "[Delimiter="","", Columns=10, Encoding=65001, QuoteStyle=QuoteStyle.None])," & _ vbCrLf & "#""Promoted Headers"" = Table.PromoteHeaders(" & _ "Source, [PromoteAllScalars=true])," & vbCrLf & _ "#""Changed Type"" = Table.TransformColumnTypes(" & _ "#""Promoted Headers" & _ """,{{""Lot_No"", Int64.Type}, {""Unit"", type text}, " & _ "{""Cont_UE"", Int64.Type}, {""Int_UE"", Int64.Type}, " & _ "{""Owners_Name"", type text}, {""Owners_Address"", type text}, " & _ "{""Owners_Phone"", type text}, {""Owners_Email"", type text}, " & _ "{""Committee_Member"", type text}, {""Balance"", type text}})" & vbCrLf & _ "in" & vbCrLf & "#""Changed Type""" ActiveWorkbook.Queries.Add nm, f With ActiveSheet.ListObjects.Add(SourceType:=0, Source:= _ "OLEDB;Provider=Microsoft.Mashup.OleDb.1;" & _ "Data Source=$Workbook$;Location=" & nm & ";" & _ "Extended Properties=""""", _ Destination:=r).QueryTable .CommandType = xlCmdSql .CommandText = Array("SELECT * FROM [" & nm & "]") .RowNumbers = False .FillAdjacentFormulas = False .PreserveFormatting = True .RefreshOnFileOpen = False .BackgroundQuery = True .RefreshStyle = xlInsertDeleteCells .SavePassword = False .SaveData = True .AdjustColumnWidth = True .RefreshPeriod = 0 .PreserveColumnInfo = True .ListObject.DisplayName = nm .Refresh BackgroundQuery:=False End With ActiveWindow.SmallScroll Down:=-3 Application.CommandBars("Queries and Connections").Visible = False ActiveWorkbook.Save End Sub |
#14
|
|||
|
|||
Agreed, many thanks for your efforts Ken!
|
Thread Tools | |
Display Modes | |
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Relative path for hyperlinks | wpryan | Word | 4 | 01-09-2019 05:15 PM |
Image absolute position relative to page | thuizt | Word VBA | 4 | 08-01-2017 07:25 AM |
Hyperlinks defaulting to absolute instead of relative!!! | wdzambotti | Word VBA | 1 | 08-17-2015 09:03 PM |
PowerPoint VBA: ApplyTemplate & absolute path | tinfanide | PowerPoint | 2 | 04-05-2013 07:28 AM |
Relative path to desktop | b0x4it | Word VBA | 4 | 05-18-2011 11:50 PM |