Open Workbook (Excel 2000)

[RNCIENG]

Good Morning,

Ok, here goes.

I have a .xlt and a .xls that work together. What I would like to do is open the .xls automatically when the .xlt is open. I've been using the following code but have the following problems. I would prefer to NOT open the spreadsheet, but another code won't work (copy & paste---copies cells from xlt and pastes to xls) if the xls isn't open.

CODE: (In the Open Event)
Workbooks.Open "T:All_NHSOperatorLBPBackUp.xls"
Workbooks(LBP1).Activate***which is really LBP but when it begins use, it becomes LBP1

ERROR/PROBLEMS:
The .xls opens and returns to the .xlt and immediatley the spreadsheet wants to be saved (I have the following code for that below) but if I cancel I get "subscript out of range" error msg. If I select end twice, it goes away....but I don't have my user forms that I should have.

SAVE CODE:
Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim strPath As String
Dim strFileName As String

If InStr(ThisWorkbook.FullName, "") = 0 Then
strPath = "T:All_NHSOperator"
strFileName = ThisWorkbook.Worksheets("PatientInfo").Range("G1") & Format(Date, "yyyymmdd") & ThisWorkbook.Worksheets("PatientInfo").Range("H1") & ".xls"
Application.Dialogs(xlDialogSaveAs).Show strPath & strFileName
End If
End Sub

Does anyone have any suggestions on how to bypass this issue...better yet....does anyone understand my ramblings?

Thanks,

[sdckapr]

In your code I would create workbook objects so the names are non-critical

CODE: (In the Open Event)
Dim wkbXLT as workbook
dim wkbXLS as workbook
set wkbxlt = activeworkbook
set wkbxls = Workbooks.Open "T:All_NHSOperatorLBPBackUp.xls"
wkbxlt.activate

Though the distinction xlt/xls is misleading, there is not xlt open, you have an xls created from your lbp.xlt but it is an xls file not a template.

I do not understand why your "file wants to be saved". Which file, what triggers it?

I am also unclear which code is in the backup and what code is in the file from the template. I am also a little confused on the problem could you elaborate and maybe call them WOrkbookA and workbookB or even wkbXLS and wkbXLT or something to tell us what is going on.

Steve

[LegareColeman]

If you click on Debug in the error message box, what VBA statement is hilighted. My gues is that when you click on Cancel, the workbook is not being renamed to the saved name, and you try to refer to it by that name somewhere in you code.

[RNCIENG]

Steve,

To explain further:

The xlt is the spreadsheet opening first "LBP.xlt" once open I would like for another spreadsheet to open "LBPBackUP.xls".

I too am not sure why it wants to be saved right away....once changes are made to LBP then it should save it automatically, but if no changes are made it shouldn't.

There isn't any code in LBPBackUp.

I hope I'm a little more clear.

Thanks for the help.

[LegareColeman]

You have code in your previous message that saves the workbook. That is probably why it is wanting to be saved immediately, but the snippets you have posted are not enough to figure out why your code is trying to save the workbook when it opens.

[sdckapr]

The file you open is not the template (xlt) it is a new workbook (xls) which is a copy of the template file.

When this file is closed (whether there are changes or not) you will run the before close routine, which will save the file since it has never been saved (since it has never been saved, there is not "" in the name)

Steve

[RNCIENG]

Steve,

I tried your suggestion with no luck.

Dim wkbXLT as workbook
dim wkbXLS as workbook
set wkbxlt = activeworkbook
set wkbxls = Workbooks.Open "T:All_NHSOperatorLBPBackUp.xls"****it fails here***** while writing this I get an error "expected End of Statement"
wkbxlt.activate

Also, I read your post below and I have the following in the BeforeClose:
Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim strPath As String
Dim strFileName As String

If InStr(ThisWorkbook.FullName, "") = 0 Then
strPath = "T:All_NHSOperator"
strFileName = ThisWorkbook.Worksheets("PatientInfo").Range("G1") & Format(Date, "yyyymmdd") & ThisWorkbook.Worksheets("PatientInfo").Range("H1") & ".xls"
Application.Dialogs(xlDialogSaveAs).Show strPath & strFileName
End If
End Sub

With all that said, it still brings me back to the original question and I'm probably further lost. LBPBackup.xls opens but when the code returns to LBP it wants to save, when Cancel is choosen, errors appear and the UserForm disappears. However, If I use the code below as it is right now....all is fine

Dim fNew As Boolean
Dim strFile As String
'Dim wkbXLTemp As Workbook
'Dim wkbXLSheet As Workbook
'Set wkbXLTemp = ActiveWorkbook
'set wkbXLSheet = Workbooks.Open "T:All_NHSOperatorLBPBackUp.xls"
'Set wkbXLSheet = "T:All_NHSOperatorLBPBackUp.xls"
'wkbXLTemp.Activate

Private Sub Workbook_Open()
Dim fNew As Boolean
Dim strFile As String
'Dim wkbXLTemp As Workbook
'Dim wkbXLSheet As Workbook
'Set wkbXLTemp = ActiveWorkbook
'set wkbXLSheet = Workbooks.Open "T:All_NHSOperatorLBPBackUp.xls"
'Set wkbXLSheet = "T:All_NHSOperatorLBPBackUp.xls"
'wkbXLTemp.Activate

Const strPath As String = "T:All_NHSOperator"
strFile = "LBPBackUP.xls"
Workbooks.Open Filename:=strPath
' Workbooks.Open Filename:=strPath & strFile
' Workbooks.Open Filename:=wkbXLSheet
fNew = (InStr(ThisWorkbook.FullName, "") = 0)
Sheets("AssessmentFU").cmdAssUpload.Visible = fNew
Sheets("PatientInfo").cmdPatUpdate.Visible = fNew
Sheets("SendMailer").cmdMailerUpdate.Visible = fNew
UserForm1.cmdIntake.Visible = False

