how can i open a workbook in vba?

Hi,
I need some assistance to have a vba macro do the following:

1. I have  workbook, and want a procedure that runs On_Open.
2. The procedure shows you the File_Open dialog box and you select your file. (but defaults the path of the open dialog to make it easier for the user - i.e H:\Macro\)
3. The program analyses the name of that file and decides to name it as the previous business day.
4. It then saves a copy of the file under that name (previous business day - i.e in the format like October 8 2013.xlsx)

Thanks.
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:
This save the selected file of the previous business day. It takes holidays into consideration. It does that by looking for a list of holidays in column A of a sheet named Holidays. In the attached workbook I added a couple of "holidays" for testing purposes. After you finish testing, you will need to replace them with your real holidays and do it every year.

Here is the code

Sub OpenAndSave()

Dim EndDate As Date
Dim vSourceWB As Variant
Dim oSourceWB As Workbook

EndDate = WorksheetFunction.WorkDay(Now, -1, Sheets("Holidays").Range("A:A"))

' Change this to your default folder
ChDir "C:\Temp"
vSourceWB = Application.GetOpenFilename(Title:="Please choose the file", FileFilter:="Excel Files *.xlsx (*.xlsx),")

If vSourceWB = False Then
    MsgBox "No file specified.", vbExclamation
    Exit Sub
Else
    Set oSourceWB = Workbooks.Open(vSourceWB)
    ActiveWorkbook.SaveAs Format(EndDate, "mmmm dd yyyy") & ".xlsx", FileFormat:=51
    MsgBox ActiveWorkbook.Name & " Saved"
    ActiveWorkbook.Close

End If

End Sub

Open in new window

Q-28262356.xlsm
0
Martin LissOlder than dirtCommented:
Also the code sets the default folder to C:\Temp so you should change that to H:\Macro or whatever you want.
0
FaustulusCommented:
Hi,
I'm working on a slightly different file selector. If you have the time please wait for me to finish.
Faustulus
0
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

FaustulusCommented:
Thank you for waiting. I think it was worth the effort because it finally dawned on me that one doesn't have to open a file to rename it. One just renames it, that's all. Everything is easy once one knows how to do it.  :-)
The workbook has one sheet with two buttons, Start and Close. Use the Start button to call up the File_Open dialog, the Close button to close the workbook. Note that it will close without prompting to Save. If there is anything you want to save you have two ways to do it. One is to use the Save button in the VBE window. The other is to run the procedure 'ResetApplication' which you will find at the top of the 'Utilities' code sheet. This procedure will restore Excel to its usual, good-natured self.
I positioned my File_Open dialog box in such a way that its top left corner is directly below the sheet title ("Rename Report Workbook" - please modify as required), and the lower right corner directly above the Close button which, by the way, won't work until you press Cancel on the dialog box. Of course, this lovely arrangement depends to a large extent upon the size of your screen and the size and location of the dialog box. The latter will sort of remember where you dropped it. To the extent that you might wish to move the buttons to a more advantageous position you will find this code in the TheWorkbook module,
    SetButtonLocations "Start", BtnTop:=150, BtnLeft:=210
    SetButtonLocations "Close", BtnTop:=480, BtnLeft:=600

Open in new window

As you see, you can set the distance from the top of the screen to the top of the button and from the left of the screen to the right of the button for each button individually. You can run the Workbook_Open procedure like any other one on F5. Then press Start to see what you have achieved. Press Cancel to do the next adjustment, and remember that you must manually save because once you press Close all changes are lost.

Martin suggested the use of a Holiday list. That is a wonderful idea - the list is - if you can delegate its maintenance to an intern who will still be around when it expires. There are lists on the Internet, I believe, which you can download into an Excel worksheet. Meanwhile, I thought your idea more practical, since we all agreed that it would give the correct result most of the time. I programmed an InputBox with the suggested new date as default. It can be over-written and I added weekdays to its text to help with the decision. The method of calculating the previous workday has been further simplified by using the WORKDAY() worksheet function.

