Reference dynamic filename

[Bob Maloney]

I have the following part code included in macro 1:

Dim spath As String
spath = Range("B3").Value
ActiveWorkbook.SaveAs spath & ".xls"

(The text in range B3 is:
C:\Documents and Settings\Agents\07_11_14 Daily Withdrawal Team 1)


In macros 2, 3, 4, etc. I repeatedly copy and paste a dynamic sheet into
Daily Withdrawal Team 3.xls by referencing with hard code:
Sheets("Output").Select
Sheets("Output").Copy After:=Workbooks("07_11_14 Daily Withdrawal Team
1.xls").Sheets(1)
Cells.Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Range("A1").Select
Application.CutCopyMode = False
With ActiveSheet
.Name = Range("D1").Value
.Move After:=Sheets(Sheets.Count)
End With

Problem:
As the date component of the filename (07_11_14 ) will be dynamic, is there
any way to reference this in macros 2, 3, 4, etc without the need to
manually <Edit> <Replace> in VB?

Any assistance greatly appreciated

Cheers

Ozbobeee

 

[Jim Cone]

Declare a module level variable to reference the workbook...
At the top of the module, just below Option Explicit add
"Private wbNew as Excel.Workbook" ' no quote marks

In your code set the variable to the saved workbook...
ActiveWorkbook.SaveAs spath & ".xls"
Set wbNew = ActiveWorkbook

You can use the reference and not have to use the workbook name...
Sheets("Output").Copy After:=wbNew.Sheets(1)
-or- possibly eliminate moving the sheet...
Sheets("Output").Copy After:=wbNew.Sheets(wbNew.Sheets.Count)

If the macros are not all in the same module then change "Private" to "Public".
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware
(Excel Add-ins / Excel Programming)



"Bob Maloney"
wrote in message
I have the following part code included in macro 1:
Dim spath As String
spath = Range("B3").Value
ActiveWorkbook.SaveAs spath & ".xls"
(The text in range B3 is:
C:\Documents and Settings\Agents\07_11_14 Daily Withdrawal Team 1)

In macros 2, 3, 4, etc. I repeatedly copy and paste a dynamic sheet into
Daily Withdrawal Team 3.xls by referencing with hard code:
Sheets("Output").Select
Sheets("Output").Copy After:=Workbooks("07_11_14 Daily Withdrawal Team
1.xls").Sheets(1)
Cells.Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Range("A1").Select
Application.CutCopyMode = False
With ActiveSheet
.Name = Range("D1").Value
.Move After:=Sheets(Sheets.Count)
End With

Problem:
As the date component of the filename (07_11_14 ) will be dynamic, is there
any way to reference this in macros 2, 3, 4, etc without the need to
manually <Edit> <Replace> in VB?
Any assistance greatly appreciated
Cheers
Ozbobeee

 

[Dave Peterson]

First, I'm not sure I understood the problem.

I thought that you wanted to change the name of the file in code. That may not
be what you want.

This first section offers some ways to change the name of the file. The second
section is a way to refer to that workbook without using its (except when you
open it). (I'm kind of thinking that the second option is what you really
wanted--so don't give up on me!!!)

First option...

Maybe you could have the value in B3 obtained through a formula:

="C:\Documents and Settings\Agents\"
&TEXT(TODAY(),"yy\_mm\_dd")
&" Daily Withdrawal Team 1)"

or you could put the path (drive and folder) in one cell and the "suffix" in
another:

with worksheets("Somesheetnamehere")
spath = .range("b3").value & format(date,"yy_mm_dd") _
& .range("C3").value & ".xls"
end with

Or you could inspect the string, look for the last backslash and replace the 8
characters following it with the formatted date.

Dim sPath As String
Dim LastBackSlashPos As Long

sPath = "C:\Documents and Settings\Agents\07_11_14 Daily Withdrawal Team 1" &
".xls"
LastBackSlashPos = InStrRev(sPath, "\", -1, vbTextCompare)

sPath = Left(sPath, LastBackSlashPos) _
& Format(Date, "yy_mm_dd") & Mid(sPath, LastBackSlashPos + 9)

Debug.Print sPath

But all this depends on the fact that I replaced the date in that string with
today's date.

========
Second Option...

Dim wkbk as workbook
dim sPath as string

'your code to retrieve the name of the file.

'then open the file using that name:

Set wkbk = workbooks.open(filename:=spath)

'now you can use that workbook variable in your code later on:

Sheets("Output").Copy _
After:=wkbk.Sheets(1)

 

[Bob Maloney]

Jim and Dave,

Setting the variable to the saved workbook and referencing that in the other
macros was what I was after.

Thanks to both of you for your assistance.

Cheers

Ozbobeee