I am working on a macro to export certain data to a txt file. Everything is
working now but I would like to add a little sizzle to it. Right now
everytime the macro is run it saves the data to a fixed file name. I would
like to have the file name reflect what is entered into a cell on a sheet.
here is the macro as I have it now.

Sub Macro3()
Sheets("Cad Script").Visible = True
Sheets("Cad Script").Select
Range("A1:F15").Select
Range("F15").Activate
Dim r As Range, c As Range
Dim sTemp As String
Open "ICIICAD.txt" For Output As #1
For Each r In Selection.Rows
sTemp = ""
For Each c In r.Cells
sTemp = sTemp & c.Text & Chr(9)
Next c
'Get rid of trailing tabs
While Right(sTemp, 1) = Chr(9)
sTemp = Left(sTemp, Len(sTemp) - 1)
Wend
Print #1, sTemp
Next r
Close #1
ActiveWindow.SelectedSheets.Visible = False
Sheets("Build").Select
End Sub

Everytime I use the macro the data is saved to ICIICAD.txt. If I do not move
or rename the file the macro simply overwrites the file. I would like the
file name to be dynamic and change based upon what I enter into cell A1 of
sheet1. I hope someone can understand my ramblings and either let me know how
this can be done or if it can be done at all.

Thanks, Lee

Sub Macro3()
Dim MyFileName as string
'no validation at all!
myFilename = worksheets("somesheetname").range("a1").value & ".txt"

Sheets("Cad Script").Visible = True
Sheets("Cad Script").Select
Range("A1:F15").Select
Range("F15").Activate
Dim r As Range, c As Range
Dim sTemp As String
Open myfilename For Output As #1
For Each r In Selection.Rows
sTemp = ""
For Each c In r.Cells
sTemp = sTemp & c.Text & Chr(9)
Next c
'Get rid of trailing tabs
While Right(sTemp, 1) = Chr(9)
sTemp = Left(sTemp, Len(sTemp) - 1)
Wend
Print #1, sTemp
Next r
Close #1
ActiveWindow.SelectedSheets.Visible = False
Sheets("Build").Select
End Sub

Lee Stiles wrote:

I am working on a macro to export certain data to a txt file. Everything is
working now but I would like to add a little sizzle to it. Right now
everytime the macro is run it saves the data to a fixed file name. I would
like to have the file name reflect what is entered into a cell on a sheet.
here is the macro as I have it now.

Sub Macro3()
Sheets("Cad Script").Visible = True
Sheets("Cad Script").Select
Range("A1:F15").Select
Range("F15").Activate
Dim r As Range, c As Range
Dim sTemp As String
Open "ICIICAD.txt" For Output As #1
For Each r In Selection.Rows
sTemp = ""
For Each c In r.Cells
sTemp = sTemp & c.Text & Chr(9)
Next c
'Get rid of trailing tabs
While Right(sTemp, 1) = Chr(9)
sTemp = Left(sTemp, Len(sTemp) - 1)
Wend
Print #1, sTemp
Next r
Close #1
ActiveWindow.SelectedSheets.Visible = False
Sheets("Build").Select
End Sub

Everytime I use the macro the data is saved to ICIICAD.txt. If I do not move
or rename the file the macro simply overwrites the file. I would like the
file name to be dynamic and change based upon what I enter into cell A1 of
sheet1. I hope someone can understand my ramblings and either let me know how
this can be done or if it can be done at all.

Thanks, Lee

--

Dave Peterson

Dave, thank you very much. It worked like a charm.

"Dave Peterson" wrote:

Sub Macro3()
Dim MyFileName as string
'no validation at all!
myFilename = worksheets("somesheetname").range("a1").value & ".txt"

Sheets("Cad Script").Visible = True
Sheets("Cad Script").Select
Range("A1:F15").Select
Range("F15").Activate
Dim r As Range, c As Range
Dim sTemp As String
Open myfilename For Output As #1
For Each r In Selection.Rows
sTemp = ""
For Each c In r.Cells
sTemp = sTemp & c.Text & Chr(9)
Next c
'Get rid of trailing tabs
While Right(sTemp, 1) = Chr(9)
sTemp = Left(sTemp, Len(sTemp) - 1)
Wend
Print #1, sTemp
Next r
Close #1
ActiveWindow.SelectedSheets.Visible = False
Sheets("Build").Select
End Sub

Lee Stiles wrote:

I am working on a macro to export certain data to a txt file. Everything is
working now but I would like to add a little sizzle to it. Right now
everytime the macro is run it saves the data to a fixed file name. I would
like to have the file name reflect what is entered into a cell on a sheet.
here is the macro as I have it now.

Sub Macro3()
Sheets("Cad Script").Visible = True
Sheets("Cad Script").Select
Range("A1:F15").Select
Range("F15").Activate
Dim r As Range, c As Range
Dim sTemp As String
Open "ICIICAD.txt" For Output As #1
For Each r In Selection.Rows
sTemp = ""
For Each c In r.Cells
sTemp = sTemp & c.Text & Chr(9)
Next c
'Get rid of trailing tabs
While Right(sTemp, 1) = Chr(9)
sTemp = Left(sTemp, Len(sTemp) - 1)
Wend
Print #1, sTemp
Next r
Close #1
ActiveWindow.SelectedSheets.Visible = False
Sheets("Build").Select
End Sub

Everytime I use the macro the data is saved to ICIICAD.txt. If I do not move
or rename the file the macro simply overwrites the file. I would like the
file name to be dynamic and change based upon what I enter into cell A1 of
sheet1. I hope someone can understand my ramblings and either let me know how
this can be done or if it can be done at all.

Thanks, Lee

--

Dave Peterson