Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Edit Macro script
Hello All, I'm putting a text file into excel and formatting it with text to column. Another program spits out the text file, each team member must enter their own, so they run the macro on their local machine. Is there a way for the macro to choose only the text file and ignore all other files in the folder. each txt file has a different alfa numeric number, for example "ME_TOO123" I recorded a Macro and now I'm trying to edit the following script... Workbooks.OpenText Filename:= _ "C:\Documents and Settings\CHANGED\My Documents\ME_TOO123.txt", Origin:= _ 437, StartRow:=1, DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, _ ConsecutiveDelimiter:=True, Tab:=True, Semicolon:=True, Comma:=False, _ Space:=True, Other:=True, OtherChar:=":", FieldInfo:=Array(Array(1, 1), _ Array(2, 1), Array(3, 1), Array(4, 1), Array(5, 1), Array(6, 1), Array(7, 1), Array(8, 1), _ Array(9, 1)), TrailingMinusNumbers:=True Thanks in advance... BadRasta |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Edit Macro script
Hi, Not sure if you want to do it this way but you could use the FilePicker dialog box and set the filter to text files and allow the user to pick the required file. I have included commented out code for extracting the filename only from the Path and filename just in case you want it but it is not required in this case because you need both path and filename. Note that I have used space and underscores (which are line breaks in otherwise single lines of code) so that the code will not break at incorrect places in this post and can be copied directly into your VBA editor without having to fix broken line problems. Sub OpenTxtFile() Dim myTitle As String Dim sFile As String 'Path and file name Dim ShortName As String 'FileName only myTitle = "Select the required text file" With Application.FileDialog(msoFileDialogFilePicker) .AllowMultiSelect = False .Filters.Add "Text files", "*.txt", 1 .Title = myTitle If .Show = False Then Exit Sub End If sFile = .SelectedItems(1) End With 'Following line of code extracts the 'filename only if required 'ShortName = Right(sFile, Len(sFile) _ - InStrRev(sFile, "\")) Workbooks.OpenText Filename:= _ sFile, _ Origin:=437, _ StartRow:=1, _ DataType:=xlDelimited, _ TextQualifier:=xlDoubleQuote, _ ConsecutiveDelimiter:=True, _ Tab:=True, _ Semicolon:=True, _ Comma:=False, _ Space:=True, _ Other:=True, _ OtherChar:=":", _ FieldInfo:=Array(Array(1, 1), _ Array(2, 1), Array(3, 1), _ Array(4, 1), Array(5, 1), _ Array(6, 1), Array(7, 1), _ Array(8, 1), _ Array(9, 1)), _ TrailingMinusNumbers:=True End Sub -- Regards, OssieMac |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Edit Macro script
Thanks OssieMac,
That was very helpful. As you can probably tell I'm a beginner so this helps to put some of this VB stuff in perspective. The reason I thought the path was necessary is we are on a network and as the other program (proprietary) spits out the txt file it goes in €śthat€ť folder on the operators hard drive I need 15 pieces of information from the text file to go into one row on the spreadsheet (that spreadsheet will have pivot tables) I haven't got it to work yet because I have to splice it of the top of the script. However, if anyone can suggest other ways of doing this, or has resource that might get the job done quicker please let me know Once again good job OssieMac, Thanks BadRasta "OssieMac" wrote: Hi, Not sure if you want to do it this way but you could use the FilePicker dialog box and set the filter to text files and allow the user to pick the required file. I have included commented out code for extracting the filename only from the Path and filename just in case you want it but it is not required in this case because you need both path and filename. Note that I have used space and underscores (which are line breaks in otherwise single lines of code) so that the code will not break at incorrect places in this post and can be copied directly into your VBA editor without having to fix broken line problems. Sub OpenTxtFile() Dim myTitle As String Dim sFile As String 'Path and file name Dim ShortName As String 'FileName only myTitle = "Select the required text file" With Application.FileDialog(msoFileDialogFilePicker) .AllowMultiSelect = False .Filters.Add "Text files", "*.txt", 1 .Title = myTitle If .Show = False Then Exit Sub End If sFile = .SelectedItems(1) End With 'Following line of code extracts the 'filename only if required 'ShortName = Right(sFile, Len(sFile) _ - InStrRev(sFile, "\")) Workbooks.OpenText Filename:= _ sFile, _ Origin:=437, _ StartRow:=1, _ DataType:=xlDelimited, _ TextQualifier:=xlDoubleQuote, _ ConsecutiveDelimiter:=True, _ Tab:=True, _ Semicolon:=True, _ Comma:=False, _ Space:=True, _ Other:=True, _ OtherChar:=":", _ FieldInfo:=Array(Array(1, 1), _ Array(2, 1), Array(3, 1), _ Array(4, 1), Array(5, 1), _ Array(6, 1), Array(7, 1), _ Array(8, 1), _ Array(9, 1)), _ TrailingMinusNumbers:=True End Sub -- Regards, OssieMac |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Macro Script help | Excel Worksheet Functions | |||
Macro Script | Excel Discussion (Misc queries) | |||
Macro or script | Excel Programming | |||
Macro or Script??? | Excel Programming |