Hello,
Need your help on a problem I have - it's probably easy, but I don't have much experience with VBA yet and can't get around it. I'm trying to create more user friendly of excel report that uses three different text files as the source of data (file names and location changes - it depends on the user who's running the report). I wanted to use a form to allow users to choose the location of the files before they run the macro. So the whole program consists of two separate macros: LoadForm and Report. LoadForm starts a form that looks something like this (sorry, but seems like cannot attach any files):
|----------- Text_Field_1 ------------| |-Button_1-|
|----------- Text_Field_2 ------------| |-Button_2-|
|----------- Text_Field_3 ------------| |-Button_3-|
|--Button_4--|
I wanted to define three variables equal to the files locations to use later to open files (they are not opened at the beginning, but on different stages of the process). So I have assigned following code to the Button_1:
Private Sub CommandButton1_Click()
Dim File1PathName
File1PathName = Application.GetOpenFilename( _
filefilter:="Text Files(*.txt), *.txt,", _
Title:="Please select Customer Credit report...", MultiSelect:=False)
End Sub
And then File2PathName to Button_2 and so on. Button_4 is then supposed to go back to the Report macro and open the files based on the values of the FileXPathName variables:
Workbooks.OpenText Filename:=File1PathName, Origin:=xlWindows, _
StartRow:=29, DataType:=xlFixedWidth, FieldInfo:= _
Array(Array(0, 1), Array(20, 1), .......
But File1PathName returns empty value and of course the whole thing fails. Is it because form is part of LoadForm and I'm trying to use the value in Report
Another question is: is there a way to display the path to the selected file (basically the value of FileXPathName) in the text field once file is selected "Open" button pressed
Your help is highly appreciated. Best regards.
Andrew
Using user form to create list of files to be open
Kaoticfen
Have assigned following code to Button_1:
Text_Field_1.Text = Application.GetOpenFilename("Text Files(*.txt),*.txt")
And then following to the Button_4:
Dim FilePathName
Dim r As Long
Dim k As Integer
Dim data
FilePathName = Text_Field_1.Text
k = FreeFile
Open FilePathName For Input As #k
r = 0
Do Until EOF(1)
Line Input #k, data
ActiveCell.Offset(r, 0) = data
r = r + 1
Loop
Close #k
It works, but takes a while to load if the file is big. If there is any way to improve it, I'd be greatful if you could let me know.
Thanks.
tkn0507
Hello,
As I understand you, you have only to save the path in a global variable. see code example below:
Public File1PathName As Variant
Private Sub CommandButton1_Click()
File1PathName = Application.GetOpenFilename("Text Files(*.txt), *.txt,", , _
"Please select Customer Credit report...")
End Sub
Private Sub Demo()
If File1PathName <> False Then
Workbooks.OpenText Filename:=File1PathName, Origin:=xlWindows, _
StartRow:=29, DataType:=xlFixedWidth, FieldInfo:= _
Array(Array(0, 1), Array(20, 1)...
End If
End Sub
You have to copy the declaration of the variable 'File1PathName' in the declaration part of a modul. that means in the first row and outside of procedures.
Regards,
FreakFrossard
trichards57
I guess that will do. Thanks for your help.
Best regards,
A.