Multiple people using workbook not getting same results

[allison]

In the workbook, I've created a macro that warehouses the necessary datapoints to a log that is on our network. It works great when I run the process from my computer. I've had 2 others try it (one in the same office as myself, and one that is a remote user). When they run the process, nothing gets written to the log that is on our network. We are all mapped the same way, so I don't think that the problem is the where the log is stored (but it might be?).

I have the process written in Module1. I do not have it notated at Option Explicit or anything like that. Do I need to?

Any suggestions would be greatly appreciated!!

Thanks!!

[Bob Phillips]

the code?

[allison]

Sub LogReview()

Dim DestBook As Workbook, SrcBook As Workbook
Dim FinalRow As Long


Application.ScreenUpdating = False
Set SrcBook = ThisWorkbook

On Error Resume Next
Set DestBook = Workbooks.Open("L:\~Claims Operations\File Review\Log.xls")
If Err.Number = 1004 Then

Set DestBook = Workbooks.Add
End If

With DestBook.Worksheets(1)

FinalRow = .Range("A" & .Rows.Count).End(xlUp).Row

SrcBook.Worksheets(2).Range("Name").Copy DestBook.Worksheets(1).Range("A" & FinalRow + 1)
SrcBook.Worksheets(2).Range("Mgr").Copy DestBook.Worksheets(1).Range("B" & FinalRow + 1)
SrcBook.Worksheets(2).Range("Clm_Num").Copy DestBook.Worksheets(1).Range("C" & FinalRow + 1)
SrcBook.Worksheets(2).Range("DOR").Copy DestBook.Worksheets(1).Range("D" & FinalRow + 1)
SrcBook.Worksheets(2).Range("Overall_Score").Copy DestBook.Worksheets(1).Range("E" & FinalRow + 1)
SrcBook.Worksheets(2).Range("Coverage").Copy DestBook.Worksheets(1).Range("F" & FinalRow + 1)
SrcBook.Worksheets(2).Range("Investigation").Copy DestBook.Worksheets(1).Range("G" & FinalRow + 1)
SrcBook.Worksheets(2).Range("Communication").Copy DestBook.Worksheets(1).Range("H" & FinalRow + 1)
SrcBook.Worksheets(2).Range("Evaluation").Copy DestBook.Worksheets(1).Range("I" & FinalRow + 1)
SrcBook.Worksheets(2).Range("Damage_Assessment").Copy DestBook.Worksheets(1).Range("J" & FinalRow + 1)
SrcBook.Worksheets(2).Range("Vendor_Mgmt").Copy DestBook.Worksheets(1).Range("K" & FinalRow + 1)
SrcBook.Worksheets(2).Range("Negotiation_Direction").Copy DestBook.Worksheets(1).Range("L" & FinalRow + 1)
SrcBook.Worksheets(2).Range("Contact_24Hrs").Copy DestBook.Worksheets(1).Range("M" & FinalRow + 1)
SrcBook.Worksheets(2).Range("Documentation").Copy DestBook.Worksheets(1).Range("N" & FinalRow + 1)
SrcBook.Worksheets(2).Range("Data_Integrity").Copy DestBook.Worksheets(1).Range("O" & FinalRow + 1)
Range("A2:O" & FinalRow + 1).Select

Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Selection.Font.ColorIndex = 0
Selection.Interior.ColorIndex = xlNone
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
Selection.Borders(xlEdgeLeft).LineStyle = xlNone
Selection.Borders(xlEdgeTop).LineStyle = xlNone
Selection.Borders(xlEdgeBottom).LineStyle = xlNone
Selection.Borders(xlEdgeRight).LineStyle = xlNone
Selection.Borders(xlInsideVertical).LineStyle = xlNone
Selection.Borders(xlInsideHorizontal).LineStyle = xlNone
Selection.Font.Bold = False

DestBook.Save
DestBook.Close
End With

On Error GoTo 0
Set DestBook = Nothing
Set SrcBook = Nothing

Application.ScreenUpdating = True

End Sub

[Bob Phillips]

The only thing that jumps out at me is tat this ine

[vba]

Range("A2:O" & FinalRow + 1).Select
[/vba]

should be

[vba]

.Range("A2:O" & FinalRow + 1).Select
[/vba]

[allison]

I fixed that line - thanks for pointing it out.

Unfortunately, it did not solve the problem. I find it odd that it works from my workstation but from someone else's workstation, it does not save to the log.

[Bob Phillips]

are you sure that they have drive L mapped?

[allison]

Yes, they are all mapped to the L: drive (it's our departmental drive) - but I would not bet my life that the mappings are identical. I posed a question to our network team regarding that because that is the only thing I can think of that would cause it to not work the same for them as it does for me.

[Bob Phillips]

Go on their machines and enter

?dir("L:\~Claims Operations\File Review\Log.xls")<>""

in the VBIDE immediate window, and see what it says (do it on yours first, it should say True for you).

[allison]

I'm going to have to show my ignorance again....the VBIDE immediate window?

[Bob Phillips]

Go inti the VBIDE (Alt-F11), open the immediate window (Ctrl-G), and you then enter commands directly, like the Dir command. ? is the immediate equivalent of Debug.Print

[mdmackillop]

Create a message box to show if the log file is opening. If not (as I suspect), your code creates a new workbook and saves to your default location, but it has no link to the L drive.
Add the second line here
[vba]
DestBook.Save
MsgBox DestBook.FullName

[/vba]

[RichardSchollar]

Also you have truned error reporting off with On Error Resume Next but then don't turn it back on again until nearly the end of your code. I don't think you want to do this because you lose an important source of debugging information. You should verify the presence of the file using eg Dir (as Xld showed you) and only make use of On Error Resume Next in restricted circumstances.

Richard

[allison]

Richard - I'm really a newbie at this and am learning as I go. I understand what you and Xld having mentioned - but not sure how to incorporate it into the code. Should I take out the On Error Resume Next and On Error Go To 0 lines?

[Bob Phillips]

Reset it immediately you are done with it

[vba]

On Error Resume Next
Set DestBook = Workbooks.Open("L:\~Claims Operations\File Review\Log.xls")
If Err.Number = 1004 Then

Set DestBook = Workbooks.Add
End If
On Error GoTo 0
[/vba]

[allison]

gotcha! Thanks much for all the help.

[mdmackillop]

Try something like
[VBA]
Dim Test As String
Dim TestBook As Workbook
Dim AddFile As Integer
Test = Dir("L:\~Claims Operations\File Review\Log.xls")
If Len(Test) = 0 Then
AddFile = MsgBox("File not found. Create new log file?", vbYesNo)
If AddFile = vbYes Then
Set destbook = Workbooks.Add
destbook.SaveAs "L:\~Claims Operations\File Review\Log.xls"
End If
Else
Set destbook = Workbooks.Open("L:\~Claims Operations\File Review\Log.xls")
End If

[/VBA]