Macro - Share with Users on a shared network drive
 

I wrote a macro in a workbook that refreshes data from an external file then
populates multiple spreadsheets. It is working very well for me at my
station, but won't run at other users' stations. The .CSV file that is being
read and imported in is in the same shared network folder where the workbook
is.

Why won't the macro run for the other users? Is there a security setting or
more code I need in my macro so they can run it.

Macro - Share with Users on a shared network drive
 

Without seeing your code it is hard to say. It could be security if the other
users do not have access to the file. More likely the other users might have
the drive mapped differently. Your "G" dirve might be different from other
users "G" drive. find out if the other users were to navigat to that file
what path would they follow.. There are remedies for this but lets see if
that is the problem first. Also... Post your code...
--
HTH...

Jim Thomlinson


"Vicki" wrote:

I wrote a macro in a workbook that refreshes data from an external file then
populates multiple spreadsheets. It is working very well for me at my
station, but won't run at other users' stations. The .CSV file that is being
read and imported in is in the same shared network folder where the workbook
is.

Why won't the macro run for the other users? Is there a security setting or
more code I need in my macro so they can run it.

Macro - Share with Users on a shared network drive
 

I do have the network directory path in my code, but I am now finding out
that some do not use the same letter. So far I have some where they used G,
R and L. Can I just use the whole path name? When she tries to run it, she
gets a message that the file can not be found.

Here is my code:
Sub ExtractCodes()
Dim ws1 As Worksheet
Dim wsNew As Worksheet
Dim rng As Range
Dim r As Integer
Dim c As Range
Set ws1 = Sheets("AllCrosswalkCodes")
Set rng = Range("CodesDatabase")
Dim N As Integer
Dim M As Integer
Dim FirstWSToSort As Integer
Dim LastWSToSort As Integer
Dim SortDescending As Boolean

'refresh all codes data from the most current RIMS text file
'Selection.QueryTable.Refresh BackgroundQuery:=False
With ActiveSheet.QueryTables.Add(Connection:= _
"TEXT;R:\IntegFinanceTeam\Crosswalk\crosswalkrepor t.csv",
Destination:=Range( _
"A2"))
.Name = "crosswalkreport_1"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.RefreshStyle = xlOverwriteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.TextFilePromptOnRefresh = False
.TextFilePlatform = 437
.TextFileStartRow = 1
.TextFileParseType = xlDelimited
.TextFileTextQualifier = xlTextQualifierDoubleQuote
.TextFileConsecutiveDelimiter = False
.TextFileTabDelimiter = True
.TextFileSemicolonDelimiter = False
.TextFileCommaDelimiter = True
.TextFileSpaceDelimiter = False
.TextFileColumnDataTypes = Array(2, 2, 2, 2, 2, 2, 3)
.TextFileTrailingMinusNumbers = True
.Refresh BackgroundQuery:=False
End With
Columns("A:G").Select
Columns("A:G").EntireColumn.AutoFit
Range("A2").Select
'extract a list of Modality Codes
ws1.Columns("A:A").Copy _
Destination:=Range("L1")
ws1.Columns("L:L").AdvancedFilter _
Action:=xlFilterCopy, _
CopyToRange:=Range("J1"), Unique:=True
r = Cells(Rows.Count, "J").End(xlUp).Row

'set up Criteria Area
Range("L1").Value = Range("A1").Value

For Each c In Range("J2:J" & r)
'add to the criteria area
ws1.Range("L2").Value = c.Value
'add new sheet (if required)
'and run advanced filter
If WksExists(c.Value) Then
Sheets(c.Value).Cells.Clear
rng.AdvancedFilter Action:=xlFilterCopy, _
CriteriaRange:=Sheets("AllCrosswalkCodes").Range(" L1:L2"), _
CopyToRange:=Sheets(c.Value).Range("A1"), _
Unique:=False
Columns("A:G").Select
Columns("A:G").EntireColumn.AutoFit
'Columns.AutoFit

