Why does this sheet pop up? (Office 2000)

Topic

New Reply

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

Reply | Quote

Replies

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.

Reply | Quote

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.

Reply | Quote

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

Reply | Quote

Hi,

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

Thanks

Reply | Quote

I haven’t taken a good look at the code, so here’s my

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.

Reply | Quote

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.

Reply | Quote