creating a file name from a cell
 

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

creating a file name from a cell
 

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

creating a file name from a cell
 

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