Solved: OPEN FILE

[oleg_v]

Hello
i have a macro:
Sub Macro2()
With ActiveSheet.QueryTables.Add(Connection:= _
"TEXT;\\166698-p584\oleg\REPORTS_FOR_BACKUP\455015010062\1_STEP.TXT", _
Destination:=Range("Q3"))
.Name = "1_STEP"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = False
.RefreshOnFileOpen = False
.RefreshStyle = xlOverwriteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = False
.RefreshPeriod = 0
.TextFilePromptOnRefresh = False
.TextFilePlatform = 862
.TextFileStartRow = 1
.TextFileParseType = xlDelimited
.TextFileTextQualifier = xlTextQualifierDoubleQuote
.TextFileConsecutiveDelimiter = False
.TextFileTabDelimiter = False
.TextFileSemicolonDelimiter = False
.TextFileCommaDelimiter = False
.TextFileSpaceDelimiter = False
.TextFileOtherDelimiter = "|"
.TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1)
.TextFileTrailingMinusNumbers = True
.Refresh BackgroundQuery:=False
End With
End Sub

i need help to change the path of the file to "open file window"
and that i will be able to activate this macro from ather sheets.

because if i activate this macro from from ather sheet it paste me the data on the wrong sheet


thanks

[lucas]

I believe that you need to change this line:

Destination:=Range("Q3"))

If you fully qualify it to include the sheet you want the data to go to then you can run it from any sheet.


Destination:=Worksheets("Data").Range("Q3"))


Change Data to the name of the worksheet you want the data to go to.

[oleg_v]

thanks
but how can i get the "open file" window

[lucas]

Untested:

Sub Macro2()
With Application.FileDialog(msoFileDialogFilePicker)
.AllowMultiSelect = False
If .Show = -1 Then
End If
End With
With ActiveSheet.QueryTables.Add(Connection:= _
"TEXT;.SelectedItems(1)", _
Destination:=Range("Q3"))
.Name = "1_STEP"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = False
.RefreshOnFileOpen = False
.RefreshStyle = xlOverwriteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = False
.RefreshPeriod = 0
.TextFilePromptOnRefresh = False
.TextFilePlatform = 862
.TextFileStartRow = 1
.TextFileParseType = xlDelimited
.TextFileTextQualifier = xlTextQualifierDoubleQuote
.TextFileConsecutiveDelimiter = False
.TextFileTabDelimiter = False
.TextFileSemicolonDelimiter = False
.TextFileCommaDelimiter = False
.TextFileSpaceDelimiter = False
.TextFileOtherDelimiter = "|"
.TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1)
.TextFileTrailingMinusNumbers = True
.Refresh BackgroundQuery:=False
End With
End Sub

[oleg_v]

it does not work what should i do?

[GTO]

it does not work what should i do?

Hi Oleg,


What version of Excel are you using? (ie - 97, 2000, 2003...)
I would suggest zipping your workbook (or an example wb that adequately replicates yours) and the textfile, and attach the zip, so we can see what's happening (or that is, what's not happening).Mark

[ZVI]

Hi Oleg,

Below is tweaked a bit Steve's code in which "TEXT;.SelectedItems(1)" is fixed and your initial .TextFilePlatform = 862 is replaced by .TextFilePlatform = xlMSDOS (it's actually equal to 3):


Sub Test()
Dim f As String
With Application.FileDialog(msoFileDialogFilePicker)
.AllowMultiSelect = False
.Filters.Add "TXT or CSV files", "*.CSV; *.TXT", 1
If Not .Show Then Exit Sub
f = .SelectedItems(1)
End With
With ActiveSheet.QueryTables.Add(Connection:= _
"TEXT;" & f, _
Destination:=Range("Q3"))
.Name = "1_STEP"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = False
.RefreshOnFileOpen = False
.RefreshStyle = xlOverwriteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = False
.RefreshPeriod = 0
.TextFilePromptOnRefresh = False
.TextFilePlatform = xlMSDOS
.TextFileStartRow = 1
.TextFileParseType = xlDelimited
.TextFileTextQualifier = xlTextQualifierDoubleQuote
.TextFileConsecutiveDelimiter = False
.TextFileTabDelimiter = False
.TextFileSemicolonDelimiter = False
.TextFileCommaDelimiter = False
.TextFileSpaceDelimiter = False
.TextFileOtherDelimiter = "|"
.TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1)
.TextFileTrailingMinusNumbers = True
.Refresh BackgroundQuery:=False
End With
End Sub

But as Mark has suggested your example would help us to help you more efficiently :)

Vladimir

[oleg_v]

hi
i attached the text file because the xls file it just a new empty file

the problem is that the name of the txt file is always changes and i need each time to choose a new file.

[ZVI]

Oleg,
Previous code works with your TXT file.
But macro in the attached workbook is adjusted for your data.
Use "Import" button to run macro and "VBA-code" button to see the code.
The code now is as follows:


Sub ImportTXT()

Const DestCell = "Q3" ' <-- adjust destination cell address if required

Dim f As String

' Choose TXT file
With Application.FileDialog(msoFileDialogFilePicker)
.AllowMultiSelect = False
.Filters.Add "CSV files", "*.CSV; *.TXT", 1
If Not .Show Then Exit Sub
f = .SelectedItems(1)
End With

' Clear destination range
' Empty row should be above, empty columns should be on left & right sides
Range(DestCell).CurrentRegion.ClearContents

' Query
With ActiveSheet.QueryTables.Add(Connection:="TEXT;" & f, Destination:=Range(DestCell))
.Name = "1_STEP"
.FieldNames = False
.TextFilePlatform = xlMSDOS
.TextFileStartRow = 1
.TextFileParseType = xlDelimited
.TextFileTextQualifier = xlTextQualifierNone
.TextFileOtherDelimiter = "|"
.TextFileColumnDataTypes = Array(xlTextFormat, xlTextFormat, xlYMDFormat)
.TextFileTrailingMinusNumbers = True
.Refresh BackgroundQuery:=False
.Delete
End With

End Sub
Regards,
Vladimir

[oleg_v]

hi

this line "
.Name = "1_STEP"

should not be replaced??
because the file name is changing every time

[ZVI]

hi

this line "
.Name = "1_STEP"

should not be replaced??
because the file name is changing every time

No, it shouldn't, because it's just the query range name