Referencing a cell value in a macro

[kittronald]

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.

 

[GS]

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"

 

[kittronald]

Garry,

Thanks for the response.

In the macro below, how can I replace ...

ActiveWorkbook.SaveAs Filename:="C:\Data\Open.txt",

with ...

Dim sFilename As String

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



**********************************************
Sub Macro1()
'
' Macro1
'

'
Application.DisplayAlerts = False
Sheets("Sheet2").Select
ActiveWorkbook.SaveAs Filename:="C:\Data\Open.txt", FileFormat:= _
xlText, CreateBackup:=False
Application.DisplayAlerts = True
Range("B1").Select
End Sub


- Ronald K.

 

[GS]

After serious thinking kittronald wrote :

Garry,

Thanks for the response.

In the macro below, how can I replace ...

ActiveWorkbook.SaveAs Filename:="C:\Data\Open.txt",

with ...

Dim sFilename As String

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



**********************************************
Sub Macro1()
'
' Macro1
'

'

Dim sFilename As String
sFilename = "C:\Data\" & Sheets("Sheet2").Range("A1").Text &".txt"

Application.DisplayAlerts = False
Sheets("Sheet2").Select

ActiveWorkbook.SaveAs Filename:=sFilename, FileFormat:= _
xlText, CreateBackup:=False

 

[kittronald]

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.

 

[GS]

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'!

 

[sfdsdf]

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.

 

[GS]

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