how can i open a workbook in vba?

i am trying to open a workbook in vba and having some issues.

basically I want it to open the workbook with the name of the .xlsm as the previous date based and if this book does not exist, return a msgbox.

Sub OpenOldReport()
Dim strName As String

Select Case Day(Now)
    Case 2 To 6 ' Tuesday to Saturday
        strName = Format(Now - 1, "mmmm d yyyy")
    Case 7 ' Sunday
        strName = Format(Now - 2, "mmmm d yyyy")
    Case 1 ' Monday
        strName = Format(Now - 3, "mmmm d yyyy")
End Select

Set wkbThis = ThisWorkbook
  
  ' Open Client Workbook
  Set wkbClient = strName
  ' Set wkbClient = OpenWorkbook1()
  
  If wkbClient Is Nothing Then
    MsgBox "No valid workbook has been provided, Exiting..."
    Exit Sub
  Else
    If (wkbClient.Worksheets.Count > 0) Then
       Set wsClient = wkbClient.Worksheets(1)
    Else
       MsgBox "Unable to process - no worksheet available"
       Exit Sub
    End If
    
    If (wsClient Is Nothing) Then
      MsgBox "Null client worksheet..."
      Exit Sub
    Else
    End If
     End If
ActiveWorkbook.Open Filename:="H:\Report\" & strName & ".xlsm", FileFormat:= _
xlOpenXMLWorkbookMacroEnabled, CreateBackup:=False
End Sub

Open in new window

eastsidemarketAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Martin LissOlder than dirtCommented:
You don't describe the problem but I'm guessing that your having a problem with line 16.

Change line 16 to something like

' Change TheFilePath to be the path to your old workbooks.
Set wkbClient = Workbooks.Open(TheFilePath & strName)
0
FaustulusCommented:
Please try this code,
Option Explicit

Sub OpenOldReport()

    Const myPath As String = "H:\Report\"
    
    Dim strName As String
    Dim Wb As Workbook
    Dim Ws As Worksheet
    Dim B As Integer
    
    B = InStr("1,7", CStr(Day(Date)))
    strName = Format(Date - (B - IIf(B, 4, 1)), "mmmm d yyyy") & _
              ".xlsm"
                  
    If Dir(myPath & strName) = "" Then
        MsgBox "No valid workbook has been provided, Exiting..."
    Else
        On Error Resume Next
        ' check, if Wb is already open
        Set Wb = Workbooks(strName)
        If Err Then
            ' open it only if it wasn't already open
            Set Wb = Workbooks.Open(strName)
        End If
        On Error GoTo 0
        ' If it exists, it also has at least 1 sheet
        Set Ws = Wb.Worksheets(1)
        MsgBox "Workbook: " & Wb.Name & " is open." & vbCr & _
               "Worksheet(1) is '" & Ws.Name & "'"
    End If
End Sub

Open in new window

I figured that you would likely study the code. So, I put a few things inside that will make it worth your while. I hope you will enjoy it.
Let me know if you need anything explained.
0
eastsidemarketAuthor Commented:
Faustulus - does seem to work. i have a workbook in that path:
October 7 2013.xlsm and it says "No valid workbook has been provided, Exiting..."
0
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

Martin LissOlder than dirtCommented:
Did you try my simple change?
0
eastsidemarketAuthor Commented:
MartinLiss not working for me either. I also tried to edit a bit, but can't find the strname.

in addition, i need to open a file that not has previous business day, but 2 previous business days.

how can i edit my strname to do so?

thanks.

Sub OpenOldReport()
Dim strName As String
Dim Wkbthis As Workbook
Dim Wkbclient As Workbook
Dim wsclient As Worksheet



Select Case Day(Now)
    Case 2 To 6 ' Tuesday to Saturday
        strName = Format(Now - 1, "mmmm d yyyy")
    Case 7 ' Sunday
        strName = Format(Now - 2, "mmmm d yyyy")
    Case 1 ' Monday
        strName = Format(Now - 3, "mmmm d yyyy")
End Select

Set Wkbthis = ThisWorkbook
  
