Wild Card Path

[Chris Sergent]

I would like to write the appropriate code so that I
could save a file based on a current filename:

Example:
The filename is G:\030_Madison.xls
030_Madison is variable with various lengths, so there
are multiple folders such as:

030_Madison
060_Grundy
091_Sangamon

I would like to save my files into the appropriate folder
automatically in the following way.

1. Open the xls file 030_Madison.xls and save it as
G:\030_Madison\030_Madison.txt
2. Open the xls file 060_Grundy.xls and save it as
G:\060_Grundy\060_Grundy.txt

This is not a loop, I just want to be able to save the
xls file with the same name and in the appropriate folder.

Any assistance would be greatly appreciated.

 

[Tom Ogilvy]

Dim sName as String
sName = left(ActiveWorkbook.Name,len(Activeworkbook.Name)-4)
ActiveWorkbook.SaveAs FileName:="G:\" & sName & "\" & sName & ".txt", _
FileFormat:=xlText

Change the fileformat to the appropriate xlconstant (xlText,xlCSV, etc).

 

[Bernie Deitrick]

Chris,

Dim myFN As String
myFN = Replace(ActiveWorkbook.Name, ".xls", "")
ActiveWorkbook.SaveAs "G:\" & myFN & "\" & myFN & ".txt", xlText

HTH,
Bernie
MS Excel MVP