Else
Set wsNew = Sheets.Add
wsNew.Move After:=Worksheets(Worksheets.Count)
wsNew.Name = c.Value
rng.AdvancedFilter Action:=xlFilterCopy, _
CriteriaRange:=Sheets("AllCrosswalkCodes").Range(" L1:L2"), _
CopyToRange:=wsNew.Range("A1"), _
Unique:=False
Columns("A:G").Select
Columns("A:G").EntireColumn.AutoFit
'Columns.AutoFit
End If
Next
ws1.Select
ws1.Columns("J:L").Delete
SortDescending = False

If ActiveWindow.SelectedSheets.Count = 1 Then
FirstWSToSort = 1
LastWSToSort = Worksheets.Count
Else
With ActiveWindow.SelectedSheets
For N = 2 To .Count
If .Item(N - 1).Index < .Item(N).Index - 1 Then
MsgBox "You cannot sort non-adjacent sheets"
Exit Sub
End If
Next N
FirstWSToSort = .Item(1).Index
LastWSToSort = .Item(.Count).Index
End With
End If

For M = FirstWSToSort To LastWSToSort
For N = M To LastWSToSort
If SortDescending = True Then
If UCase(Worksheets(N).Name) UCase(Worksheets(M).Name) Then
Worksheets(N).Move Befo=Worksheets(M)
End If
Else
If UCase(Worksheets(N).Name) < UCase(Worksheets(M).Name) Then
Worksheets(N).Move Befo=Worksheets(M)
End If
End If
Next N
Next M

End Sub
Function WksExists(wksName As String) As Boolean
On Error Resume Next
WksExists = CBool(Len(Worksheets(wksName).Name) 0)
End Function



"Jim Thomlinson" wrote:

Without seeing your code it is hard to say. It could be security if the other
users do not have access to the file. More likely the other users might have
the drive mapped differently. Your "G" dirve might be different from other
users "G" drive. find out if the other users were to navigat to that file
what path would they follow.. There are remedies for this but lets see if
that is the problem first. Also... Post your code...
--
HTH...

Jim Thomlinson


"Vicki" wrote:

I wrote a macro in a workbook that refreshes data from an external file then
populates multiple spreadsheets. It is working very well for me at my
station, but won't run at other users' stations. The .CSV file that is being
read and imported in is in the same shared network folder where the workbook
is.

Why won't the macro run for the other users? Is there a security setting or
more code I need in my macro so they can run it.

Macro - Share with Users on a shared network drive
 

I got it to work thanks to you. I don't know why I did not think of that
sooner. I just changed the directory path without the assigned letter since
it varies from user to user. Thank you so much for your time.

"Jim Thomlinson" wrote:

Without seeing your code it is hard to say. It could be security if the other
users do not have access to the file. More likely the other users might have
the drive mapped differently. Your "G" dirve might be different from other
users "G" drive. find out if the other users were to navigat to that file
what path would they follow.. There are remedies for this but lets see if
that is the problem first. Also... Post your code...
--
HTH...

Jim Thomlinson


"Vicki" wrote:

I wrote a macro in a workbook that refreshes data from an external file then
populates multiple spreadsheets. It is working very well for me at my
station, but won't run at other users' stations. The .CSV file that is being
read and imported in is in the same shared network folder where the workbook
is.

Why won't the macro run for the other users? Is there a security setting or
more code I need in my macro so they can run it.

Macro - Share with Users on a shared network drive
 

I believe I have fixed it THANKS TO YOU! I just changed the network path
director in place of the specific letter assigned to the network path. This
accomodates all who have different letters assigned. I can't believe I did
not think of that sooner. It was just tested by two different users and it
refreshed wonderfully.

Thank you for your time.

"Jim Thomlinson" wrote:

Without seeing your code it is hard to say. It could be security if the other
users do not have access to the file. More likely the other users might have
the drive mapped differently. Your "G" dirve might be different from other
users "G" drive. find out if the other users were to navigat to that file
what path would they follow.. There are remedies for this but lets see if
that is the problem first. Also... Post your code...
--
HTH...

Jim Thomlinson


"Vicki" wrote:

I wrote a macro in a workbook that refreshes data from an external file then
populates multiple spreadsheets. It is working very well for me at my
station, but won't run at other users' stations. The .CSV file that is being
read and imported in is in the same shared network folder where the workbook
is.

Why won't the macro run for the other users? Is there a security setting or
more code I need in my macro so they can run it.