Passing Parameters while opening workbook. 2

[mveera]

Hi,

I would like if there is a way to pass parameters while opening a workbook.

Whenever i open the workbook i query the DB to get some parameters. I have created a template file which i will be using to create other workbooks. To create a workbook i will have to open the template file and do a saveas. When i open the template file i don't want it to access the DB which i do on workbook_open.
is there any way i can prevent this?

Currently i set a cell in the template file to some value and check in workbook_open. if the value is "T" i don't access the DB. Is there anyother clean way to obtain this?

Thanks
Veera

 

[RobBroekhuis]

I don't know if it's clean, but here's kind of a sneaky way to do it: when opening the template from code, set
application.screenupdating=false
Then, in your template's workbook_open event, check this parameter. It will always be true when the workbook is opened by the user (because this parameter's value resets to true when macros finish executing), but remains false if code is executing.
Rob

 

[rmikesmith]

Rob,

That is pretty sneaky.
I got the impression Veera was creating new workbooks from the template manually, not via code. If that's the case, here is another suggestion:

First, use the template workbook as a true template; i.e. save it as an xlt file. Create new workbooks based on the template by selecting File/New... and choosing the template name. With that setup, create the following Workbook_Open event procedure in the template's ThisWorkbook module:

Private Sub Workbook_Open()
  If InStr(1, Me.Name, ".", vbTextCompare) = 0 Then Exit Sub
' Code to query the database goes here
  
End Sub

When a new workbook is created the template file does not open, so its Workbook_Open event doesn't fire. The newly created workbook doesn't yet have a file extension as part of its name, so no "." and, therefore, its Workbook_Open event doesn't fire. After being saved, its Workbook_Open code will execute on each opening thereafter.

p.s. If you need to actually open the template to make changes, hold down the SHIFT key while clicking OK in the File/Open dialog. This will prevent the Workbook_Open event procedure from executing.

HTH
Mike

 

[mveera]

Thanks Mike for the suggestion. It works fine.

This is what i do

Workbooks.Open ("d:\temp\Template.xlt&quot
Dim tempWB As Workbook
Set tempWB = Workbooks(ActiveWorkbook.Name)
With tempWB
.SaveCopyAs "d:\temp\Template1.xls"
.Close SaveChanges:=False
End With


In the workbook open event i do the same thing that u suggested.

If InStr(1, Me.Name, ".", vbTextCompare) = 0 Then
Exit Sub
End If

Thank You Very Much.

Is there any way save a copy of the template or for that matter any workbook without opening and then doing save?

Thanks
Veera

 

[RobBroekhuis]

Check out the FileCopy command. It's pretty much a general-purpose copy tool, not limited to Excel files.
Rob

 

[rmikesmith]

Veera,

Use

FileCopy Source, Dest

where Source and Dest are strings representing the full path/filename.


HTH
Mike

 

[rmikesmith]

Rob,

Sorry... Attack of the parallel universe phenomenon


Mike

 

[RobBroekhuis]

Getting the answer twice never hurts
Rob

 

[mveera]

Thanks for the answer.

I didn't use FileCopy b'cos i had to open the new workbook to do changes after saving. this resulted in leaving the excel application open as i was doing the save from access. So i created a Excel application and closed after saving.

Thanks
Veera