Is it possible to reference a variable cell value in a macro ?

For example, A1 is linked to a combo box selection.

A macro called MACRO1 saves the current file under the filename of
<value of A1.txt .

If A1=Apple, the macro should save the current file as Apple.txt .

Any ideas ?



- Ronald K.

kittronald used his keyboard to write :
Is it possible to reference a variable cell value in a macro ?

Absolutely!


For example, A1 is linked to a combo box selection.

A macro called MACRO1 saves the current file under the filename of
<value of A1.txt .

If A1=Apple, the macro should save the current file as Apple.txt .

Any ideas ?



- Ronald K.

Dim sFilename As String

sFilename = ThisWorkbook.Path & "\" & Range("A1").Value & ".txt"

--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc

Garry,

Thanks for the reply.

Took me a while to get to the point where I could test the code
above.

I've made the following changes:

Sub Macro_Export_Output()
'
' Macro_Export_Output Macro
'

'
Dim sFilename As String
sFilename = "C:\Temp\" & Sheets("Data").Range("Data").Text & ".txt"
Application.DisplayAlerts = False
Sheets("Data").Select
ActiveWorkbook.SaveAs Filename:=sFilename, FileFormat:=xlText,
CreateBackup:=False
Application.DisplayAlerts = True
Range("A1").Select
End Sub


However, when the macro is run, the following error occurs:

Run-time error '1004'

Method 'SaveAs' of object _Workbook failed

Any ideas ?


- Ronald K.

Ronald,
I tried your code and it worked fine for me...

Sub ExportToTextFile()
Dim sFilename As String
sFilename = "C:\TempXls\" _
& ActiveWorkbook.Sheets("Data").Range("Data").Text _
& ".txt"
Application.DisplayAlerts = False
ActiveWorkbook.SaveAs sFilename, xlText
Application.DisplayAlerts = True
End Sub

...what I suspect is that the folder "Temp" does not exist and so the
resulting error. (SaveAs does not allow creating/renaming folders in
the process)

OR

...the active workbook - IS - the text file and you can't overwrite the
file because it's 'IN USE'!

--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc

Garry,

Thanks for getting back to me.

Google Groups hasn't been updating so I've had to find another way to get
back to you.

I figured out what the problem was.

When creating sFilename, I wasn't referencing the correct range to build
the filename.

There's a "Settings" worksheet that has a named range linked to a
ComboBox. That named range determines the filename prefix (i.e., "File1" in
File1.txt)

Below is the working macro:

Sub Macro_Export_Output()
'
' Macro_Export_Output Macro
'

'
Dim sFilename As String
sFilename = "C:\Temp\" & Sheets("Settings").Range("Data_Type").Text & ".txt"
Application.DisplayAlerts = False
Sheet1.Select
ActiveWorkbook.SaveAs sFilename, xlText
Application.DisplayAlerts = True
Sheets("Settings").Select
End Sub


Thanks for all the help ! +1,000,000



- Ronald K.

You're welcome! Glad you were able to get it working.
I appreciate the feedback!

--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc