PDA

View Full Version : Multiple people using workbook not getting same results



allison
02-18-2008, 09:39 AM
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
02-18-2008, 09:49 AM
the code?

allison
02-18-2008, 11:11 AM
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
02-18-2008, 12:39 PM
The only thing that jumps out at me is tat this ine



Range("A2:O" & FinalRow + 1).Select


should be



.Range("A2:O" & FinalRow + 1).Select

allison
02-18-2008, 01:41 PM
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
02-18-2008, 02:07 PM
are you sure that they have drive L mapped?

allison
02-18-2008, 02:34 PM
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
02-18-2008, 02:52 PM
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
02-18-2008, 03:03 PM
I'm going to have to show my ignorance again....the VBIDE immediate window?

Bob Phillips
02-18-2008, 03:39 PM
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
02-18-2008, 05:19 PM
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

DestBook.Save
MsgBox DestBook.FullName

RichardSchollar
02-19-2008, 06:51 AM
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
02-19-2008, 08:18 AM
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
02-19-2008, 08:37 AM
Reset it immediately you are done with it



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

allison
02-19-2008, 08:45 AM
gotcha! Thanks much for all the help.

mdmackillop
02-19-2008, 08:50 AM
Try something like

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