' Change TheFilePath to be the path to your old workbooks.
Set Wkbclient = Workbooks.Open("H:\Futures\Macros\F&O Report\" & strName & ".xlsx")
  ' Set wkbClient = OpenWorkbook1()
  
  If Wkbclient Is Nothing Then
    MsgBox "No valid workbook has been provided, Exiting..."
    Exit Sub
  Else
    If (Wkbclient.Worksheets.Count > 0) Then
       Set wsclient = Wkbclient.Worksheets(1)
    Else
       MsgBox "Unable to process - no worksheet available"
       Exit Sub
    End If
    
    If (wsclient Is Nothing) Then
      MsgBox "Null client worksheet..."
      Exit Sub
    Else
    End If
     End If
ActiveWorkbook.Open Filename:="H:\Futures\Macros\F&O Report\" & strName & ".xlsx", FileFormat:= _
xlOpenXMLWorkbookMacroEnabled, CreateBackup:=False
End Sub

Open in new window

0
eastsidemarketAuthor Commented:
to clarify-
basically i want to open the file from 2 business days ago, and save as with date from 1 business day prior. (that's what i'm trying to accomplish with this all).

as my report will always be prior day's data.
0
Martin LissOlder than dirtCommented:
The Day() function returns 1 for Monday, 2 for Tuesday, etc. So if you want to subtract 2 days and not include the weekend I believe you should do this

Select Case Day(Now)
    Case 3 To 7 ' Wednesday to Sunday
        strName = Format(Now - 2, "mmmm d yyyy")
    Case 1 to 2 ' Monday to Tuesday
        strName = Format(Now - 4, "mmmm d yyyy")
End Select

Open in new window

0
eastsidemarketAuthor Commented:
Martin the save as prevous business day code seems to not work anymore either.

below is everything I have so far.
2 issues:
1. SaveReport is saving the filename with today's date, and not previous business day. (i.e workbook saving as October 8 2013.xlsm) - should be October 7 2013.xlsm

2. The OpenOldReport is getting an error. See attached image.
highlighting - Set Wkbclient = Workbooks.Open("H:\Futures\Macros\F&O Report\" & strName & ".xlsx")

almost seems like it can't pull in strName.

Any ideas? appreciate the help in advance to both of you.

Option Explicit
Sub SaveReport()
Dim strName As String

'save workbook with previous businses day
Select Case Day(Now)
    Case 2 To 6 ' Tuesday to Saturday
        strName = Format(Now - 1, "mmmm d yyyy")
    Case 7 ' Sunday
        strName = Format(Now - 2, "mmmm d yyyy")
    Case 1 ' Monday
        strName = Format(Now - 3, "mmmm d yyyy")
End Select
ActiveWorkbook.SaveAs Filename:="H:\Futures\Macros\F&O Report\" & Format(Now, "mmmm d yyyy") & ".xlsm", FileFormat:=xlExcel8, _
        Password:="", WriteResPassword:="", ReadOnlyRecommended:=False, _
        CreateBackup:=False
End Sub

Sub OpenOldReport()
Dim strName As String
Dim Wkbthis As Workbook
Dim Wkbclient As Workbook
Dim wsclient As Worksheet

'open workbook from 2 business days ago
Select Case Day(Now)
    Case 3 To 7 ' Wednesday to Sunday
        strName = Format(Now - 2, "mmmm d yyyy")
    Case 1 To 2 ' Monday to Tuesday
        strName = Format(Now - 4, "mmmm d yyyy")
End Select

Set Wkbthis = ThisWorkbook
  
' Change TheFilePath to be the path to your old workbooks.
Set Wkbclient = Workbooks.Open("H:\Futures\Macros\F&O Report\" & strName & ".xlsx")
  ' Set wkbClient = OpenWorkbook1()
  
  If Wkbclient Is Nothing Then
    MsgBox "No valid workbook has been provided, Exiting..."
    Exit Sub
  Else
    If (Wkbclient.Worksheets.Count > 0) Then
       Set wsclient = Wkbclient.Worksheets(1)
    Else
       MsgBox "Unable to process - no worksheet available"
       Exit Sub
    End If
    
    If (wsclient Is Nothing) Then
      MsgBox "Null client worksheet..."
      Exit Sub
    Else
    End If
     End If
ActiveWorkbook.Open Filename:="H:\Futures\Macros\F&O Report\" & strName & ".xlsx", FileFormat:= _
xlOpenXMLWorkbookMacroEnabled, CreateBackup:=False
End Sub

Open in new window

10-8-2013-11-52-19-AM.jpg
0
Martin LissOlder than dirtCommented:
1. SaveReport is saving the filename with today's date, and not previous business day. (i.e workbook saving as October 8 2013.xlsm) - should be October 7 2013.xlsm
Change lines 14 to 16 to
ActiveWorkbook.SaveAs Filename:="H:\Futures\Macros\F&O Report\" & strName & ".xlsm", FileFormat:=xlExcel8, _
        Password:="", WriteResPassword:="", ReadOnlyRecommended:=False, _
        CreateBackup:=False


2. The OpenOldReport is getting an error. See attached image.
highlighting - Set Wkbclient = Workbooks.Open("H:\Futures\Macros\F&O Report\" & strName & ".xlsx")
If you are trying to open an xlsm file then you need to change .xlsx to .xlsm
0
eastsidemarketAuthor Commented:
1. my apologies. i want to save as .xlsx
method "saveas" of object'_workbook' failed
highlights:
ActiveWorkbook.SaveAs Filename:="H:\Futures\Macros\F&O Report\" & strName & ".xlsx", FileFormat:=xlExcel8, _
        Password:="", WriteResPassword:="", ReadOnlyRecommended:=False, _
        CreateBackup:=Fals

Open in new window

e

2. open work book, still getting error per previous attachment. i have a file in that directory:
October 4 2013.xlsx
highlights:
Set Wkbclient = Workbooks.Open("H:\Futures\Macros\F&O Report\" & strName & ".xlsx")

Open in new window

0
Martin LissOlder than dirtCommented:
1) I'm not sure what's wrong but put a breakpoint on that line and highlight Filename:="H:\Futures\Macros\F&O Report\" & strName & ".xlsx" when the code gets there. What does the value of that string show up as? Is it the path and name you want to open?

2) In OpenOldReport do similar. Put a breakpoint on line 36 and check the value of "H:\Futures\Macros\F&O Report\" & strName & ".xlsx"

I'm going out for a few hours but I'll check back with you if you still have problems.
0
eastsidemarketAuthor Commented:
hi MartinLiss - thanks for your reply..

still having issues here. I don't know why.  i added a breakpoint as requested but not sure how to check the value.
0
Martin LissOlder than dirtCommented:
Okay here's a picture of a line where I set a breakpoint.
Break PointIf you look closely you'll see that after the code got there (as indicated by the line being yellow, that I moved the cursor over the lngRow variable and it tells me that the value is 4. You can also select a valid expression like .Cells(lngRow, PO.VendorName) and then click the little "eye-glasses" Quick-Watch icon or enter Shift+F9 and VBA will show you a widow that contains the value of what you've selected.

I wrote this article on debugging that will teach you how to debug. Don't worry that it says VB6. Most of it applies to VBA as well.

A question for you. In your SaveReport you are saving .xlsm files but in your OpenOldReport you are trying gto open .xlsx files. Is there some internal or external process that changes the xlsm to xlsx?
0
eastsidemarketAuthor Commented:
that was a typo. it's fixed and still not fixing the issue:


for openoldreport:
Set Wkbclient = Workbooks.Open("H:\Futures\Macros\F&O Report\" & strName & ".xlsx")
highlighted this line - says Wkbclient = Nothing


in savereport, highlighted:
ActiveWorkbook.SaveAs Filename:="H:\Futures\Macros\F&O Report\" & strName & ".xlsx", FileFormat:=xlExcel8, _
        Password:="", WriteResPassword:="", ReadOnlyRecommended:=False, _
        CreateBackup:=False

says strName = ""

current code:
Sub SaveReport()
Dim strName As String

'save workbook with previous businses day
Select Case Day(Now)
    Case 2 To 6 ' Tuesday to Saturday
        strName = Format(Now - 1, "mmmm d yyyy")
    Case 7 ' Sunday
        strName = Format(Now - 2, "mmmm d yyyy")
    Case 1 ' Monday
        strName = Format(Now - 3, "mmmm d yyyy")
End Select
ActiveWorkbook.SaveAs Filename:="H:\Futures\Macros\F&O Report\" & strName & ".xlsx", FileFormat:=xlExcel8, _
        Password:="", WriteResPassword:="", ReadOnlyRecommended:=False, _
        CreateBackup:=False
End Sub

Sub OpenOldReport()
Dim strName As String
Dim Wkbthis As Workbook
Dim Wkbclient As Workbook
Dim wsclient As Worksheet

'open workbook from 2 business days ago
Select Case Day(Now)
    Case 3 To 7 ' Wednesday to Sunday
        strName = Format(Now - 2, "mmmm d yyyy")
    Case 1 To 2 ' Monday to Tuesday
        strName = Format(Now - 4, "mmmm d yyyy")
End Select

Set Wkbthis = ThisWorkbook
  
' Change TheFilePath to be the path to your old workbooks.
Set Wkbclient = Workbooks.Open("H:\Futures\Macros\F&O Report\" & strName & ".xlsx")
  ' Set wkbClient = OpenWorkbook1()
  
  If Wkbclient Is Nothing Then
    MsgBox "No valid workbook has been provided, Exiting..."
    Exit Sub
  Else
    If (Wkbclient.Worksheets.Count > 0) Then
       Set wsclient = Wkbclient.Worksheets(1)
    Else
       MsgBox "Unable to process - no worksheet available"
       Exit Sub
    End If
    
    If (wsclient Is Nothing) Then
      MsgBox "Null client worksheet..."
      Exit Sub
    Else
    End If
     End If
'ActiveWorkbook.Open Filename:="H:\Futures\Macros\F&O Report\" & strName & ".xlsx", FileFormat:= _
'xlOpenXMLWorkbookMacroEnabled, CreateBackup:=False
End Sub

Open in new window

0
Martin LissOlder than dirtCommented:
Set Wkbclient = Workbooks.Open("H:\Futures\Macros\F&O Report\" & strName & ".xlsx")
highlighted this line - says Wkbclient = Nothing
That's what you'd expect since wkbClient doesn't get set until that line executes which it hasn't yet. I suggest you put the breakpoint there so that you could see what the string you are using resolves to. So on that line select "H:\Futures\Macros\F&O Report\" & strName & ".xlsx" and press Shift+F9.

As far as xlsx and xlsm go, remember that is you save a workbook in xlsx format that all code will be removed.
0
eastsidemarketAuthor Commented:
what i decided to do is run the macro from a separate sheet that is a .xlsm.

this macro will open the .xlsx do what it's gotta do, then save as .xlsx.

the entire macro will be on a totally different workbook.
0
Martin LissOlder than dirtCommented:
You can also save as xls which is an older format but one which allows macros.
0
eastsidemarketAuthor Commented:
says "H:\Futures\Macros\F&O Report\" = "H:\Futures\Macros\F&O Report\.xlsx"

so sounds like its not pulling in strname.
0
eastsidemarketAuthor Commented:
also how about the openworkbook piece?

these are separate in that they are called from 2 different buttons.
0
Martin LissOlder than dirtCommented:
Doh!!! Day() gives the day of the month not the day number of the week, so change the code to determine strDay to this.


'open workbook from 2 business days ago
Select Case Weekday(Now, 2)
    Case 3 To 7 ' Wednesday to Sunday
        strName = Format(Now - 2, "mmmm d yyyy")
    Case 1 To 2 ' Monday to Tuesday
        strName = Format(Now - 4, "mmmm d yyyy")
End Select

Open in new window


Note that until today (the 8th) the old code would have set strName to some value because the day of the moth was in the range 1 to 7, but not the correct value. Today however, being the 8th, caused, as you saw when you set the breakpoint, strName had no value since only values 1 to 7 would have returned anything.
0
eastsidemarketAuthor Commented:
great, openworkbook works. thanks.

how about savereport(). For this it will save as previous day, and not previous 2 days.

'save workbook with previous businses day
Select Case Day(Now)
    Case 2 To 6 ' Tuesday to Saturday
        strName = Format(Now - 1, "mmmm d yyyy")
    Case 7 ' Sunday
        strName = Format(Now - 2, "mmmm d yyyy")
    Case 1 ' Monday
        strName = Format(Now - 3, "mmmm d yyyy")
End Select

Open in new window

0
Martin LissOlder than dirtCommented:
Change that code to the same code I just posted.
0
eastsidemarketAuthor Commented:
change the Select Case Weekday(Now, 2) to
Select Case Weekday(Now, 1) ? to represent 1 business day prior?
0
Martin LissOlder than dirtCommented:
You said
For this it will save as previous day, and not previous 2 days
.
So do you want it to save as 2 days prior to today or 1 day prior to today?
0
FaustulusCommented:
Whaw! You guys have been busy while I slept!
I did all the higher mathematics quite well but failed on elementary algebra. Please change the minus to plus, here:-
    strName = Format(Date + (B - IIf(B, 4, 1)), "mmmm d yyyy") & _
              ".xlsm"
It is this variable that must be added + (B - IIf(B, 4, 1), not subtracted as in the posted code.

eastsidemarket wrote,
to clarify-
basically i want to open the file from 2 business days ago, and save as with date from 1 business day prior. (that's what i'm trying to accomplish with this all).
Unless you delete some files you will end up with duplicate file names. So, the action of the macro should be to open the file, save a copy under another name and delete the original. Not hard to program but hard for me to link to the original question. For example, is there a need to create a worksheet object if no worksheet will be looked at? In fact, why don't you just rename the file?

The answer is in the file name. Your simple method of determining the name of the file you wish to treat isn't accurate enough. Holidays do happen, and there may be other reasons for a file not being available. But neither is your method of determining the new file name reliable other than most of the time.

Here is my suggestion:-
1. Create a workbook with a procedure that runs On_Open
2. That procedure shows you the File_Open dialog box and you select your file.
3. The program analyses the name of that file and decides what name it should have
4. It then saves a copy of the file under that name and deletes the original.

If you like that idea do the following:-
a) Post your new requirement under another thread
b) Post a link to the new question in this thread
c) Close this thread and award points
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
eastsidemarketAuthor Commented:
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
VB Script

From novice to tech pro — start learning today.