Referencing a cell value in a macro

  • Thread starter Thread starter kittronald
  • Start date Start date
K

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.
 
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.
Click to expand...

Dim sFilename As String

sFilename = ThisWorkbook.Path & "\" & Range("A1").Value & ".txt"
 
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.
 
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
'

'
Click to expand...
Dim sFilename As String
sFilename = "C:\Data\" & Sheets("Sheet2").Range("A1").Text &".txt"
Application.DisplayAlerts = False
Sheets("Sheet2").Select
Click to expand...

ActiveWorkbook.SaveAs Filename:=sFilename, FileFormat:= _
xlText, CreateBackup:=False
 
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,

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.
 
Back
Top