Coding help on Macro

[Smoothstinger]

****** http-equiv="Content-Type" content="text/html; charset=utf-8">****** name="ProgId" content="Word.Document">****** name="Generator" content="Microsoft Word 11">****** name="Originator" content="Microsoft Word 11"><link rel="File-List" href="file:///C:%5CDOCUME%7E1%5CHOLTHA%7E1%5CLOCALS%7E1%5CTemp%5Cmsohtml1%5C01%5Cclip_filelist.xml"><!--[if gte mso 9]><xml> <w:WordDocument> <w:View>Normal</w:View> <w:Zoom>0</w:Zoom> <w:PunctuationKerning/> <w:ValidateAgainstSchemas/> <w:SaveIfXMLInvalid>false</w:SaveIfXMLInvalid> <w:IgnoreMixedContent>false</w:IgnoreMixedContent> <w:AlwaysShowPlaceholderText>false</w:AlwaysShowPlaceholderText> <w:Compatibility> <w:BreakWrappedTables/> <w:SnapToGridInCell/> <w:WrapTextWithPunct/> <w:UseAsianBreakRules/> <w:DontGrowAutofit/> </w:Compatibility> <w:BrowserLevel>MicrosoftInternetExplorer4</w:BrowserLevel> </w:WordDocument> </xml><![endif]--><!--[if gte mso 9]><xml> <w:LatentStyles DefLockedState="false" LatentStyleCount="156"> </w:LatentStyles> </xml><![endif]--><style> <!-- /* Style Definitions */ p.MsoNormal, li.MsoNormal, div.MsoNormal {mso-style-parent:""; margin:0in; margin-bottom:.0001pt; mso-pagination:widow-orphan; font-size:12.0pt; font-family:"Times New Roman"; mso-fareast-font-family:"Times New Roman";} @page Section1 {size:8.5in 11.0in; margin:1.0in 1.25in 1.0in 1.25in; mso-header-margin:.5in; mso-footer-margin:.5in; mso-paper-source:0;} div.Section1 {page:Section1;} --> </style><!--[if gte mso 10]> <style> /* Style Definitions */ table.MsoNormalTable {mso-style-name:"Table Normal"; mso-tstyle-rowband-size:0; mso-tstyle-colband-size:0; mso-style-noshow:yes; mso-style-parent:""; mso-padding-alt:0in 5.4pt 0in 5.4pt; mso-para-margin:0in; mso-para-margin-bottom:.0001pt; mso-pagination:widow-orphan; font-size:10.0pt; font-family:"Times New Roman"; mso-ansi-language:#0400; mso-fareast-language:#0400; mso-bidi-language:#0400;} </style> <![endif]--> I could use some help with a Macro that was developed for me a few weeks ago.
<o> </o>
Here is the situation. I have over 90 employees using a workbook I have developed containing 12 worksheets depicting each month of the year. When a monthly report is sent to me it only sends one sheet from the workbook to a folder in my “N”drive. In a separate workbook I am using a Macro that is extracting data from a cell (W38) from each sheet and adding them together for a total for that month. The code works great if I create the files from my computer, however I found one problem. When I asked a few of the employees to send me a test sheet I was unable to extract the information because it is coming from a different computer.
<o> </o>
Is there a way to change the code below to have said sheets sent to my “N” drive with the ability to extract the data I need that is coming from another computer?

Option Explicit
Sub SumC2s()
Dim MyDir As String, FN As String, MyTotal As Double
Application.ScreenUpdating = False

'********** Change the path to suit your environment **********
'MyDir = "C:\TestData\"

MyDir = "N:\Drive"


FN = Dir(MyDir & "\*.xls")
Do While FN <> ""
  If FN <> ThisWorkbook.Name Then
    With Workbooks.Open(MyDir & FN)
      With .Sheets(1)
        MyTotal = MyTotal + .Range("W38").Value
      End With
      .Close False
    End With
  End If
  FN = Dir


Loop
Range("A3") = MyTotal
Application.ScreenUpdating = False
End Sub

 

[sijpie]

What is the difference between a sheet sent from your PC or someone elses?

If you figure that out you probably solved your problem

 

[Joe4]

Do they have the same N drive mapping as you?

I would recommend using Uniform Naming Convention (UNC) instead of direct drive mappings. Then, it doesn't matter how their drives are mapped, the files will be sent to where you want them (provided that the users have access to this location).

Here is a link on what UNC is, if you are not familiar with it. This is what you actually enter in when you are mapping drives:
http://en.wikipedia.org/wiki/Path_(computing)#Uniform_Naming_Convention