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

S

stakar

Active Member
Joined
Mar 6, 2004
Messages
333
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
 

Excel Facts

Last used cell?
Press Ctrl+End to move to what Excel thinks is the last used cell.
Sort by date Sort by votes
It should be:
Code: 
wbkObj.Windows(1).Visible = True
to unhide a workbook.
 
Upvote 0
Why are you using GetObject and also opening the file using workbooks.open?
 
Upvote 0
rorya said:
Why are you using GetObject and also opening the file using workbooks.open?
Click to expand...

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
 
Upvote 0
Try this:
Code: 
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
 
Upvote 0
rorya said:
Try this:
Code: 
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
Click to expand...

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!!
 
Upvote 0
Since the workbooks are opened in a hidden instance of Excel, they should not be visible. If you need to suppress open events, use:
Code: 
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
 
Upvote 0
Excellent!!
That was it!
Thank you very much , i really appreciate your help !!!
Thanks a lot
 
Upvote 0
rorya said:
Since the workbooks are opened in a hidden instance of Excel, they should not be visible. If you need to suppress open events, use:
Code: 
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
Click to expand...

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 !!!!
 
Upvote 0
You must log in or register to reply here.

Similar threads

H
  • Solved
Disabling All VBA code from worksheet and/or workbook
Replies
2
Views
339
hazmat
H
S
  • Solved
"Run-time error '91': Object variable or With block variable not set" error
Replies
5
Views
1K
Micron
M
E
  • Solved
Option buttons not clickable after running VBA export code
Replies
2
Views
360
Domenic
D
C
  • Question Question
VBA code stuck (extract data from worksheet)
Replies
1
Views
408
Alex Blakenburg
A
F
  • Question Question
VBA run code based off cell value
Replies
1
Views
708
igold
igold

Forum statistics

Threads
1,224,507
Messages
6,179,176
Members
452,893
Latest member
denay

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top