Refering to a Workbook with a ever-changing name

[youngda]

I'm trying to write some code that will activate a workbook that has today's date in it. I am able to save the workbook with the following line, but later I need to activate it after its been created. THis line saves the book:
ActiveWorkbook.SaveAs Filename:= _
"C:\Documents and Settings\All Users\Desktop\" & Format(Date, "yymmdd") & " Schwab Trades.csv", _
FileFormat:=xlCSVMSDOS, CreateBackup:=False

I essentially want to later write
Workbooks("Today's Date Schwab Trades").activate

but I can't seem to quite get the syntax correct. Help anyone?

 

[dcardno]

If you are always referring to the workbook on the day it is created you would assemble the name string the same way as you did to save the workbook. For clarity, I always find it easiest to assemble the name in a string variable:

dim stFile as string

stFile = "C:\Documents and Settings\All Users\Desktop\" & Format(Date, "yymmdd") & " Schwab Trades.csv"
workbooks.open(stFile)

If you are trying to access a workbook created on an earlier day you will have to have some way of obtaining the date to open from the user.

 

[lozzablake]

create a variable as a workbook and then set your new workbook to this variable. that way you do not need to know the name eg

Dim myWorkbook as Workbook

set myWorkbook=activeworkbook

now just refer to myWorkbook instead of workbooks("SomeNameHere"). eg

myWorkbook.activate

 

[dcardno]

...that way you do not need to know the name eg:

Yes - but at some point you have to either know the name of the workbook (either to open it or to set the object variable) or know that it is active (to set the object variable). Once that is done, you are correct that you can just refer to the object without worrying about the name.

 

[youngda]

grrr

Well, previous advice has been useful, but I still get flustered when I try to adapt all of it to my situation. The following is the function I am trying to create written out in English. Maybe you can help me translate this into working code

OutputBook = "C:\Documents and Settings\All Users\Desktop\" & Format(Date, "yymmdd") & " Schwab Trades.csv"

'If OutputBook does not exist Then
'Workbooks.Add
'Else If OutputBook exists but is not Open
'Workbooks.open(OutputBook)
'Else If OutputBook exists and is open
'Workbooks(OutputBook).activate <-- This last statement hasa not been working when its isolated and alone????

 

[Norie]

You don't need to activate.

Dim wbOut As Workbook
    
    OutputBook = "C:\Documents and Settings\All Users\Desktop\" & Format(Date, "yymmdd") & " Schwab Trades.csv"

    If Dir(OutputBook) = "" Then
        Workbooks.Add
        Set wbOut = ActiveWorkbook
        wbOut.SaveAs OutputBook
    Else
        Set wbOut = Workbooks.Open(OutputBook)
    End If

 

[youngda]

Activate

Your code seems to work great, but I do actually need to activate the file in some situations. If the file is already exists, this function will save over the old file, which I don't want. If the file is open already, I want to add information to it, which teh rest of my function already can do successfully.

 

[Norie]

Why do you need to activate the workbook?

There isn't normally any reason to do so.

 

[youngda]

Your right

You were right, I realized I coudl streamline the code much better right after I submitted that. However, the new code has created a slight problem, I just posted a new thread, will you take a look at it? I much appreciate your help