Refering to a Workbook with a ever-changing name

youngda

Board Regular
Joined
Jun 13, 2006
Messages
81
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?
 

Excel Facts

Save Often
If you start asking yourself if now is a good time to save your Excel workbook, the answer is Yes

dcardno

Well-known Member
Joined
Nov 20, 2002
Messages
544
Office Version
  1. 2013
Platform
  1. Windows
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:
Code:
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

Well-known Member
Joined
Dec 15, 2005
Messages
818
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

Code:
Dim myWorkbook as Workbook

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

Code:
myWorkbook.activate
 

dcardno

Well-known Member
Joined
Nov 20, 2002
Messages
544
Office Version
  1. 2013
Platform
  1. Windows
...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

Board Regular
Joined
Jun 13, 2006
Messages
81

ADVERTISEMENT

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

Well-known Member
Joined
Apr 28, 2004
Messages
76,303
Office Version
  1. 365
Platform
  1. Windows
You don't need to activate.
Code:
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

Board Regular
Joined
Jun 13, 2006
Messages
81

ADVERTISEMENT

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

Well-known Member
Joined
Apr 28, 2004
Messages
76,303
Office Version
  1. 365
Platform
  1. Windows
Why do you need to activate the workbook?

There isn't normally any reason to do so.
 

youngda

Board Regular
Joined
Jun 13, 2006
Messages
81
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
 

Forum statistics

Threads
1,141,592
Messages
5,707,287
Members
421,500
Latest member
Alex2302

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top