I don't know what I've done to it. I have on open and before save events, but
I can't see the problem. I want the document to open visible.

Can you have a look and tell me if there's something I'm missing?

Private Sub Workbook_Open()
Dim wbk1 As Workbook
Dim wbk2 As Workbook

On Error Resume Next
Set wbk1 = Workbooks("Export_Requests.csv")
Set wbk2 = Workbooks("Project Summary.xls")

If wbk1 Is Nothing Then
ChDir "I:\A&T Contracts\000 Utilities\"
Set wbk1 = Workbooks.Open("I:\A&T Contracts\000 Utilities\Project
Specific\Contract Docs\Integrated Utility Services\Export_Requests.csv")
ActiveWindow.Visible = False
End If

If wbk2 Is Nothing Then
ChDir "I:\A&T Contracts\000 Utilities\Project Specific\Contract
Docs\Integrated Utility Services\Option E\Payment\"
Set wbk4 = Workbooks.Open("I:\A&T Contracts\000 Utilities\Project
Specific\Contract Docs\Integrated Utility Services\Option E\Payment\Project
Summary.xls")
ActiveWindow.Visible = False
End If

End Sub

'*******

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Dim i As Integer
Dim sPath As String
Dim sFilename As String

Application.ScreenUpdating = False

Range("A1").Select

'Backup to Flashdrive
msg = "Do you want to save a Backup to your Flashdrive?"
Style = vbYesNo + vbInformation + vbDefaultButton2

Response = MsgBox(msg, Style)
If Response = vbYes Then

On Error Resume Next


Set fs = CreateObject("Scripting.FileSystemObject")
For i = 67 To 90
Set drspec = fs.GetDrive(Chr(i))
If drspec.drivetype = 1 Then
'MsgBox "Drive " & Chr(i) & " is the removable drive"
sPath = Chr(i) & ":\Work\Solutions Folder\IUS MONITOR\"
sFilename = "IUS Monitor " & _
Format(DateSerial(Year(Date), Month(Date), _
Day(Date)), "dd MM yy") & ".xls"

ans = MsgBox("Save File as " & sFilename & "?")
If ans = vbOK Then

'Check Path exists
If CreateObject("Scripting.FileSystemobject") _
.folderexists(sPath) = False Then
MkDir sPath
End If

ActiveWorkbook.SaveCopyAs sPath & sFilename
End If
i = 90
End If
Next i

Else
ActiveWorkbook.Activate
End If

Application.ScreenUpdating = True
End Sub

'Thanks
'Dylan

Have you looked thru your code for the word "visible"?
--
Jim Cone
Portland, Oregon USA



"DDawson"
wrote in message
I don't know what I've done to it. I have on open and before save events,
but I can't see the problem. I want the document to open visible.
Can you have a look and tell me if there's something I'm missing?
-snip-
'Thanks
'Dylan

Jim

Yeah, I just did a recheck in case there was something in the modules. The
only references to visible = false are contained in the OnOpen Event and
refer to two other workbooks called Export_Requests.csv and
Project Summary.xls", and then hides them.

Is there anything in the options that can set the document to hidden?

"Jim Cone" wrote:

Have you looked thru your code for the word "visible"?
--
Jim Cone
Portland, Oregon USA



"DDawson"
wrote in message
I don't know what I've done to it. I have on open and before save events,
but I can't see the problem. I want the document to open visible.
Can you have a look and tell me if there's something I'm missing?
-snip-
'Thanks
'Dylan

Comment out "On Error Resume Next" in the workbook open sub and see what happens.
--
Jim Cone
Portland, Oregon USA



"DDawson"
wrote in message
Jim
Yeah, I just did a recheck in case there was something in the modules. The
only references to visible = false are contained in the OnOpen Event and
refer to two other workbooks called Export_Requests.csv and
Project Summary.xls", and then hides them.
Is there anything in the options that can set the document to hidden?

"Jim Cone" wrote:
Have you looked thru your code for the word "visible"?
--
Jim Cone
Portland, Oregon USA



"DDawson"
wrote in message
I don't know what I've done to it. I have on open and before save events,
but I can't see the problem. I want the document to open visible.
Can you have a look and tell me if there's something I'm missing?
-snip-
'Thanks
'Dylan

Try removing the "on error resume next" statement.

If something goes wrong, that line will hide the error.

And the activewindow may not be what you think it is.



DDawson wrote:

I don't know what I've done to it. I have on open and before save events, but
I can't see the problem. I want the document to open visible.

Can you have a look and tell me if there's something I'm missing?

Private Sub Workbook_Open()
Dim wbk1 As Workbook
Dim wbk2 As Workbook

On Error Resume Next
Set wbk1 = Workbooks("Export_Requests.csv")
Set wbk2 = Workbooks("Project Summary.xls")

If wbk1 Is Nothing Then
ChDir "I:\A&T Contracts\000 Utilities\"
Set wbk1 = Workbooks.Open("I:\A&T Contracts\000 Utilities\Project
Specific\Contract Docs\Integrated Utility Services\Export_Requests.csv")
ActiveWindow.Visible = False
End If

If wbk2 Is Nothing Then
ChDir "I:\A&T Contracts\000 Utilities\Project Specific\Contract
Docs\Integrated Utility Services\Option E\Payment\"
Set wbk4 = Workbooks.Open("I:\A&T Contracts\000 Utilities\Project
Specific\Contract Docs\Integrated Utility Services\Option E\Payment\Project
Summary.xls")
ActiveWindow.Visible = False
End If

End Sub

'*******

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Dim i As Integer
Dim sPath As String
Dim sFilename As String

Application.ScreenUpdating = False

Range("A1").Select

'Backup to Flashdrive
msg = "Do you want to save a Backup to your Flashdrive?"
Style = vbYesNo + vbInformation + vbDefaultButton2

Response = MsgBox(msg, Style)
If Response = vbYes Then

On Error Resume Next


Set fs = CreateObject("Scripting.FileSystemObject")
For i = 67 To 90
Set drspec = fs.GetDrive(Chr(i))
If drspec.drivetype = 1 Then
'MsgBox "Drive " & Chr(i) & " is the removable drive"
sPath = Chr(i) & ":\Work\Solutions Folder\IUS MONITOR\"
sFilename = "IUS Monitor " & _
Format(DateSerial(Year(Date), Month(Date), _
Day(Date)), "dd MM yy") & ".xls"

ans = MsgBox("Save File as " & sFilename & "?")
If ans = vbOK Then

'Check Path exists
If CreateObject("Scripting.FileSystemobject") _
.folderexists(sPath) = False Then
MkDir sPath
End If

ActiveWorkbook.SaveCopyAs sPath & sFilename
End If
i = 90
End If
Next i

Else
ActiveWorkbook.Activate
End If

Application.ScreenUpdating = True
End Sub

'Thanks
'Dylan

--

Dave Peterson

Maybe better would be to leave in the On Error statement and add
the following immediately below the Set statements...

On Error GoTo 0
--
Jim Cone
Portland, Oregon USA

Thanks for the info guys.

I removed the on error message and now the line directly below it is
highlighted
Set wbk1 = Workbooks("Export_Requests.csv")

So I commented out those two lines as well
'Set wbk1 = Workbooks("Export_Requests.csv")
'Set wbk2 = Workbooks("Project Summary.xls")

Now the document opens without errors, but my main document is still hidden.
Another thing I notice is that the document appears twice in the VBA
Projects list on the left hand pane. Something isn't right?

"Dave Peterson" wrote:

Try removing the "on error resume next" statement.

If something goes wrong, that line will hide the error.

And the activewindow may not be what you think it is.



DDawson wrote:

I don't know what I've done to it. I have on open and before save events, but
I can't see the problem. I want the document to open visible.

Can you have a look and tell me if there's something I'm missing?

Private Sub Workbook_Open()
Dim wbk1 As Workbook
Dim wbk2 As Workbook

On Error Resume Next
Set wbk1 = Workbooks("Export_Requests.csv")
Set wbk2 = Workbooks("Project Summary.xls")

If wbk1 Is Nothing Then
ChDir "I:\A&T Contracts\000 Utilities\"
Set wbk1 = Workbooks.Open("I:\A&T Contracts\000 Utilities\Project
Specific\Contract Docs\Integrated Utility Services\Export_Requests.csv")
ActiveWindow.Visible = False
End If

If wbk2 Is Nothing Then
ChDir "I:\A&T Contracts\000 Utilities\Project Specific\Contract
Docs\Integrated Utility Services\Option E\Payment\"
Set wbk4 = Workbooks.Open("I:\A&T Contracts\000 Utilities\Project
Specific\Contract Docs\Integrated Utility Services\Option E\Payment\Project
Summary.xls")
ActiveWindow.Visible = False
End If

End Sub

'*******

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Dim i As Integer
Dim sPath As String
Dim sFilename As String

Application.ScreenUpdating = False

Range("A1").Select

'Backup to Flashdrive
msg = "Do you want to save a Backup to your Flashdrive?"
Style = vbYesNo + vbInformation + vbDefaultButton2

Response = MsgBox(msg, Style)
If Response = vbYes Then

On Error Resume Next


Set fs = CreateObject("Scripting.FileSystemObject")
For i = 67 To 90
Set drspec = fs.GetDrive(Chr(i))
If drspec.drivetype = 1 Then
'MsgBox "Drive " & Chr(i) & " is the removable drive"
sPath = Chr(i) & ":\Work\Solutions Folder\IUS MONITOR\"
sFilename = "IUS Monitor " & _
Format(DateSerial(Year(Date), Month(Date), _
Day(Date)), "dd MM yy") & ".xls"

ans = MsgBox("Save File as " & sFilename & "?")
If ans = vbOK Then

'Check Path exists
If CreateObject("Scripting.FileSystemobject") _
.folderexists(sPath) = False Then
MkDir sPath
End If

ActiveWorkbook.SaveCopyAs sPath & sFilename
End If
i = 90
End If
Next i

Else
ActiveWorkbook.Activate
End If

Application.ScreenUpdating = True
End Sub

'Thanks
'Dylan

--

Dave Peterson

You're using a variable named wbk4. That looks like an error.

I'd do something like:

Option Explicit
Private Sub Workbook_Open()

Dim wbk1 As Workbook
Dim wbk2 As Workbook

Set wbk1 = Nothing
Set wbk2 = Nothing

On Error Resume Next
Set wbk1 = Workbooks("Export_Requests.csv")
Set wbk2 = Workbooks("Project Summary.xls")
On Error GoTo 0

If wbk1 Is Nothing Then
On Error GoTo 0
Set wbk1 = Workbooks.Open _
("I:\A&T Contracts\000 Utilities\Project " _
& "Specific\Contract Docs\Integrated Utility Services\" _
& "Export_Requests.csv")
On Error GoTo 0

If wbk1 Is Nothing Then
MsgBox "wbk1 didn't open!"
Exit Sub
End If
wbk1.Windows(1).Visible = False
End If

If wbk2 Is Nothing Then
On Error Resume Next
Set wbk2 = Workbooks.Open _
("I:\A&T Contracts\000 Utilities\Project " _
& "Specific\Contract Docs\Integrated Utility Services\" _
& "Option E\Payment\Project Summary.xls ")
On Error GoTo 0
If wbk2 Is Nothing Then
MsgBox "wbk2 didn't open!"
Exit Sub
End If
wbk2.Windows(1).Visible = False
End If

End Sub

Untested, but it did compile.

Ps. Check the path names. I put them on separate lines to avoid line wrapping
problems, but the spaces may not be right!

DDawson wrote:

Thanks for the info guys.

I removed the on error message and now the line directly below it is
highlighted
Set wbk1 = Workbooks("Export_Requests.csv")

So I commented out those two lines as well
'Set wbk1 = Workbooks("Export_Requests.csv")
'Set wbk2 = Workbooks("Project Summary.xls")

Now the document opens without errors, but my main document is still hidden.
Another thing I notice is that the document appears twice in the VBA
Projects list on the left hand pane. Something isn't right?

"Dave Peterson" wrote:

Try removing the "on error resume next" statement.

If something goes wrong, that line will hide the error.

And the activewindow may not be what you think it is.



DDawson wrote:

I don't know what I've done to it. I have on open and before save events, but
I can't see the problem. I want the document to open visible.

Can you have a look and tell me if there's something I'm missing?

Private Sub Workbook_Open()
Dim wbk1 As Workbook
Dim wbk2 As Workbook

On Error Resume Next
Set wbk1 = Workbooks("Export_Requests.csv")
Set wbk2 = Workbooks("Project Summary.xls")

If wbk1 Is Nothing Then
ChDir "I:\A&T Contracts\000 Utilities\"
Set wbk1 = Workbooks.Open("I:\A&T Contracts\000 Utilities\Project
Specific\Contract Docs\Integrated Utility Services\Export_Requests.csv")
ActiveWindow.Visible = False
End If

If wbk2 Is Nothing Then
ChDir "I:\A&T Contracts\000 Utilities\Project Specific\Contract
Docs\Integrated Utility Services\Option E\Payment\"
Set wbk4 = Workbooks.Open("I:\A&T Contracts\000 Utilities\Project
Specific\Contract Docs\Integrated Utility Services\Option E\Payment\Project
Summary.xls")
ActiveWindow.Visible = False
End If

End Sub

'*******

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Dim i As Integer
Dim sPath As String
Dim sFilename As String

Application.ScreenUpdating = False

Range("A1").Select

'Backup to Flashdrive
msg = "Do you want to save a Backup to your Flashdrive?"
Style = vbYesNo + vbInformation + vbDefaultButton2

Response = MsgBox(msg, Style)
If Response = vbYes Then

On Error Resume Next


Set fs = CreateObject("Scripting.FileSystemObject")
For i = 67 To 90
Set drspec = fs.GetDrive(Chr(i))
If drspec.drivetype = 1 Then
'MsgBox "Drive " & Chr(i) & " is the removable drive"
sPath = Chr(i) & ":\Work\Solutions Folder\IUS MONITOR\"
sFilename = "IUS Monitor " & _
Format(DateSerial(Year(Date), Month(Date), _
Day(Date)), "dd MM yy") & ".xls"

ans = MsgBox("Save File as " & sFilename & "?")
If ans = vbOK Then

'Check Path exists
If CreateObject("Scripting.FileSystemobject") _
.folderexists(sPath) = False Then
MkDir sPath
End If

ActiveWorkbook.SaveCopyAs sPath & sFilename
End If
i = 90
End If
Next i

Else
ActiveWorkbook.Activate
End If

Application.ScreenUpdating = True
End Sub

'Thanks
'Dylan

--

Dave Peterson


--

Dave Peterson

Thanks Dave, you got it to work for me!
I simply had to amend the path names.

If you've got another moment:
I wonder what went wrong in my other version?
What does On Error GoTo 0 do?

Thanks again
from Dylan

"Dave Peterson" wrote:

You're using a variable named wbk4. That looks like an error.

I'd do something like:

Option Explicit
Private Sub Workbook_Open()

Dim wbk1 As Workbook
Dim wbk2 As Workbook

Set wbk1 = Nothing
Set wbk2 = Nothing

On Error Resume Next
Set wbk1 = Workbooks("Export_Requests.csv")
Set wbk2 = Workbooks("Project Summary.xls")
On Error GoTo 0

If wbk1 Is Nothing Then
On Error GoTo 0
Set wbk1 = Workbooks.Open _
("I:\A&T Contracts\000 Utilities\Project " _
& "Specific\Contract Docs\Integrated Utility Services\" _
& "Export_Requests.csv")
On Error GoTo 0

If wbk1 Is Nothing Then
MsgBox "wbk1 didn't open!"
Exit Sub
End If
wbk1.Windows(1).Visible = False
End If

If wbk2 Is Nothing Then
On Error Resume Next
Set wbk2 = Workbooks.Open _
("I:\A&T Contracts\000 Utilities\Project " _
& "Specific\Contract Docs\Integrated Utility Services\" _
& "Option E\Payment\Project Summary.xls ")
On Error GoTo 0
If wbk2 Is Nothing Then
MsgBox "wbk2 didn't open!"
Exit Sub
End If
wbk2.Windows(1).Visible = False
End If

End Sub

Untested, but it did compile.

Ps. Check the path names. I put them on separate lines to avoid line wrapping
problems, but the spaces may not be right!

I still think that you were hiding any errors by using "on error resume next".

On error resume next
tells excel/vba to ignore any error that happens.

So this is not something you want to turn on and leave on. Turn it on, do the
thing that you know may cause the error and turn it off.

And to turn it off, you can tell excel/vba to handle any error that occurs by
using "on error goto 0".

DDawson wrote:

Thanks Dave, you got it to work for me!
I simply had to amend the path names.

If you've got another moment:
I wonder what went wrong in my other version?
What does On Error GoTo 0 do?

Thanks again
from Dylan

"Dave Peterson" wrote:

You're using a variable named wbk4. That looks like an error.

I'd do something like:

Option Explicit
Private Sub Workbook_Open()

Dim wbk1 As Workbook
Dim wbk2 As Workbook

Set wbk1 = Nothing
Set wbk2 = Nothing

On Error Resume Next
Set wbk1 = Workbooks("Export_Requests.csv")
Set wbk2 = Workbooks("Project Summary.xls")
On Error GoTo 0

If wbk1 Is Nothing Then
On Error GoTo 0
Set wbk1 = Workbooks.Open _
("I:\A&T Contracts\000 Utilities\Project " _
& "Specific\Contract Docs\Integrated Utility Services\" _
& "Export_Requests.csv")
On Error GoTo 0

If wbk1 Is Nothing Then
MsgBox "wbk1 didn't open!"
Exit Sub
End If
wbk1.Windows(1).Visible = False
End If

If wbk2 Is Nothing Then
On Error Resume Next
Set wbk2 = Workbooks.Open _
("I:\A&T Contracts\000 Utilities\Project " _
& "Specific\Contract Docs\Integrated Utility Services\" _
& "Option E\Payment\Project Summary.xls ")
On Error GoTo 0
If wbk2 Is Nothing Then
MsgBox "wbk2 didn't open!"
Exit Sub
End If
wbk2.Windows(1).Visible = False
End If

End Sub

Untested, but it did compile.

Ps. Check the path names. I put them on separate lines to avoid line wrapping
problems, but the spaces may not be right!


--

Dave Peterson