Page 1 of 2 12 LastLast
Results 1 to 15 of 18
  1. #1
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Louisville, Kentucky, USA
    Posts
    605
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Open Workbook (Excel 2000)

    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,
    Roberta Price <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Open Workbook (Excel 2000)

    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

  3. #3
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Open Workbook (Excel 2000)

    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.
    Legare Coleman

  4. #4
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Louisville, Kentucky, USA
    Posts
    605
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Open Workbook (Excel 2000)

    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.
    Roberta Price <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

  5. #5
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Open Workbook (Excel 2000)

    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.
    Legare Coleman

  6. #6
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Open Workbook (Excel 2000)

    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

  7. #7
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Louisville, Kentucky, USA
    Posts
    605
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Open Workbook (Excel 2000)

    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


    ???????????????
    Roberta Price <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

  8. #8
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Open Workbook (Excel 2000)

    Change that to:

    set wkbxls = Workbooks.Open("T:All_NHSOperatorLBPBackUp.xls")
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  9. #9
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Open Workbook (Excel 2000)

    <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

  10. #10
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Open Workbook (Excel 2000)

    <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

  11. #11
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Open Workbook (Excel 2000)

    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.
    Legare Coleman

  12. #12
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Louisville, Kentucky, USA
    Posts
    605
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Open Workbook (Excel 2000)

    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!!!
    Roberta Price <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

  13. #13
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Open Workbook (Excel 2000)

    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?
    Legare Coleman

  14. #14
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Louisville, Kentucky, USA
    Posts
    605
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Open Workbook (Excel 2000)

    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!
    Roberta Price <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

  15. #15
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Open Workbook (Excel 2000)

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

Page 1 of 2 12 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •