Microsoft Office Forums Changing Absolute path to Relative in a Macro

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 01-24-2019, 11:10 AM
MrKim MrKim is offline Changing Absolute path to Relative in a Macro Windows 10 Changing Absolute path to Relative in a Macro Office 2019
Novice
Changing Absolute path to Relative in a Macro
 
Join Date: Jan 2019
Posts: 8
MrKim is on a distinguished road
Default 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
Reply With Quote
  #2  
Old 01-24-2019, 12:02 PM
Kenneth Hobson Kenneth Hobson is offline Changing Absolute path to Relative in a Macro Windows 10 Changing Absolute path to Relative in a Macro Office 2016
Novice
 
Join Date: Jun 2018
Posts: 29
Kenneth Hobson is on a distinguished road
Default

Code:
MsgBox ActiveWorkbook.path & "\x.csv"
Reply With Quote
  #3  
Old 01-24-2019, 01:16 PM
MrKim MrKim is offline Changing Absolute path to Relative in a Macro Windows 10 Changing Absolute path to Relative in a Macro Office 2019
Novice
Changing Absolute path to Relative in a Macro
 
Join Date: Jan 2019
Posts: 8
MrKim is on a distinguished road
Default

Can you pls advise exactly where I insert that into the macro so as to get the syntax correct?
Reply With Quote
  #4  
Old 01-24-2019, 04:18 PM
Kenneth Hobson Kenneth Hobson is offline Changing Absolute path to Relative in a Macro Windows 10 Changing Absolute path to Relative in a Macro Office 2016
Novice
 
Join Date: Jun 2018
Posts: 29
Kenneth Hobson is on a distinguished road
Default

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")
You may have to duplicate the double quotes when embedded between a quoted string.
Reply With Quote
  #5  
Old 01-24-2019, 06:05 PM
MrKim MrKim is offline Changing Absolute path to Relative in a Macro Windows 10 Changing Absolute path to Relative in a Macro Office 2019
Novice
Changing Absolute path to Relative in a Macro
 
Join Date: Jan 2019
Posts: 8
MrKim is on a distinguished road
Default

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
Reply With Quote
  #6  
Old 01-24-2019, 06:25 PM
MrKim MrKim is offline Changing Absolute path to Relative in a Macro Windows 10 Changing Absolute path to Relative in a Macro Office 2019
Novice
Changing Absolute path to Relative in a Macro
 
Join Date: Jan 2019
Posts: 8
MrKim is on a distinguished road
Default

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.
Reply With Quote
  #7  
Old 01-24-2019, 08:16 PM
Kenneth Hobson Kenneth Hobson is offline Changing Absolute path to Relative in a Macro Windows 10 Changing Absolute path to Relative in a Macro Office 2016
Novice
 
Join Date: Jun 2018
Posts: 29
Kenneth Hobson is on a distinguished road
Default

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
Reply With Quote
  #8  
Old 01-24-2019, 09:26 PM
MrKim MrKim is offline Changing Absolute path to Relative in a Macro Windows 10 Changing Absolute path to Relative in a Macro Office 2019
Novice
Changing Absolute path to Relative in a Macro
 
Join Date: Jan 2019
Posts: 8
MrKim is on a distinguished road
Default

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
Reply With Quote
  #9  
Old 01-24-2019, 09:51 PM
Kenneth Hobson Kenneth Hobson is offline Changing Absolute path to Relative in a Macro Windows 10 Changing Absolute path to Relative in a Macro Office 2016
Novice
 
Join Date: Jun 2018
Posts: 29
Kenneth Hobson is on a distinguished road
Default

Code:
ActiveWorkbook.Queries.Add  "x", f
Reply With Quote
  #10  
Old 01-24-2019, 10:04 PM
MrKim MrKim is offline Changing Absolute path to Relative in a Macro Windows 10 Changing Absolute path to Relative in a Macro Office 2019
Novice
Changing Absolute path to Relative in a Macro
 
Join Date: Jan 2019
Posts: 8
MrKim is on a distinguished road
Default

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.
Reply With Quote
  #11  
Old 01-25-2019, 09:50 PM
Kenneth Hobson Kenneth Hobson is offline Changing Absolute path to Relative in a Macro Windows 10 Changing Absolute path to Relative in a Macro Office 2016
Novice
 
Join Date: Jun 2018
Posts: 29
Kenneth Hobson is on a distinguished road
Default

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.
Reply With Quote
  #12  
Old 01-25-2019, 10:53 PM
MrKim MrKim is offline Changing Absolute path to Relative in a Macro Windows 10 Changing Absolute path to Relative in a Macro Office 2019
Novice
Changing Absolute path to Relative in a Macro
 
Join Date: Jan 2019
Posts: 8
MrKim is on a distinguished road
Default

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
Reply With Quote
  #13  
Old 01-26-2019, 10:23 AM
Kenneth Hobson Kenneth Hobson is offline Changing Absolute path to Relative in a Macro Windows 10 Changing Absolute path to Relative in a Macro Office 2016
Novice
 
Join Date: Jun 2018
Posts: 29
Kenneth Hobson is on a distinguished road
Default

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
Reply With Quote
  #14  
Old 01-26-2019, 02:18 PM
MrKim MrKim is offline Changing Absolute path to Relative in a Macro Windows 10 Changing Absolute path to Relative in a Macro Office 2019
Novice
Changing Absolute path to Relative in a Macro
 
Join Date: Jan 2019
Posts: 8
MrKim is on a distinguished road
Default

Agreed, many thanks for your efforts Ken!
Reply With Quote
Reply

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
Changing Absolute path to Relative in a Macro 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
Changing Absolute path to Relative in a Macro PowerPoint VBA: ApplyTemplate & absolute path tinfanide PowerPoint 2 04-05-2013 07:28 AM
Changing Absolute path to Relative in a Macro Relative path to desktop b0x4it Word VBA 4 05-18-2011 11:50 PM


All times are GMT -7. The time now is 08:03 AM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2019, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2019 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft