software

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(quot;Cad Scriptquot;).Visible = True
Sheets(quot;Cad Scriptquot;).Select
Range(quot;A1:F15quot;).Select
Range(quot;F15quot;).Activate
Dim r As Range, c As Range
Dim sTemp As String
Open quot;ICIICAD.txtquot; For Output As #1
For Each r In Selection.Rows
sTemp = quot;quot;
For Each c In r.Cells
sTemp = sTemp amp; c.Text amp; 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(quot;Buildquot;).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, LeeSub Macro3()
Dim MyFileName as string
'no validation at all!
myFilename = worksheets(quot;somesheetnamequot;).range(quot;a1quot;).value amp; quot;.txtquot;

Sheets(quot;Cad Scriptquot;).Visible = True
Sheets(quot;Cad Scriptquot;).Select
Range(quot;A1:F15quot;).Select
Range(quot;F15quot;).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 = quot;quot;
For Each c In r.Cells
sTemp = sTemp amp; c.Text amp; 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(quot;Buildquot;).Select
End Sub

Lee Stiles wrote:
gt;
gt; I am working on a macro to export certain data to a txt file. Everything is
gt; working now but I would like to add a little sizzle to it. Right now
gt; everytime the macro is run it saves the data to a fixed file name. I would
gt; like to have the file name reflect what is entered into a cell on a sheet.
gt; here is the macro as I have it now.
gt;
gt; Sub Macro3()
gt; Sheets(quot;Cad Scriptquot;).Visible = True
gt; Sheets(quot;Cad Scriptquot;).Select
gt; Range(quot;A1:F15quot;).Select
gt; Range(quot;F15quot;).Activate
gt; Dim r As Range, c As Range
gt; Dim sTemp As String
gt; Open quot;ICIICAD.txtquot; For Output As #1
gt; For Each r In Selection.Rows
gt; sTemp = quot;quot;
gt; For Each c In r.Cells
gt; sTemp = sTemp amp; c.Text amp; Chr(9)
gt; Next c
gt; 'Get rid of trailing tabs
gt; While Right(sTemp, 1) = Chr(9)
gt; sTemp = Left(sTemp, Len(sTemp) - 1)
gt; Wend
gt; Print #1, sTemp
gt; Next r
gt; Close #1
gt; ActiveWindow.SelectedSheets.Visible = False
gt; Sheets(quot;Buildquot;).Select
gt; End Sub
gt;
gt; Everytime I use the macro the data is saved to ICIICAD.txt. If I do not move
gt; or rename the file the macro simply overwrites the file. I would like the
gt; file name to be dynamic and change based upon what I enter into cell A1 of
gt; sheet1. I hope someone can understand my ramblings and either let me know how
gt; this can be done or if it can be done at all.
gt;
gt; Thanks, Lee

--

Dave Peterson

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

quot;Dave Petersonquot; wrote:

gt; Sub Macro3()
gt; Dim MyFileName as string
gt; 'no validation at all!
gt; myFilename = worksheets(quot;somesheetnamequot;).range(quot;a1quot;).value amp; quot;.txtquot;
gt;
gt; Sheets(quot;Cad Scriptquot;).Visible = True
gt; Sheets(quot;Cad Scriptquot;).Select
gt; Range(quot;A1:F15quot;).Select
gt; Range(quot;F15quot;).Activate
gt; Dim r As Range, c As Range
gt; Dim sTemp As String
gt; Open myfilename For Output As #1
gt; For Each r In Selection.Rows
gt; sTemp = quot;quot;
gt; For Each c In r.Cells
gt; sTemp = sTemp amp; c.Text amp; Chr(9)
gt; Next c
gt; 'Get rid of trailing tabs
gt; While Right(sTemp, 1) = Chr(9)
gt; sTemp = Left(sTemp, Len(sTemp) - 1)
gt; Wend
gt; Print #1, sTemp
gt; Next r
gt; Close #1
gt; ActiveWindow.SelectedSheets.Visible = False
gt; Sheets(quot;Buildquot;).Select
gt; End Sub
gt;
gt; Lee Stiles wrote:
gt; gt;
gt; gt; I am working on a macro to export certain data to a txt file. Everything is
gt; gt; working now but I would like to add a little sizzle to it. Right now
gt; gt; everytime the macro is run it saves the data to a fixed file name. I would
gt; gt; like to have the file name reflect what is entered into a cell on a sheet.
gt; gt; here is the macro as I have it now.
gt; gt;
gt; gt; Sub Macro3()
gt; gt; Sheets(quot;Cad Scriptquot;).Visible = True
gt; gt; Sheets(quot;Cad Scriptquot;).Select
gt; gt; Range(quot;A1:F15quot;).Select
gt; gt; Range(quot;F15quot;).Activate
gt; gt; Dim r As Range, c As Range
gt; gt; Dim sTemp As String
gt; gt; Open quot;ICIICAD.txtquot; For Output As #1
gt; gt; For Each r In Selection.Rows
gt; gt; sTemp = quot;quot;
gt; gt; For Each c In r.Cells
gt; gt; sTemp = sTemp amp; c.Text amp; Chr(9)
gt; gt; Next c
gt; gt; 'Get rid of trailing tabs
gt; gt; While Right(sTemp, 1) = Chr(9)
gt; gt; sTemp = Left(sTemp, Len(sTemp) - 1)
gt; gt; Wend
gt; gt; Print #1, sTemp
gt; gt; Next r
gt; gt; Close #1
gt; gt; ActiveWindow.SelectedSheets.Visible = False
gt; gt; Sheets(quot;Buildquot;).Select
gt; gt; End Sub
gt; gt;
gt; gt; Everytime I use the macro the data is saved to ICIICAD.txt. If I do not move
gt; gt; or rename the file the macro simply overwrites the file. I would like the
gt; gt; file name to be dynamic and change based upon what I enter into cell A1 of
gt; gt; sheet1. I hope someone can understand my ramblings and either let me know how
gt; gt; this can be done or if it can be done at all.
gt; gt;
gt; gt; Thanks, Lee
gt;
gt; --
gt;
gt; Dave Peterson
gt;