Excel VBA Close Workbook and Open Existing Workbook

[rjm65]

Here's the part of the code I'm having a problem with:

Dim fso
Dim fol As String
fol = "c:\" & Left(Sheets("PO").Range("B1"), 5) Set fso =
CreateObject("Scripting.FileSystemObject")
If Not fso.FolderExists(fol) Then
fso.CreateFolder (fol)
Else
End If
ActiveWorkbook.SaveAs fol & "\" & SuggName & ".xls"
ActiveWindow.Close
Workbooks.Open Filename:="C:\test.xls"

fol is my customer's name from B1 and SuggName is created by using
customer's name plus the date. The save as, create new directory part
of the code works great. The last two lines of code are what are giving
me a problem. After the active workbook is saved, I want to close it
and open a blank workbook (c:\test.xls in this example). The reason I
want to do this is because when the file is saved (it's a purchase
order for customer A), even if all my inputs are cleared, when the file
is resaved (with a new customer's name, say customer B), all the
macro's point to Customer A's file and they start up when I click their
respective buttons on my customized toolbar.

Any tips and pointers to help me out here would be greatly appreciated.
I'm just stumbling my way around VBA and really just picking up bits
and pieces of it from the internet, especially forums.

Thanks,
Raymond

 

[Sharad]

Excel VBA Close Workbook and Open Existing Workbook:

Wrong!
It should be
Excel VBA Open Existing Workbook and Close Workbook:

1. Add a varibale:
Dim Wb as Workbook.

2. Delete last two lines of your code and add following:
Set Wb = ActiveWorkbook
Workbooks.Open Filename:="C:\test.xls"
Wb.Close


Sharad