Excel Workbook hiden when written to from Word

[Guest]

In Microsoft Word, using VB, I write to an Excel workbook. When I go to Excel and load the workbook, all sheets are hiden (I can unhide). I have been trying to find a way to unhide the workbook, either before saving it or when it is opened

The Workbook.Worksheet.Visible = True does not work from Word (compile error)

The App_WorkbookOpen sub (and other subs meant to run on opening) do not run, I think because the workbook is hidden

Any suggestions

John

 

[Tom Ogilvy]

for workbooks, the window has the visible property.

However, you probably need to make the Excel application visible

--
Regards,
Tom Ogilvy

In Microsoft Word, using VB, I write to an Excel workbook. When I go to

Excel and load the workbook, all sheets are hiden (I can unhide). I have
been trying to find a way to unhide the workbook, either before saving it or
when it is opened.

The Workbook.Worksheet.Visible = True does not work from Word (compile error).

The App_WorkbookOpen sub (and other subs meant to run on opening) do not

run, I think because the workbook is hidden.

 

[Henry]

John,
I'm not sure what you want.
Assume that you're opening Excel as AppExcel.
AppExcel.Visible = True 'Makes Excel visible.
Workbook.Visible = True 'Makes the workbook visible.
Worksheets ("YourNameHere").Visible 'Makes the sheet visible.
The reason your Workbook_open event is not firing is probably because the
"Enable Macros?" dialog window is opening, but you can't see it because
Excel is hidden.

HTH
Henry

In Microsoft Word, using VB, I write to an Excel workbook. When I go to

Excel and load the workbook, all sheets are hiden (I can unhide). I have
been trying to find a way to unhide the workbook, either before saving it or
when it is opened.

The Workbook.Worksheet.Visible = True does not work from Word (compile error).

The App_WorkbookOpen sub (and other subs meant to run on opening) do not

run, I think because the workbook is hidden.

 

[Tom Ogilvy]

Workbook.Visible = True 'Makes the workbook visible.

Seems you have made a few assumptions here, Henry. Care to elaborate.

The reason your Workbook_open event is not firing is probably because the
"Enable Macros?" dialog window is opening, but you can't see it because
Excel is hidden.

don't believe this dialog shows when a workbook is opened through
automation. I have never come across it. Certainly would put a wrench in
using automation.

 

[Guest]

Here is the sequence:
1. Load a Document in Word - on opening the document, my VB runs displaying a userform. When the "Save" command button is clicked, the Word document (using a different name) is saved.

2. Still in the "Save" command button clicked VB script, I then save the data to an Excel spreadsheet (across an intranet connection) using the following code:

Set WorkBook = GetObject("S:\TWI FORMS\data-Intro to Job Skills.xls")
Set myWorkSheet = WorkBook.Sheets("by date")

After writing in the data, I close the workbook using the following code:

WorkBook.Save
Set myWorkSheet = Nothing
Set WorkBook = Nothing

3. After closing Word, I open Excel and load the Workbook. I get the "Enable Macros" screen and click "Yes." There is no indication that the workbook has even been loaded -- the whole WorkBook is hidden. You have to unhide it even to get to the VB editor.

I have put a popup (MsgBox) as the first line to show that the function was being run (since breakpoints cannot be saved).

Thanks,

John H