I think most of the big items have been attended to, and I hope I didn't slip up on a little thing like getting the direction mixed up in which you prefer to move the dates. There is one more thing, though. I have programmed the File_open dialog box to be pre-filled with the file of today's date. (This could be changed to yesterday's if that is your normal workflow.) The date is set correctly and you can click "Rename" to have the file selected and renamed if it exists, but the left third of the name doesn't show. I haven't been able to figure out why. Somehow I hope that the fault will have disappeared by tomorrow morning and/or by the time you load the file in your machine.
EXX-131010-Rename-File.xlsm
0
eastsidemarketAuthor Commented:
Martin - I do appreciate the constant attention on helping me with this and I will be sure to award some points for the help. The holidays sheet is something I don't want to go about doing. I agree with Faustulus that it's too much maintenance. Really appreciate the effort though!

Faustulus - love what you did here. I think this is a nice approach. Few things I want to highlight to adjust if possible.

1. I don't actually want to replace the sheet. I tested the START, and it took the sheet it opened and just renamed it. What I actually want to do is, take the sheet, and SAVE AS, doing so will keep the old workbook and now , the newly created workbook with a new date.

2. The date of which it is saving as, will not be previous date of the file i'm loading, it will be the NEXT day (but previous day from today's trade date).

i.e I'm opening a file titled October 8 2013.xlsx. I want to open that file , and save as October 9 2012.xlsx (with today being October 10th).

This file will always be previous business date, however copying from 2 business days before (hope that's more clear).

Thanks!
0
FaustulusCommented:
I was afraid of something like that, but no problem. I found it is easier to talk weekdays.
- On Monday you will look for the workbook of Thursday and assign it the date of Friday.
- On Tuesday it will be Friday's book, assigned Monday's date.
- On Wednesday it will be Monday's book assigned Tuesday's date.
Please confirm.

Now, in the light of morning:-
a) I would move the Close button to just below the Start button. So that both buttons are hidden by the dialog box.
b) I would suggest that you use different directories for the source and target directory, whether you keep the old file or not.
c) I would urge you to reconsider your file names. October 1 2013 has the worst of all worlds because 11 will sort before 2 and December before October. Striving for the best of both worlds you might end up with 131001 Oct 1 2013.
0
Martin LissOlder than dirtCommented:
For sort purposes I think better would be 2013 10 01

or 20131001

or either of the above with some prefix or suffix.
0
FaustulusCommented:
Martin: I have been using 131001 and _991231. The 4-digit year is preferrable only where you have regular occurrances of last century's dates, say, at least 50% of all file names within a folder.
0
Martin LissOlder than dirtCommented:
I have been using 131001 and _991231. The 4-digit year is preferrable only where you have regular occurrances of last century's dates…;
Why? _991231 and 131001 sort the same as 19991231 and 20131001, but when you look at the last two I think it's more apparent what they are than the first two. But I don't want to annoy the Asker with our small disagreement so I'll say no more.
0
eastsidemarketAuthor Commented:
I was afraid of something like that, but no problem. I found it is easier to talk weekdays.
- On Monday you will look for the workbook of Thursday and assign it the date of Friday.
- On Tuesday it will be Friday's book, assigned Monday's date.
- On Wednesday it will be Monday's book assigned Tuesday's date.
Please confirm.

confirmed.

sorting is not to be considered.

thanks!
0
FaustulusCommented:
The sorting we have been discussing here is done by Windows Explorer. If you ever have to find the file in your directory you will rely on WE's sorting, and if you didn't provide a sortable file name you won't find the file where you expect.
I'll try and fix the code tonight.
0
FaustulusCommented:
Here we go.
Have a great weekend.
EXX-131011-Copy-File.xlsm
0
eastsidemarketAuthor Commented:
works great thanks! just want to add one last item.

i want to open the newly saved/created .xlsx and keep it open.

thanks!
0
FaustulusCommented:
So, I'm letting the code close the workbook in which it resides.
That would be the line
CloseThisWorkbook
EXX-131012-Copy-Workbook---Open.xlsm
0
eastsidemarketAuthor Commented:
i see, thank you.

few things remaining which i could use your assistance on.

1. when it opens the newly saved file it adds a (1) in the title bar of the opened excel workbook (but doesn't save it with that).

ie. today it created a file October 11 2013.xlsx, and when its opened, it shows October 11 20131 (in the title bar of the workbook).

2.  how can i have the default path set when it searches for the file. i'd like to have it browse right in the default path, rather than having to navigate through everytime to find the sheet to copy / rename/save.

thanks!
0
FaustulusCommented:
The default path is set at the top of the code. If it is available on your machine the dialog box will open with the contents of the specified folder displayed.
In the code a folder existing on my computer is specified.
    ' modify path as required
'    Const PathName As String = "H:\Reports"
    Const PathName As String = "D:\My Documents"


The 1 is caused by an error in my code. Please correct it.
I place of Workbooks.Add Fn
use Workbooks.Open Fn
In the procedure CopyWorkbook.
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:
excellent work as always from both!!
0
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
Microsoft Excel

From novice to tech pro — start learning today.