Results 1 to 7 of 7
  1. #1
    FloorHor
    Guest

    Why does this sheet pop up? (Office 2000)

    I have a spreadsheet with code that will take information from that spreadsheet, open Word, and put that info into the document. Works great. The thing is, when I launch this thing, another spreadsheet pops up before the document comes up. It does not appear to make any difference other than I know it's making the thing slower and it's annoying. I have stepped through the program using Shift F8, but the spreadsheet does not pop up while I do this. But run it normally & bam, there it is!
    Here is my attempt at programming. Don't laugh. Have a look. Thanks!!

    Public Sub WriteQuote()

    'On Error GoTo WriteQuoteError

    Dim strLetter As String
    Dim strDate As String
    Dim strSalutation As String
    Dim strTemplateDir As String
    Dim objWord As Word.Application
    Dim objDocs As Word.Documents
    Dim prps As Object
    Dim dp As Object

    'Set the Document Properties of this quote calc
    Set dp = ActiveWorkbook.BuiltinDocumentProperties
    dp("Title") = ActiveSheet.Range("B2")
    dp("Subject") = ActiveSheet.Range("B5")
    dp("Author") = "Robbie Harrison"
    dp("Company") = "CCI Flooring Systems"
    dp("Category") = FormatNumber(ActiveSheet.Range("C6"), 0, vbUseDefault, vbUseDefault, vbTrue) & " sf"
    dp("Keywords") = "$" & FormatNumber(ActiveSheet.Range("F73"), 2, vbUseDefault, vbUseDefault, vbTrue)

    'Open Word invisibly
    Set objWord = CreateObject("Word.Application")

    strTemplateDir = "D:WINDOWSDesktopCCIWord Processor Files"
    strLetter = strTemplateDir & "Quote Template.dot"

    'Open a new quote based on this template
    Set objDocs = objWord.Documents
    objDocs.Add strLetter
    objWord.Visible = True

    'Write info from contact item to document custom doc properties
    Set prps = objWord.ActiveDocument.CustomDocumentProperties

    strDate = FormatDateTime(ActiveSheet.Range("F2"), vbLongDate)
    prps.Item("TodayDate").Value = strDate
    prps.Item("Name").Value = ActiveSheet.Range("B1")
    prps.Item("Address").Value = ActiveSheet.Range("B3")
    prps.Item("CompanyName").Value = ActiveSheet.Range("B2")
    prps.Item("AreaName").Value = ActiveSheet.Range("B5")
    prps.Item("QuoteNumber").Value = ActiveSheet.Range("F3")
    prps.Item("AreaSize").Value = FormatNumber(ActiveSheet.Range("C6"), 0, vbUseDefault, vbUseDefault, vbTrue)
    prps.Item("Amount").Value = FormatNumber(ActiveSheet.Range("F73"), 2, vbUseDefault, vbUseDefault, vbTrue)
    objWord.ActiveDocument.Fields.Update
    objWord.Activate

    'Set the Document Properties of the quote
    Set dp = objWord.ActiveDocument.BuiltinDocumentProperties
    dp("Title") = ActiveSheet.Range("B2")
    dp("Subject") = ActiveSheet.Range("B5")
    dp("Author") = "Robbie Harrison"
    dp("Company") = "CCI Flooring Systems"
    dp("Category") = FormatNumber(ActiveSheet.Range("C6"), 0, vbUseDefault, vbUseDefault, vbTrue) & " sf"
    dp("Keywords") = "$" & FormatNumber(ActiveSheet.Range("F73"), 2, vbUseDefault, vbUseDefault, vbTrue)

    WriteQuoteExit:
    Exit Sub

    WriteQuoteError:
    If Err.Number = 91 Then
    MsgBox "There is no Inspector object open; exiting"
    Else
    MsgBox "Error No: " & Err.Number & "; Description: " & Err.Description
    End If

    Resume WriteQuoteExit

    End Sub

  2. #2
    Lounger
    Join Date
    Nov 2001
    Location
    Fort Dodge, Iowa, USA
    Posts
    38
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Why does this sheet pop up? (Office 2000)

    Just a couple quick questions to flesh out the situation. First, does this pop-up happen independent of whether you've got other files open? Even having the Book1 (unsaved, created-by-Excel workbook) open can cause problems; strange but true. Second, is it possible that there is code imbeded in any of the documents this manipulates (i.e. the template you set: "D:WINDOWSDesktopCCIWord ProcessorQuote Template.dot")?
    Just looking over the code, it looks like it should work. There might be a piece of this puzzle, however, that isn't even making onto your radar. In my experience, 9/10 of all _simple_ problems evaporate either when someone else is going over my code with me or when I'm trying to explain the problem to them.
    Hope this helps.

  3. #3
    2 Star Lounger
    Join Date
    Feb 2001
    Posts
    141
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Why does this sheet pop up? (Office 2000)

    I haven't taken a good look at the code, so here's my <img src=/S/2cents.gif border=0 alt=2cents width=15 height=15>

    One Excel trick that helps speed things up is

    Application.screenupdating = False

    Then at the end of your macro put

    Application.screenupdating = True

    That might take care of your pesky pop-up sheet problem. <img src=/S/shrug.gif border=0 alt=shrug width=39 height=15>

  4. #4
    FloorHor
    Guest

    Re: Why does this sheet pop up? (Office 2000)

    Hi,
    Thanks for the info. I'm a vb newbie, so any new trick helps. It didn't help to fix this problem , although your lines are now in my code, so take a closer look if you've got time.

  5. #5
    FloorHor
    Guest

    Re: Why does this sheet pop up? (Office 2000)

    Hi,

    I've been out of town, so I'm just now getting back to it.

    I guess a little more back ground would be helpful. I use Outlook for my contact management. From a Contact screen I press a button and a spreadsheet, quote.xlt, containing the code above pops up with the contact info in it.

    After I do my calcs in this spreadsheet, I press a button that runs the code in the spreadsheet. It's actually pretty slick for a vb idiot like me.

    So when the code runs, Outlook is open along with quote.xlt (or if I happen to have saved the sheet, some other name...doesn't make a difference with this problem). So after this code runs the following are open: Outlook, quote.xlt, this mysterious sheet, and quote.dot.

    One odd thing though, or at least I think it is odd, the spreadsheet that mysteriously opens is the same every time. It is the file that I save quote.xlt as the first time. It has only changed one time. I lost a lot of stuff on my hard drive including my copy of quote.xlt. I got a copy off of my salesman's machine and sure enough now the first file I Saved As now pops up every time.

    Maybe this info will help.

  6. #6
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Why does this sheet pop up? (Office 2000)

    Where is Quote.xlt saved ?. Is it in your XLStart folder ? - if so you should removve it from there and place it in your Templates folder or some alternate folder. All files in XLStart are open each time you launch Excel. It is possible to have more than one XLStart folder depending on the OS, so it might be a good idea to search for Quote.xlt just to be sure of it's location.

    Andrew C

  7. #7
    FloorHor
    Guest

    Re: Why does this sheet pop up? (Office 2000)

    Hi,

    Quote.xlt is in another folder. It is in the "spreadsheets" folder of the path in my code.

    Thanks

Posting Permissions

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