Help reading directory and filename of an open file 2

[Grogo]

Please I need some help! I am a total noob to VB. In fact today is the first time I've seen a VB line of code. I am familiar with the Aspect scripting language so I'm not totally lost. Here's my situation:

I have written a pair of Aspect scripts. One opens a data file containing Cisco switch names, telnets to to it, displays the port status, and captures it to a text file. There are 26 files.

The 2nd script uses the same data file, opens the text file, cleans it up, saves it as a text file but with a .xls extention.

Eventually I will have a 3rd Aspect script that will use DDE to have Excel open up a file and manipulate it.

Currently I have recorded an Excel macro that highlightes a column, does a text to column operation, autosizes the columns, and does a "save as" in Excel format using the same filename.

The problem is that I only want to have 1 script. If I try to open the next file and run the script it wants it to save it as the original filename. I need to modify the script so it reads the filename and directory of the file that is currently open and assign these to strings as the file and subdirectory it is in will always be different. Here is the code the recorded generated:

Sub Text2Column()
'
' Text2Column Macro
' Text to columns, autosize columns, save in Excel format.
'
' Keyboard Shortcut: Ctrl+j
'
Columns("A:A&quot.Select
Selection.TextToColumns Destination:=Range("A1&quot, DataType:=xlFixedWidth, _
FieldInfo:=Array(Array(0, 2), Array(5, 1), Array(25, 1), Array(36, 1), Array(49, 1), _
Array(54, 1), Array(59, 1)), TrailingMinusNumbers:=True
Cells.Select
Cells.EntireColumn.AutoFit
ChDir "C:\Documents and Settings\gjw0096\My Documents\Port Tracking\SEP2003"
ActiveWorkbook.SaveAs Filename:= _
"C:\Documents and Settings\gjw0096\My Documents\Port Tracking\SEP2003\cxksolthb0ga.xls" _
, FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False
End Sub

The SEP2003 is a subdirectory that will be created each month so the next one will be OCT2003, etc. The filename will also be different each time as I currently have 26 files.

I'm guessing I will need to declare some string variables to contain the static part of the directory tree, the subdirectory, and the filename. If necessary I can figure out how to extract parts I need using some sort of left$, mid$, and right$ commands then concatenate as needed.

What I can't seem to figure out is how to pull the information into these variables, sort of like system variables in Aspect. Any help would be greatly appreciated.

Oh, as a bonus it would be ideal to have 1 file with 26 sheets as opposed to 26 individual files. Is this possible with VB and if so how?

 

[BobRodes]

Read up on Mid, Left, Right, and InStr. Also, to put stuff into a string, just say
MyString = "Whatever"

HTH

Bob

 

[Grogo]

Thanks for your reply. I understand how to assign data to strings but how to you tell the script to read the path and filename of the file that is currently opened then assign the data to the strings?

 

[mmilan]

I really don't think you can do this... I suppose you might be able to use a dictionary object by assigning elements when you open a specfic file number, but that's hardly elegant...

 

[SeanGriffin]

Grogo

Look at ActiveWorkbook.FullName.
There are also other simular properties of ActiveWorkbook that might actualy be more of a help for you.

-Sean

 

[Grogo]

YES! I believe that is exactly what I am looking for. I'll give it a try. Thank you all.