How to SaveAs in Excel/ VBA using cell values ?

[tful282]

I am fairly new with VB, any guidance is truly appreciated.
I am opening an Excel template and filling cells with values from an Access database. I want to be able to automate saving (SaveAs), and name the file with string values taken from 2 different cells.
How do I get these string values ?
This is what i have so far

Dim Rng1 As Range, Rng2 As Range
Dim StrCpy1 As Variant, StrCpy2 As Variant
Dim WrkSht As Worksheet
Dim WrkBk As Workbook
Set WrkBk = ActiveWorkbook
Set WrkSht = WrkBk.Sheets(2)
Set Rng1 = WrkSht.Range("A1&quot
Set Rng2 = WrkSht.Range("C1&quot
StrCpy1 =??????
StrCpy2=????
WrkBk.SaveAs FileName:="C:\My Documents\" & StrCpy1 & _
"_" & StrCpy2 & ".xls", _
FileFormat:=xlNormal

 

[scottrod]

You can name a Range for the cell that will contain the information you are trying to include in the SaveAs statement.

Say C6's contents is "Accounting"
I would name the Range C6 as sFileName so I could then say
StrCpy1=Range("sFileName&quot
Whatever is in that cell will be used to formulate your filename. Dim sFileName as String
Hope this helps you out.

 

[dsi]

You can skip the Range defining steps.

Dim StrCpy1 As String, StrCpy2 As String

StrCpy1 = WrkSht.Range("A1").Text
StrCpy2 = WrkSht.Range("C1").Text