Home » Forums » Open Source Learning Center » Question and Answer

worksheet becomes hidden by itself...

Submitted by anglo on 7 March, 2013 - 21:08

hi, to try this you need to create 2 new excel files (a.xls , b.xls), each containg 1 worksheet, and 1 word file.
copy the following code to the word file :
===================================================
''make reference to Microsoft Execl Object Library
Private Sub Document_Open()
Dim wbA As Excel.Workbook
Dim wbB As Excel.Workbook

Set wbA = GetObject(ThisDocument.Path & "\a.xls")
Set wbB = GetObject(ThisDocument.Path & "\b.xls")

wbA.Worksheets(1).Range("A1:Z100").Copy wbB.Worksheets(1).Range("A1:Z100")

wbA.Close False
wbB.Close True

Set wbA = Nothing
Set wbB = Nothing

ThisDocument.Parent.Quit
End Sub
===================================================

after running this word file - the range are copied and saved allright, BUT THE SAVED WORKSHEET BECOMES HIDDEN (unhide it from execl toolbar
and see that it was copied and saved).

WHY HIDDEN ???? who asked to hide ?
thanks

‹ Check range A10 to A45 and if cell not empty then same line in B must have numeric value. Dual linked cells across multiple worksheets ›
»
Nick's picture

agreed... it's a

Submitted by Nick on 8 March, 2013 - 12:26.
agreed... it's a bug.. here's a fix: 
Private Sub Document_Open()
 
Dim wbA As Excel.Workbook
Dim wbB As Excel.Workbook
Dim myExcel As Excel.Application
 
Set myExcel = New Excel.Application
 
Set wbA = myExcel.Workbooks.Open(ThisDocument.Path & "\a.xls")
Set wbB = myExcel.Workbooks.Open(ThisDocument.Path & "\b.xls")
 
wbA.Worksheets(1).Range("A1:Z100").Copy wbB.Worksheets(1).Range("A1:Z100")
 
wbA.Close False
wbB.Close True
myExcel.Quit
 
Set myExcel = Nothing
Set wbA = Nothing
Set wbB = Nothing
 
ThisDocument.Parent.Quit
End Sub
»

tnx, i'll use half of the fix :

Submitted by anglo on 8 March, 2013 - 21:28.

i knew the workaround...but in this particular case, i MUST copy that data from a.xls without using the workbooks.open command.
don't ask me why...it's quite complicated...take it as a fact.

that's why i used the getObject and copy without "opening" the file.

what i didnt think of was to access the 2 execels in 2 diffrent ways...so i tried it now and it's fine, so i'll ues half of your fix :
=========================
Private Sub Document_Open()
Dim wbA As Excel.Workbook
Dim wbB As Excel.Workbook
Dim exl As Excel.Application

Set exl = New Excel.Application

Set wbA = GetObject(ThisDocument.Path & "\a.xls")
'Set wbB = GetObject(ThisDocument.Path & "\b.xls")

Set wbB = exl.Workbooks.Open(ThisDocument.Path & "\b.xls")

wbA.Worksheets(1).Range("A1:Z100").Copy wbB.Worksheets(1).Range("A1:Z100")

wbA.Close False
wbB.Close True

exl.Quit

Set exl = Nothing
Set wbA = Nothing
Set wbB = Nothing

ThisDocument.Parent.Quit
End Sub
=======================================
tnx

»