View Full Version : Solved: OPEN FILE
oleg_v
03-02-2010, 01:25 AM
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
03-02-2010, 08:44 AM
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
03-04-2010, 04:44 AM
thanks
but how can i get the "open file" window
lucas
03-04-2010, 08:38 AM
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
03-07-2010, 05:20 AM
it does not work what should i do?
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
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
03-07-2010, 11:16 PM
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.
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
03-08-2010, 04:47 AM
hi
this line "
.Name = "1_STEP"
should not be replaced??
because the file name is changing every time
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
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.