After running a code trying to set a workbook unhidden with no success

[stakar]

Hi there!!
As the titles says i run the following code to unhide the 2 workbooks because are hidden with no luck The code updates 2 workbooks without to be visible to the user, but afterwards the two workbooks are hidden, so i want to unhidden them.

When it runs the--> "wbkObj.Workbook(1).Visible = True" there's an error

Im running vb6 & excel 2007
Please someone help me with this
Thanks a lot

--------------------------------------------------------
Sub ResaveAllWBsInFolder()

Dim PathDat As String
Dim BookName_1 As String
Dim BookName_2 As String
Dim wbkOld As Workbook
Dim wbkObj As Excel.Workbook

Application.ScreenUpdating = False
Application.EnableEvents = False
Application.DisplayAlerts = False
Application.ShowWindowsInTaskbar = False

BookName_1 = "Main Source.xlsb"
PathDat = ThisWorkbook.Path & "\" & BookName_1

Set wbkObj = GetObject(PathDat)

Set wbkOld = Workbooks.Open(PathDat, UpdateLinks:=True)
Application.StatusBar = "Updating: " & BookName_1
Calculate
wbkObj.Workbook(1).Visible = True 'unhide the workbok
wbkOld.Save
wbkOld.Close

BookName_2 = "Joker SEK Source.xlsb"
PathDat = ThisWorkbook.Path & "\" & BookName_2
Set wbkObj = GetObject(PathDat)
Set wbkOld = Workbooks.Open(PathDat, UpdateLinks:=True)
Application.StatusBar = "Updating: " & BookName_2
Calculate
wbkObj.Workbook(1).Visible = True 'unhide the workbok

wbkOld.Save
wbkOld.Close

Set wbkObj = Nothing

Application.DisplayAlerts = True
Application.EnableEvents = True
Application.ScreenUpdating = True
Application.ShowWindowsInTaskbar = True
Application.StatusBar = Empty

End Sub

 

[RoryA]

It should be:

wbkObj.Windows(1).Visible = True

to unhide a workbook.

 

[stakar]

It should be:

wbkObj.Windows(1).Visible = True

to unhide a workbook.

Thanks for the reply

I get an error "Automation error"
I dont understand why itsn't working

 

[RoryA]

Why are you using GetObject and also opening the file using workbooks.open?

 

[stakar]

Why are you using GetObject and also opening the file using workbooks.open?

I just found the code for the getobject to make my workbook visible.
I dont know how to fix it.
I just want to open the workbook as hidden, to update it, to save it and then to close it.
But when the user will open the workbook, it must be unhidden.

Can you correct the code for me ?
Thanks a lot

 

[RoryA]

Try this:

Sub ResaveAllWBsInFolder()
   Dim appOther          As Excel.Application
   Dim wbkOld            As Excel.Workbook
   Dim PathDat           As String
   Dim BookName_1        As String
   Dim BookName_2        As String

   BookName_1 = "Main Source.xlsb"
   BookName_2 = "Joker SEK Source.xlsb"
   PathDat = ThisWorkbook.Path & "\"

   Set appOther = New Excel.Application

   With appOther
      .Visible = False

      Set wbkOld = .Workbooks.Open(PathDat & BookName_1, UpdateLinks:=True)
      .Calculate
      wbkOld.Close savechanges:=True

      Set wbkOld = .Workbooks.Open(PathDat & BookName_2, UpdateLinks:=True)
      .Calculate
      wbkOld.Close savechanges:=True

      Set wbkOld = Nothing
   End With
   appOther.Quit

End Sub

 

[stakar]

Try this:

Sub ResaveAllWBsInFolder()
   Dim appOther          As Excel.Application
   Dim wbkOld            As Excel.Workbook
   Dim PathDat           As String
   Dim BookName_1        As String
   Dim BookName_2        As String

   BookName_1 = "Main Source.xlsb"
   BookName_2 = "Joker SEK Source.xlsb"
   PathDat = ThisWorkbook.Path & "\"

   Set appOther = New Excel.Application

   With appOther
      .Visible = False

      Set wbkOld = .Workbooks.Open(PathDat & BookName_1, UpdateLinks:=True)
      .Calculate
      wbkOld.Close savechanges:=True

      Set wbkOld = .Workbooks.Open(PathDat & BookName_2, UpdateLinks:=True)
      .Calculate
      wbkOld.Close savechanges:=True

      Set wbkOld = Nothing
   End With
   appOther.Quit

End Sub

Thanks for the code, but thats not the way i want it
Your code opens the workbooks even with the .visible=False , and because i have in the workbooks some forms with the .Workbooks.open(xxx) the forms appearing and expecting to push the ok button.
I dont know what to do!!

 

[RoryA]

Since the workbooks are opened in a hidden instance of Excel, they should not be visible. If you need to suppress open events, use:

Sub ResaveAllWBsInFolder()
   Dim appOther          As Excel.Application
   Dim wbkOld            As Excel.Workbook
   Dim PathDat           As String
   Dim BookName_1        As String
   Dim BookName_2        As String

   BookName_1 = "Main Source.xlsb"
   BookName_2 = "Joker SEK Source.xlsb"
   PathDat = ThisWorkbook.Path & "\"

   Set appOther = New Excel.Application

   With appOther
      .Enableevents = False
      .Visible = False

      Set wbkOld = .Workbooks.Open(PathDat & BookName_1, UpdateLinks:=True)
      .Calculate
      wbkOld.Close savechanges:=True

      Set wbkOld = .Workbooks.Open(PathDat & BookName_2, UpdateLinks:=True)
      .Calculate
      wbkOld.Close savechanges:=True

      Set wbkOld = Nothing
      .Enableevents = true
   End With
   appOther.Quit

End Sub

 

[stakar]

Excellent!!
That was it!
Thank you very much , i really appreciate your help !!!
Thanks a lot

 

[stakar]

Since the workbooks are opened in a hidden instance of Excel, they should not be visible. If you need to suppress open events, use:

Sub ResaveAllWBsInFolder()
   Dim appOther          As Excel.Application
   Dim wbkOld            As Excel.Workbook
   Dim PathDat           As String
   Dim BookName_1        As String
   Dim BookName_2        As String

   BookName_1 = "Main Source.xlsb"
   BookName_2 = "Joker SEK Source.xlsb"
   PathDat = ThisWorkbook.Path & "\"

   Set appOther = New Excel.Application

   With appOther
      .Enableevents = False
      .Visible = False

      Set wbkOld = .Workbooks.Open(PathDat & BookName_1, UpdateLinks:=True)
      .Calculate
      wbkOld.Close savechanges:=True

      Set wbkOld = .Workbooks.Open(PathDat & BookName_2, UpdateLinks:=True)
      .Calculate
      wbkOld.Close savechanges:=True

      Set wbkOld = Nothing
      .Enableevents = true
   End With
   appOther.Quit

End Sub

Rorya,
At last doesn't update the links when the workbooks are not visible.
When i exclude the " .Enableevents = False" then opens the workbooks and update them.
Maybe do you have anything in mind to update the workbooks and not to be opened??
Thanks in advance !!!!