I had recorded the following macro
Sub ImportarCsV()
'
' ImportarCsV Macro
'
'
Dim NombreArchivo As String
NombreArchivo = Application.GetOpenFilename()
On Error Resume Next
ActiveWorkbook.Queries("NombreArchivo").Delete
On Error GoTo 0
ActiveWorkbook.Queries.Add Name:="NombreArchivo", Formula:= _
"let" & Chr(13) & "" & Chr(10) & " Origen = Csv.Document(File.Contents(""NombreArchivo""),[Delimiter="","", Columns=5, Encoding=1252, QuoteStyle=QuoteStyle.None])," & Chr(13) & "" & Chr(10) & " #""Tipo cambiado"" = Table.TransformColumnTypes(Origen,{{""Column1"", type date}, {""Column2"", type text}, {""Column3"", type number}, {""Column4"", Int64.Type}, {""Column5"", type number}})" & Chr(13) & "" & Chr(10) & "in" & Chr(13) & "" & Chr(10) & " #""Tipo cambiado"""
ActiveWorkbook.Worksheets.Add.Name = "Temporal"
With ActiveSheet.ListObjects.Add(SourceType:=0, Source:= _
"OLEDB;Provider=Microsoft.Mashup.OleDb.1;Data Source=$Workbook$;Location=movimientos;Extended Properties=""""" _
, Destination:=Range("$A$1")).QueryTable
.CommandType = xlCmdSql
.CommandText = Array("SELECT * FROM [movimientos]")
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.PreserveColumnInfo = True
.ListObject.DisplayName = "temporal"
.Refresh BackgroundQuery:=False
End With
End Sub
The macro works OK only with the original CVS file, EJ d:Viridia\Nombre_original.cvs
but the problem arises when I try to run the macro from my PC and disk c:\Pepe\pepedato.cvs
the instruction
NombreArchivo = Application.GetOpenFilename() give to NombreArchivo the right string but when
arrives to.Refresh BackgroundQuery:=False loads Nombre_original.cvs
If I go to another PC with the same OS and the same version of Excel but disk D: or D\viridia don't
exist brings an error that says C:\direction or d:\Viridia\Nombre_original.cvs does not exist
Clearly, the problem is the instruction
.Refresh BackgroundQuery:=False that retains the original path when the macro was recorded
There is anyone that gives me a solution to how to tell .refreshBackgroundQuery:=False the new Path
