Results 1 to 7 of 7
  1. #1
    3 Star Lounger
    Join Date
    Mar 2006
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    289
    Thanks
    0
    Thanked 1 Time in 1 Post

    Saving a workbook (2003 sp2)

    The code below pauses at "Set xlwbk = Nothing" and prompts me to save the changes. How can save the changes without a prompt?

    Public Function PrepWkbk()
    Dim strFileName As String
    Dim xlObj As Object
    Dim xlwbk As Object
    Set xlObj = CreateObject("Excel.Application")
    xlObj.DisplayAlerts = True
    strFileName = "CatabaseNonCreditLoss.xls"
    xlObj.Workbooks.Open (strFileName)
    Call FillNotOnlyBlanks
    Set xlwbk = Nothing
    xlObj.Quit
    Set xlObj = Nothing
    Call IMPORT_DATA
    End Function
    Thanks
    chuck

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

    Re: Saving a workbook (2003 sp2)

    Immediately above the line you mention, insert this:

    xlwbk.Close SaveChanges:=True

  3. #3
    3 Star Lounger
    Join Date
    Mar 2006
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    289
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Saving a workbook (2003 sp2)

    You know it was one year ago today that I made my first post at Woody's Lounge. I have learned quite a bit since then from your help and reading the other posts. I even figured out the line you suggested but received Run-time error '91': Object variable or With Block Variable not set.

    I run this from an AutoExec macro but receive the same error when I step through as well.

    Public Function OpenApp()
    Dim strFileName As String
    Dim xlObj As Object
    Dim xlwbk As Object
    Set xlObj = CreateObject("Excel.Application")
    xlObj.DisplayAlerts = True
    strFileName = "CatabaseNonCreditLoss.xls"
    xlObj.Workbooks.Open (strFileName)
    Call FillNotOnlyBlanks
    xlwbk.Close SaveChanges:=True
    Set xlwbk = Nothing
    xlObj.Quit
    Set xlObj = Nothing
    Call IMPORT_DATA
    End Function
    Thanks
    chuck

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

    Re: Saving a workbook (2003 sp2)

    Oops. You never set xlwbk. Change

    xlObj.Workbooks.Open (strFileName)

    to

    Set xlwbk = xlObj.Workbooks.Open(strFileName)

  5. #5
    3 Star Lounger
    Join Date
    Mar 2006
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    289
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Saving a workbook (2003 sp2)

    That did it! I would not have figured that out.
    Thanks
    chuck

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

    Re: Saving a workbook (2003 sp2)

    If you never set an object, it has no content, and you cannot do anything with it.

  7. #7
    3 Star Lounger
    Join Date
    Mar 2006
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    289
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Saving a workbook (2003 sp2)

    Books are really great, but nothing compares to a teacher.
    Thanks
    chuck

Posting Permissions

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