Refresh BackgroundQuery:=False

%3CLINGO-SUB%20id%3D%22lingo-sub-2669153%22%20slang%3D%22en-US%22%3ERefresh%20BackgroundQuery%3A%3DFalse%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2669153%22%20slang%3D%22en-US%22%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%3EI%20had%20recorded%20the%20following%20macro%3C%2FSPAN%3E%3C%2FP%3E%3CP%20class%3D%22x-hidden-focus%22%3ESub%20ImportarCsV()%3C%2FP%3E%3CP%3E'%3C%2FP%3E%3CP%3E'%20ImportarCsV%20Macro%3C%2FP%3E%3CP%3E'%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E'%3C%2FP%3E%3CP%3EDim%20NombreArchivo%20As%20String%3C%2FP%3E%3CP%3ENombreArchivo%20%3D%20Application.GetOpenFilename()%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EOn%20Error%20Resume%20Next%3C%2FP%3E%3CP%3EActiveWorkbook.Queries(%22NombreArchivo%22).Delete%3C%2FP%3E%3CP%3EOn%20Error%20GoTo%200%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EActiveWorkbook.Queries.Add%20Name%3A%3D%22NombreArchivo%22%2C%20Formula%3A%3D%20_%3C%2FP%3E%3CP%3E%22let%22%20%26amp%3B%20Chr(13)%20%26amp%3B%20%22%22%20%26amp%3B%20Chr(10)%20%26amp%3B%20%22%20Origen%20%3D%20Csv.Document(File.Contents(%22%22NombreArchivo%22%22)%2C%5BDelimiter%3D%22%22%2C%22%22%2C%20Columns%3D5%2C%20Encoding%3D1252%2C%20QuoteStyle%3DQuoteStyle.None%5D)%2C%22%20%26amp%3B%20Chr(13)%20%26amp%3B%20%22%22%20%26amp%3B%20Chr(10)%20%26amp%3B%20%22%20%23%22%22Tipo%20cambiado%22%22%20%3D%20Table.TransformColumnTypes(Origen%2C%7B%7B%22%22Column1%22%22%2C%20type%20date%7D%2C%20%7B%22%22Column2%22%22%2C%20type%20text%7D%2C%20%7B%22%22Column3%22%22%2C%20type%20number%7D%2C%20%7B%22%22Column4%22%22%2C%20Int64.Type%7D%2C%20%7B%22%22Column5%22%22%2C%20type%20number%7D%7D)%22%20%26amp%3B%20Chr(13)%20%26amp%3B%20%22%22%20%26amp%3B%20Chr(10)%20%26amp%3B%20%22in%22%20%26amp%3B%20Chr(13)%20%26amp%3B%20%22%22%20%26amp%3B%20Chr(10)%20%26amp%3B%20%22%20%23%22%22Tipo%20cambiado%22%22%22%3C%2FP%3E%3CP%3EActiveWorkbook.Worksheets.Add.Name%20%3D%20%22Temporal%22%3C%2FP%3E%3CP%3EWith%20ActiveSheet.ListObjects.Add(SourceType%3A%3D0%2C%20Source%3A%3D%20_%3C%2FP%3E%3CP%3E%22OLEDB%3BProvider%3DMicrosoft.Mashup.OleDb.1%3BData%20Source%3D%24Workbook%24%3BLocation%3Dmovimientos%3BExtended%20Properties%3D%22%22%22%22%22%20_%3C%2FP%3E%3CP%3E%2C%20Destination%3A%3DRange(%22%24A%241%22)).QueryTable%3C%2FP%3E%3CP%3E.CommandType%20%3D%20xlCmdSql%3C%2FP%3E%3CP%3E.CommandText%20%3D%20Array(%22SELECT%20*%20FROM%20%5Bmovimientos%5D%22)%3C%2FP%3E%3CP%3E.RowNumbers%20%3D%20False%3C%2FP%3E%3CP%3E.FillAdjacentFormulas%20%3D%20False%3C%2FP%3E%3CP%3E.PreserveFormatting%20%3D%20True%3C%2FP%3E%3CP%3E.RefreshOnFileOpen%20%3D%20False%3C%2FP%3E%3CP%3E.BackgroundQuery%20%3D%20True%3C%2FP%3E%3CP%20class%3D%22x-hidden-focus%22%3E.RefreshStyle%20%3D%20xlInsertDeleteCells%3C%2FP%3E%3CP%3E.SavePassword%20%3D%20False%3C%2FP%3E%3CP%3E.SaveData%20%3D%20True%3C%2FP%3E%3CP%3E.AdjustColumnWidth%20%3D%20True%3C%2FP%3E%3CP%3E.RefreshPeriod%20%3D%200%3C%2FP%3E%3CP%3E.PreserveColumnInfo%20%3D%20True%3C%2FP%3E%3CP%3E.ListObject.DisplayName%20%3D%20%22temporal%22%3C%2FP%3E%3CP%20class%3D%22%22%3E.Refresh%20BackgroundQuery%3A%3DFalse%3C%2FP%3E%3CP%3EEnd%20With%3C%2FP%3E%3CP%3EEnd%20Sub%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20macro%20works%20OK%20only%20with%20the%20original%20CVS%20file%2C%20EJ%20d%3AViridia%5CNombre_original.cvs%3C%2FP%3E%3CP%3Ebut%20the%20problem%20arises%20when%20I%20try%20to%20run%20the%20macro%20from%20my%20PC%20and%20disk%20c%3A%5CPepe%5Cpepedato.cvs%3C%2FP%3E%3CP%3Ethe%20instruction%3C%2FP%3E%3CP%3ENombreArchivo%20%3D%20Application.GetOpenFilename()%20give%20to%20NombreArchivo%20the%20right%20string%20but%20when%3C%2FP%3E%3CP%3Earrives%20to.Refresh%20BackgroundQuery%3A%3DFalse%20loads%26nbsp%3BNombre_original.cvs%3C%2FP%3E%3CP%3EIf%20I%20go%20to%20another%20PC%20with%20the%20same%20OS%20and%20the%20same%20version%20of%20Excel%20but%20disk%20%3CLI-EMOJI%20id%3D%22lia_anguished-face%22%20title%3D%22%3Aanguished_face%3A%22%3E%3C%2FLI-EMOJI%3E%20or%20D%5Cviridia%20don't%26nbsp%3B%3C%2FP%3E%3CP%3Eexist%20brings%20an%20error%20that%20says%20C%3A%5Cdirection%20or%20d%3A%5CViridia%5CNombre_original.cvs%20does%20not%20exist%3C%2FP%3E%3CP%3EClearly%2C%20the%20problem%20is%20the%20instruction%3C%2FP%3E%3CP%3E.Refresh%20BackgroundQuery%3A%3DFalse%20that%20retains%20the%20original%20path%20when%20the%20macro%20was%20recorded%3C%2FP%3E%3CP%3E%3CSTRONG%3EThere%20is%20anyone%20that%20gives%20me%20a%20solution%20to%20how%20to%20tell%20.refreshBackgroundQuery%3A%3DFalse%20the%20new%20Path%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2669153%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EMacros%20and%20VBA%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E
New Contributor

 

 

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 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

 

 

 

 

0 Replies