Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
File Browse Function?
I have a macro which opens *.txt files from one location. The current
version of the macro opens a *.txt file to perform the desired operations, closes the file, then opens the next *.txt file in the list. In order for the macro to know which files I want to open, I have to type in the file names into excel. I was wondering if there was a way to point to a particular folder and have the macro work on all of the files contained in that folder without having to type out the filename in excel? Excerpt of the code which opens the files is below... num = Application.InputBox("Enter the total number of files to be processed.") Sheets("File list").Select [a1].Select For x = 1 To num n = ActiveCell.Formula Workbooks.OpenText Filename:=n, Origin:=xlWindows _ , StartRow:=1, DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, _ ConsecutiveDelimiter:=False, Tab:=True, Semicolon:=False, Comma:=False _ , Space:=False, Other:=False, FieldInfo:=Array(Array(1, 1), Array(2, 1), _ Array(3, 1)), TrailingMinusNumbers:=True Thanks! |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
File Browse Function?
Maybe Getopenfilename method (see Help) helps you, is you use XL2003 or above.
Regards, Stefi €˛fallowfz€¯ ezt Ć*rta: I have a macro which opens *.txt files from one location. The current version of the macro opens a *.txt file to perform the desired operations, closes the file, then opens the next *.txt file in the list. In order for the macro to know which files I want to open, I have to type in the file names into excel. I was wondering if there was a way to point to a particular folder and have the macro work on all of the files contained in that folder without having to type out the filename in excel? Excerpt of the code which opens the files is below... num = Application.InputBox("Enter the total number of files to be processed.") Sheets("File list").Select [a1].Select For x = 1 To num n = ActiveCell.Formula Workbooks.OpenText Filename:=n, Origin:=xlWindows _ , StartRow:=1, DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, _ ConsecutiveDelimiter:=False, Tab:=True, Semicolon:=False, Comma:=False _ , Space:=False, Other:=False, FieldInfo:=Array(Array(1, 1), Array(2, 1), _ Array(3, 1)), TrailingMinusNumbers:=True Thanks! |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
File Browse Function?
Sorry, typo: if you use XL2003 or above.
Stefi €˛Stefi€¯ ezt Ć*rta: Maybe Getopenfilename method (see Help) helps you, is you use XL2003 or above. Regards, Stefi €˛fallowfz€¯ ezt Ć*rta: I have a macro which opens *.txt files from one location. The current version of the macro opens a *.txt file to perform the desired operations, closes the file, then opens the next *.txt file in the list. In order for the macro to know which files I want to open, I have to type in the file names into excel. I was wondering if there was a way to point to a particular folder and have the macro work on all of the files contained in that folder without having to type out the filename in excel? Excerpt of the code which opens the files is below... num = Application.InputBox("Enter the total number of files to be processed.") Sheets("File list").Select [a1].Select For x = 1 To num n = ActiveCell.Formula Workbooks.OpenText Filename:=n, Origin:=xlWindows _ , StartRow:=1, DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, _ ConsecutiveDelimiter:=False, Tab:=True, Semicolon:=False, Comma:=False _ , Space:=False, Other:=False, FieldInfo:=Array(Array(1, 1), Array(2, 1), _ Array(3, 1)), TrailingMinusNumbers:=True Thanks! |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
File Browse Function?
Thanks for the tip.
With this function, I see that it is possible to select an "Arrray" of files. What would i need to do to 1) select an array of files with the Getopenfilename method, then 2) perform a series of operations on each file one-at-a-time? |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
File Browse Function?
Sorry, I'm at a loss! I tried
filestoopen = Application.GetOpenFilename(MultiSelect:=True) It allowed to select multiple files, but returned only the first selected file name as a string, not an array of file names as it's stated in HELP. Maybe somebody else can explain the reason! Stefi €˛fallowfz€¯ ezt Ć*rta: Thanks for the tip. With this function, I see that it is possible to select an "Arrray" of files. What would i need to do to 1) select an array of files with the Getopenfilename method, then 2) perform a series of operations on each file one-at-a-time? |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
File Browse Function?
That's what I ran into as well. Thanks for giving it a shot.
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
File Browse Function?
Give this code a try...
Dim X As Long Dim FilesToOpen As Variant FilesToOpen = Application.GetOpenFilename(MultiSelect:=True) For X = LBound(FilesToOpen) To UBound(FilesToOpen) MsgBox "Selected File #" & X & ": " & FilesToOpen(X) Next Don't pick too many files as you should get a MsgBox for each one you select. Rick "fallowfz" wrote in message ... That's what I ran into as well. Thanks for giving it a shot. |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
File Browse Function?
How about something like this example:
Sub UseFileDialogOpen() Dim lngCount As Long ' Open the file dialog With Application.FileDialog(msoFileDialogOpen) .AllowMultiSelect = True .Show ' Display paths of each file selected For lngCount = 1 To .SelectedItems.Count MsgBox .SelectedItems(lngCount) Next lngCount End With End Sub "Dana DeLouis" wrote: I was wondering if there was a way to point to a particular folder and have the macro work on all of the files contained in that folder without having to type out the filename in excel? I believe one way if via the DIR command. Sub Demo() Dim s As String On Error Resume Next ChDir ("C:\Your_Path") s = Dir("*.txt") ' or DIR("") for all files If Err.Number = 76 Then MsgBox "Error: Path not found" End End If Do While Len(s) 0 Debug.Print s s = Dir() Loop End Sub -- HTH :) Dana DeLouis "fallowfz" wrote in message ... I have a macro which opens *.txt files from one location. The current version of the macro opens a *.txt file to perform the desired operations, closes the file, then opens the next *.txt file in the list. In order for the macro to know which files I want to open, I have to type in the file names into excel. I was wondering if there was a way to point to a particular folder and have the macro work on all of the files contained in that folder without having to type out the filename in excel? Excerpt of the code which opens the files is below... num = Application.InputBox("Enter the total number of files to be processed.") Sheets("File list").Select [a1].Select For x = 1 To num n = ActiveCell.Formula Workbooks.OpenText Filename:=n, Origin:=xlWindows _ , StartRow:=1, DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, _ ConsecutiveDelimiter:=False, Tab:=True, Semicolon:=False, Comma:=False _ , Space:=False, Other:=False, FieldInfo:=Array(Array(1, 1), Array(2, 1), _ Array(3, 1)), TrailingMinusNumbers:=True Thanks! |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
File Browse Function?
Hi Rick,
I tried your code but it gave a "Type mismatch" error at line For X = LBound(FilesToOpen) To UBound(FilesToOpen) Locals window displayed the type of FilesToOpen to be Variant/String, not an array as it was expected. Regards, Stefi €˛Rick Rothstein (MVP - VB)€¯ ezt Ć*rta: Give this code a try... Dim X As Long Dim FilesToOpen As Variant FilesToOpen = Application.GetOpenFilename(MultiSelect:=True) For X = LBound(FilesToOpen) To UBound(FilesToOpen) MsgBox "Selected File #" & X & ": " & FilesToOpen(X) Next Don't pick too many files as you should get a MsgBox for each one you select. Rick "fallowfz" wrote in message ... That's what I ran into as well. Thanks for giving it a shot. |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
File Browse Function?
JerryH -
That seemed to work, but I was looking for a way to tell the macro to work on the files which I've selected...not just show the file name. Is there a way to extend what you've done for this application? This piece of code below will populate an excel template with the *.txt file names in a specific directory. This saves me the time of typing each one out. Is there a way, perhaps via an input box, to not be tied to one specific directory? Sub ListFiles() Const MYPATH = "C:\MyDocuments\" Dim PutRow As Long, fName As String PutRow = 1 Columns("a").Clear fName = Dir(MYPATH & "*.txt") Cells(PutRow, "a") = fName PutRow = PutRow + 1 Do fName = Dir Cells(PutRow, "a") = fName PutRow = PutRow + 1 Loop Until fName = "" End Sub |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
File Browse Function?
I really don't understand how that could be. I tested the code before
posting it, and I re-tested it just now, and it worked perfectly for me. All I did was go to any code window (not the Immediate window though), type Sub Test() and End Sub to give it someplace to work from and copy/paste'd the code between them. When I run the Sub Test(), the file selector window appeared... I used the Control Key to click-select a few files (although you could only select one file if desired) and then clicked the Open button.... a MsgBox appeared for each file selected showing me its path and name. The code works flawlessly for me... every time... so I am not sure what to tell you. Is anyone else out there having trouble making the code I posted work? Rick "Stefi" wrote in message ... Hi Rick, I tried your code but it gave a "Type mismatch" error at line For X = LBound(FilesToOpen) To UBound(FilesToOpen) Locals window displayed the type of FilesToOpen to be Variant/String, not an array as it was expected. Regards, Stefi €˛Rick Rothstein (MVP - VB)€¯ ezt Ć*rta: Give this code a try... Dim X As Long Dim FilesToOpen As Variant FilesToOpen = Application.GetOpenFilename(MultiSelect:=True) For X = LBound(FilesToOpen) To UBound(FilesToOpen) MsgBox "Selected File #" & X & ": " & FilesToOpen(X) Next Don't pick too many files as you should get a MsgBox for each one you select. Rick "fallowfz" wrote in message ... That's what I ran into as well. Thanks for giving it a shot. |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
File Browse Function?
I did exactly what you wrote, the result (or rather the failure) is the same.
Can it be affected by some environmental factor (version, any settings, etc.)? I use Excel2003 SP3 Hungarian version. Thank you for your efforts! Regards, Stefi €˛Rick Rothstein (MVP - VB)€¯ ezt Ć*rta: I really don't understand how that could be. I tested the code before posting it, and I re-tested it just now, and it worked perfectly for me. All I did was go to any code window (not the Immediate window though), type Sub Test() and End Sub to give it someplace to work from and copy/paste'd the code between them. When I run the Sub Test(), the file selector window appeared... I used the Control Key to click-select a few files (although you could only select one file if desired) and then clicked the Open button.... a MsgBox appeared for each file selected showing me its path and name. The code works flawlessly for me... every time... so I am not sure what to tell you. Is anyone else out there having trouble making the code I posted work? Rick "Stefi" wrote in message ... Hi Rick, I tried your code but it gave a "Type mismatch" error at line For X = LBound(FilesToOpen) To UBound(FilesToOpen) Locals window displayed the type of FilesToOpen to be Variant/String, not an array as it was expected. Regards, Stefi €˛Rick Rothstein (MVP - VB)€¯ ezt Ć*rta: Give this code a try... Dim X As Long Dim FilesToOpen As Variant FilesToOpen = Application.GetOpenFilename(MultiSelect:=True) For X = LBound(FilesToOpen) To UBound(FilesToOpen) MsgBox "Selected File #" & X & ": " & FilesToOpen(X) Next Don't pick too many files as you should get a MsgBox for each one you select. Rick "fallowfz" wrote in message ... That's what I ran into as well. Thanks for giving it a shot. |
#13
Posted to microsoft.public.excel.programming
|
|||
|
|||
File Browse Function?
I am using Excel 2003 (11.8211.8202) SP3 which is the American English
version. Give the code I posted works flawlessly for me, I am not sure what else I can tell you. Hopefully someone who knows something about possible differences between international versions will come along and offer their input. Rick "Stefi" wrote in message ... I did exactly what you wrote, the result (or rather the failure) is the same. Can it be affected by some environmental factor (version, any settings, etc.)? I use Excel2003 SP3 Hungarian version. Thank you for your efforts! Regards, Stefi €˛Rick Rothstein (MVP - VB)€¯ ezt Ć*rta: I really don't understand how that could be. I tested the code before posting it, and I re-tested it just now, and it worked perfectly for me. All I did was go to any code window (not the Immediate window though), type Sub Test() and End Sub to give it someplace to work from and copy/paste'd the code between them. When I run the Sub Test(), the file selector window appeared... I used the Control Key to click-select a few files (although you could only select one file if desired) and then clicked the Open button.... a MsgBox appeared for each file selected showing me its path and name. The code works flawlessly for me... every time... so I am not sure what to tell you. Is anyone else out there having trouble making the code I posted work? Rick "Stefi" wrote in message ... Hi Rick, I tried your code but it gave a "Type mismatch" error at line For X = LBound(FilesToOpen) To UBound(FilesToOpen) Locals window displayed the type of FilesToOpen to be Variant/String, not an array as it was expected. Regards, Stefi €˛Rick Rothstein (MVP - VB)€¯ ezt Ć*rta: Give this code a try... Dim X As Long Dim FilesToOpen As Variant FilesToOpen = Application.GetOpenFilename(MultiSelect:=True) For X = LBound(FilesToOpen) To UBound(FilesToOpen) MsgBox "Selected File #" & X & ": " & FilesToOpen(X) Next Don't pick too many files as you should get a MsgBox for each one you select. Rick "fallowfz" wrote in message ... That's what I ran into as well. Thanks for giving it a shot. |
#14
Posted to microsoft.public.excel.programming
|
|||
|
|||
File Browse Function?
Stefi,
I tried your code but it gave a "Type mismatch" error at line For X = LBound(FilesToOpen) To UBound(FilesToOpen) Locals window displayed the type of FilesToOpen to be Variant/String, not an array as it was expected. Did you change this line: Dim FilesToOpen As Variant to Dim FilesToOpen As array? If you did, that is what's causing the type mishmash. Cliff Edwards |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Browse up to file | Excel Programming | |||
can't browse file | Excel Discussion (Misc queries) | |||
File Browse Function (Open dialog box for choosing file) | Excel Programming | |||
File Browse Box | Excel Programming | |||
Browse function | Excel Programming |