Results 1 to 7 of 7
  1. #1
    New Lounger
    Join Date
    Mar 2013
    Posts
    4
    Thanks
    0
    Thanked 1 Time in 1 Post

    Question Can't save/close worksheets in Excel through VBA code

    I have some VBA code I wrote several years ago in Excel 2000. I'm now running Excel 2003 and a few things aren't working.

    While poking around trying to figure it out, I ran into some code (online) that said to turn on "Option Explicit" in order to resolve a problem I was having - not being able to save worksheets to a new file. (The resolution was not just to merely turn on Option Explicit, it was simply a part of the poster's solution along with his suggested replacement code).

    Well, I noticed that I had that (the Option Explicit) commented out in my code, so I turned it back on and now it's complaining about "Variable Not Defined" when I compile the code. (Since it was commented out, that leads me to believe I've always had this problem, it's not new.) Well, the variable (I'm guessing) appears to somehow be generated by the creation of a form (as I said, I wrote this several years ago so I'm not 100% clear on exactly what I did anymore).


    My original code:
    targetWB.Item(Workbooks.count).SaveAs FileName:=myFileName
    targetWB.Item(Workbooks.count).Close

    The suggested change (modified from the original, to suit my particular situation):
    targetWB.Item(Workbooks.count).Close SaveChanges:=True

    This allowed the file to save, but it still won't close. I turned Option Explicit back off so it would compile and so I could get this far but I'd like to do it the right way if possible.

    So, how do I resolve these issues please?

    Summary:
    There are two issues here: 1) Saving/closing the file properly and 2) defining the variable mentioned in the beginning so that the program compiles with Option Explicit.

    Thank you in advance

  2. The Following User Says Thank You to apb1963 For This Useful Post:

    kaybee (2014-03-06)

  3. #2
    Super Moderator
    Join Date
    Jan 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    3,520
    Thanks
    3
    Thanked 143 Times in 136 Posts
    The code you provided contains two variables. You will need to make sure both are declared earlier in the code. Also, naming a variable targetWB implies that it is of type Workbook so your code can cut out the Item(#) which looks like it wants to close the 'last opened workbook' rather than the workbook that is currently active. You need to be clear about which workbook should be saved and closed when there is more than one workbook open.
    Sub Samplecode()
    Dim myFileName As String
    Dim targetWB As Workbook
    Set targetWB = ActiveWorkbook
    myFileName = "D:\Work\temp.xlsm"
    targetWB.SaveAs Filename:=myFileName, FileFormat:=xlOpenXMLWorkbookMacroEnabled
    targetWB.Close
    End Sub
    Andrew Lockton, Chrysalis Design, Melbourne Australia

  4. #3
    Super Moderator
    Join Date
    Jan 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    3,520
    Thanks
    3
    Thanked 143 Times in 136 Posts
    The code you provided contains two variables. You will need to make sure both are declared earlier in the code. Also, naming a variable targetWB implies that it is of type Workbook so your code can cut out the Item(#) which looks like it wants to close the 'last opened workbook' rather than the workbook that is currently active. You need to be clear about which workbook should be saved and closed when there is more than one workbook open.
    Sub Samplecode()
    Dim myFileName As String
    Dim targetWB As Workbook
    Set targetWB = ActiveWorkbook
    myFileName = "D:\Work\temp.xlsm"
    targetWB.SaveAs Filename:=myFileName, FileFormat:=xlOpenXMLWorkbookMacroEnabled
    targetWB.Close
    End Sub
    Andrew Lockton, Chrysalis Design, Melbourne Australia

  5. #4
    New Lounger
    Join Date
    Mar 2013
    Posts
    4
    Thanks
    0
    Thanked 1 Time in 1 Post
    Quote Originally Posted by Andrew Lockton View Post
    The code you provided contains two variables. You will need to make sure both are declared earlier in the code. Also, naming a variable targetWB implies that it is of type Workbook so your code can cut out the Item(#) which looks like it wants to close the 'last opened workbook' rather than the workbook that is currently active. You need to be clear about which workbook should be saved and closed when there is more than one workbook open.
    Sub Samplecode()
    Dim myFileName As String
    Dim targetWB As Workbook
    Set targetWB = ActiveWorkbook
    myFileName = "D:\Work\temp.xlsm"
    targetWB.SaveAs Filename:=myFileName, FileFormat:=xlOpenXMLWorkbookMacroEnabled
    targetWB.Close
    End Sub
    Thanks for the response. I suppose I should have mentioned the variables were in fact defined. I was trying to keep things very simple, as I have a substantial amount of code and two separate problems (I suppose I should have created different posts but since they related I chose not to. Maybe a bad decision).

    Here's a bit more of the code I have. All variables are defined properly. "pathWithFileName" also gets initialized properly. The workbook gets created - and perhaps saved (not sure) but it's not being closed.

    I notice that your SET statement differs from mine, as does your inclusion of the FileFormat.

    You're absolutely right about wanting to close the last opened workbook. I remember when I first wrote this I had some troubles with this and I suspect that's why I chose to do it the way I did. I'll have to revisit that issue and see what makes the most sense at this time. The whole thing is inside a loop and I process rows of a sheet, where each row gets a new workbook. Ultimately I don't think it matters but your way is cleaner (unless I had a reason for doing it that way which I don't remember).

    Is there something wrong with what I'm doing below? As mentioned, the workbook gets created properly... (a window is opened with all the right data) just not closed.


    ' Give the new workbook a name
    Set targetWB = Workbooks.Add(Template:=pathWithFileName)

    ' Activate the new workbook
    Workbooks.Item(Workbooks.count).Activate

    '''''''''''''''''''''''''''''''''''''''''''''''''' '''''' Save the new workbook

    'targetWB.Item(Workbooks.count).SaveAs FileName:=myFileName 'OLD CODE
    'targetWB.Item(Workbooks.count).Close 'OLD CODE

    targetWB.Item(Workbooks.count).Close SaveChanges:=True 'NEW CODE

  6. #5
    New Lounger
    Join Date
    Mar 2013
    Posts
    4
    Thanks
    0
    Thanked 1 Time in 1 Post
    I tried elements of your code such as adding the file format. That made me realize that the template file that I'm using above is a .xlt file. Perhaps that has something to do with my problem? Here's the current code, which still doesn't save nor close the file:

    ' Give the new workbook a name
    Set targetWB = Workbooks.Add(Template:=pathWithFileName)

    ' Activate the new workbook
    targetWB.Activate

    '''''''''''''''''''''''''''''''''''''''''''''''''' '''''' Save the new workbook

    targetWB.SaveAs FileName:=myFileName, FileFormat:=xlOpenXMLWorkbookMacroEnabled
    targetWB.Close


    I also notice that the filename it is, and has been creating is the same as the template file... with a 1 added. So it's ignoring the ":=myFileName". (i.e. my file name is "tree.xlt" it opens the file as "tree1" I will point out, that I apparently had it at least partially right, because it did save at one point, but would not close. With the above, it does neither. I don't think the file format is correct - remember this is 2003.

    Thank you

  7. #6
    Super Moderator
    Join Date
    Jan 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    3,520
    Thanks
    3
    Thanked 143 Times in 136 Posts
    If you had declared the all variables then you wouldn't be getting an error when you enable option explicit. The error message you get will show you the variable that is undefined.

    Since you are using XL 2003 (and I neglected to pay attention to this) the file format I posted before was incorrect. The following should work better.
    Code:
      Dim myFileName As String, pathWithFileName As String
      Dim targetWB As Workbook
      
      'populate the string variables with paths to template and new workbook
      myFileName = "D:\Work\temp.xls"
      pathWithFileName = "D:\Work\Templates\Template.xlt"
    
      ' Create new workbook based on a template and assign to a variable
      Set targetWB = Workbooks.Add(Template:=pathWithFileName)
      
      ' Activate the new workbook (unnecessary considering following lines)
      targetWB.Activate
      'save and close document
      targetWB.SaveAs Filename:=myFileName, FileFormat:=xlNormal
      targetWB.Close
    Last edited by Andrew Lockton; 2013-03-12 at 15:56.
    Andrew Lockton, Chrysalis Design, Melbourne Australia

  8. #7
    New Lounger
    Join Date
    Mar 2013
    Posts
    4
    Thanks
    0
    Thanked 1 Time in 1 Post
    Changing the file format did the trick. It now saves & closes the file as expected.

    Thank you!

Posting Permissions

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