Results 1 to 3 of 3
  1. #1
    3 Star Lounger
    Join Date
    Jun 2001
    Location
    Salem, Oregon, USA
    Posts
    219
    Thanks
    0
    Thanked 0 Times in 0 Posts

    False.xls (Excel 2002 SP3)

    The code below is called from an Access (XP SP3) database, and it successfully creates a "False.xls" spreadsheet in MyDocuments, just exactly as I want EXCEPT: the name is not what I selected and the path is not what I had asked for ! <g>. I searched this forum and found a message thread, specifically message 280839 that indicated that Torquemada had encountered this in 2003, and JohnBF helped him, but I don't think the circumstances are exactly the same as mine. Here is my entire code for what I want to do, and if anything jumps out at you, any comments will be welcome:

    Public Function SpawnWorkbook()
    On Error GoTo SpawnWkbk_err

    Dim xlApp As New Excel.Application
    Dim NewBook As Excel.Workbook
    Set xlApp = CreateObject("excel.Application")

    Set NewBook = xlApp.Workbooks.Add(Template:="Cocuments and SettingsmeDesktopMySpreadsheet.xls")
    With NewBook
    .SaveAs FileName = "c:tempBlapSales.xls"
    End With

    SpawnWkbk_exit:
    NewBook.Close
    Set NewBook = Nothing
    xlApp.Quit
    Set xlApp = Nothing
    Exit Function

    SpawnWkbk_err:
    MsgBox CStr(Err.Number) & ": " & Err.Description
    GoTo SpawnWkbk_exit
    End Function

    * I might add that the template is just another spreadsheet, I don't know if it needs to be saved explicitely as a template? -- just in case it matters.
    thx
    Pat

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

    Re: False.xls (Excel 2002 SP3)

    1) You should remove the line
    <code>
    Set xlApp = CreateObject("excel.Application")
    </code>
    Declaring xlApp as New Excel.Application already starts Excel.

    2) The Save As line should be
    <code>
    .SaveAs FileName:="c:tempBlapSales.xls"
    </code>
    Note the := between FileName and the actual name.

  3. #3
    3 Star Lounger
    Join Date
    Jun 2001
    Location
    Salem, Oregon, USA
    Posts
    219
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: False.xls (Excel 2002 SP3)

    THANK YOU, HANS !
    I'm embarassed to say that I removed the ":" when some earlier code didn't work. I'm also embarassed to show what little I know about Excel VB, that I basically had a "new" and a "create." Thank you so much! It worked perfectly.
    Pat W.

Posting Permissions

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