End Sub


???????????????

[pieterse]

Change that to:

set wkbxls = Workbooks.Open("T:All_NHSOperatorLBPBackUp.xls")

[sdckapr]

<hr>I tried your suggestion with no luck.<hr>

What do you mean by "no Luck": Did it cause errors? What errors?

or did it just not fix your problem (I did not expect it to).

Do you have any code in any of the "worksheet_Activate" events for any of the worksheet or code in thisworkbook of the Workbook_SheetActivate event?
The problem seems to be that you either have a line to save the workbook being triggered or a line to close the workbook which runs the beforeclose event code and tries to save the file.

Have you tried stepping thru the code to see where it all goes and what it all does. This might give you a clue to what the line is and where you should investigate.

Steve

[sdckapr]

<img src=/S/blush.gif border=0 alt=blush width=15 height=15> My error. That is what i get for doing air-code <img src=/S/doh.gif border=0 alt=doh width=15 height=15>

Steve

[LegareColeman]

Change that statement that gets the error to:

<pre> Set wkbXLS = Workbooks.Open("T:All_NHSOperatorLBPBackUp.xls")
</pre>


The code you posted has a problem in the Workbook_Open event routine. You have these two lines:

<pre> Const strPath As String = "T:All_NHSOperator"
strFile = "LBPBackUP.xls"
Workbooks.Open Filename:=strPath
</pre>


When you get to the Workbooks.Open statement, strPath is a constant containing a path but no filename. That will fail. That routine should probably look like this:

<pre>Private Sub Workbook_Open()
Dim fNew As Boolean
Dim strFile As String, strPath As String
Dim wkbXLTemp As Workbook
Dim wkbXLSheet As Workbook
Set wkbXLTemp = ActiveWorkbook
Set wkbXLSheet = Workbooks.Open("T:All_NHSOperatorLBPBackUp.xls")
wkbXLTemp.Activate
strPath = "T:All_NHSOperator"
strFile = "LBPBackUP.xls"
Workbooks.Open Filename:=strPath & strFile
fNew = (InStr(ThisWorkbook.FullName, "") = 0)
Sheets("AssessmentFU").cmdAssUpload.Visible = fNew
Sheets("PatientInfo").cmdPatUpdate.Visible = fNew
Sheets("SendMailer").cmdMailerUpdate.Visible = fNew
UserForm1.cmdIntake.Visible = False
End Sub
</pre>


The code you posted is still not enough to determine why the file is trying to save immediately. If the form is being displayed, then it might be something in one of its event routines, or it could be something in some other event routine for the workbook.

[RNCIENG]

EUREKA!!!!!

it's working now...with a small exception....once the switch is made from the OPEN LBPBackup to LBP the SAVE Dialog still appears, but if the user cancels, no error msg, LBP appears where it should and the user can proceed as he/she should.

I've went through the entire project code and can't determine where or why the SAVE Dialog would be appearing other than in the BeforeClose as it should (but it's not closing...so why is it appearing?) at any rate, I think I can live with it as it is...ran out of options and energy.....LOL.

I used the following: (Legare's wanted to open LBPBackup twice and save twice because I didn't have it rem'd like I should have when I posted it)

Dim fNew As Boolean
Dim strFile As String, strPath As String
Dim wkbXLTemp As Workbook
Dim wkbXLSheet As Workbook
Set wkbXLTemp = ActiveWorkbook
Set wkbXLSheet = Workbooks.Open("T:All_NHSOperatorLBPBackUp.xls")
' strPath = "T:All_NHSOperator"**this should have been rem'd in mine**
' strFile = "LBPBackUP.xls" **this should have been rem'd in mine**
wkbXLTemp.Activate
Sheets("Update").Visible = False
fNew = (InStr(ThisWorkbook.FullName, "") = 0)
Sheets("AssessmentFU").cmdAssUpload.Visible = fNew
Sheets("PatientInfo").cmdPatUpdate.Visible = fNew
Sheets("SendMailer").cmdMailerUpdate.Visible = fNew
UserForm1.cmdIntake.Visible = False

Thanks so much for the help ...... it is greatly appreciated......REALLY!!!

[LegareColeman]

Sorry, I missed that there were two open statements that opened the file twice.

One question: was LBPBackup.xls created from the template? If so, is there a possibility that it contains some old VBA code that could be causing your problem?

You never answered my first question. What statement was hilighted when you got the error? That could give a clue about why it is wanting to save immediatly after opening. If you put a STOP statement at the beginning of the Workbook_Open routine and then step through the code, does that show you what is causing the file to want to save?

[RNCIENG]

1. Kinda, LBPBackup.xls OPENS (not created) from a template. However, this is a new spreadsheet that was only created a week ago and there isn't any code in it.
2. So sorry ... I missed the question about the error message...now that everything is working (other than the fact that it wants to SAVE when it switches back to LBP) there isn't an error msg. So at this time there isn't anything to DEBUG. My fault! At this time though, I can live with it the way it is....tired of banging the head and everyone's else's too!

3. I have come across a new issue though and am not quite sure how to handle it. Should be something rather simple for you guys. Both LBP.xlt and LBPBackup.xls are stored on a network drive "T" with a shortcut so the users can copy the shortcut for the LBP to their desktop. However, whenever LBP.xlt is opened, only MAIN (the spreadsheet holding the USERFORM appears but not the USERFORM. This happens to EVERYONE but me. I can open it from the shortcut or from my "C:" and everything runs fine, but no one else can get it to open correctly....what in the world have I done wrong now?

Thanks!

[HansV]

Do the others have macro security set to High? That would disable macros (including the one opening the userform.)