Filename as variable from textbox Excel97 VBA

[MrsTFB]

I have the following code to open an Excel97 file with data imported from a Labview Data Acquisition program. It works beautifully to open the file, however, (There is ALWAYS an HOWEVER), I want to be able to prompt for the filename. In this case it is 092601, which will be the date, and allow the user to enter this. With all the existing code staying the same. I know I can name the filename a variable but with the 10Tank2_ portion being constant and only the date changing. Any ideas?!?!

Workbooks.OpenText FileName:="G:\QAProdDataAcq\10Tank2_092601.xls", 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), Array(4, 1))

Thanks for any ideas,
Bsimm GO TITANS

 

[dsi]

Dim sFileName As String
sFileName = InputBox("Enter a Name", "")

sFileName = "G:\QAProdDataAcq\10Tank2_" & _
            sFileName & ".xls"

Workbooks.OpenText FileName:=sFileName, 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), Array(4, 1))

 

[dsi]

One thing I forgot to mention. If you use the InputBox function, you should also include error handling to see if the user hits the Cancel button.

 

[Sawatzky]

dear bsimm

try this:

dim filenamesuffix as string
filenamesuffix= inputbox("put in the filesuffix&quot

' i would also include a method to test what the user entered like
' if textfilenamesuffix(filenamesuffix ) then
' and you should really find a method to be sure your user entered something that makes sense

Workbooks.OpenText FileName:="G:\QAProdDataAcq\10Tank2_" & filenamesuffix & ".xls", 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), Array(4, 1))

HTH

regards Astrid

 

[DarkSun]

bsimm,

It looks like your problem has been solved but I would just like to add this bit of knowledge.

You can use:

Application.GetOpenFilename

This will launch the standard Open Dialog box for the user to select a file.

Example:

MyFilename = Application.GetOpenFilename

Workbooks.OpenText FileName:=MyFilename, 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), Array(4, 1))

 

[MrsTFB]

I have not had a great amount of time to work with it, but so far the problem has not really been solved. It is giving me an error message that file is not found, when it IS there. I have not tried the last suggestion yet, so keep the ideas coming.

Thanks guys for your input!
Bsimm GO TITANS!!!

 

[Sawatzky]

dear bsimm,

try this:

Workbooks.OpenText FileName:="G:\QAProdDataAcq\10Tank2_" &trim( filenamesuffix) & ".xls",
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),
Array(4, 1))

regards Astrid

 

[MrsTFB]

You guys are GREAT!! Here's the code and how it works. I'm opening 4 different workbooks based upon the one input box, they are saved by date and am going to input the data into the comprehensive workbook, when I learn how to pull ONLY the last 3 rows from each workbook. It's coming!!

Thanks again,
Bsimm




sFileName = InputBox("Enter the date: (format 091401):", "&quot

'opens the QA comprehensive workbook, adds new sheet, names sheet date

Workbooks.Open FileName:= _
"G:\QAProdDataAcq\QAdataComp.xls"

Sheets.Add
ActiveSheet.Name = sFileName




sFileName1 = "G:\QAProdDataAcq\10Tank1_" & sFileName & ".xls"

Workbooks.OpenText FileName:=sFileName1, 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), Array(4, 1))
Columns("B&quot.Select
Selection.NumberFormat = "0.0"


sfilename2 = "G:\QAProdDataAcq\10Tank2_" & sFileName & ".xls"

Workbooks.OpenText FileName:=sfilename2, 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), Array(4, 1))
Columns("B&quot.Select
Selection.NumberFormat = "0.0"


sFileName3 = "G:\QAProdDataAcq\10Tank3_" & sFileName & ".xls"

Workbooks.OpenText FileName:=sFileName3, 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), Array(4, 1))
Columns("B&quot.Select
Selection.NumberFormat = "0.0"

sFileName4 = "G:\QAProdDataAcq\10Tank4_" & sFileName & ".xls"

Workbooks.OpenText FileName:=sFileName4, 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), Array(4, 1))
Columns("B&quot.Select
Selection.NumberFormat = "0.0"