|
I have written a simple application for user that pulls information from another workbook. When my workbook is open, the source worksheet cannot be accessed, but when I shut my workbook down, the source workbook can be accessed. This was written using office 2003 but was saved in a 97 to 2003 format and the user is using windows xp. This is happening on the user's computer but not on my own which is on the same network. | | Derek at Potters Clay | Derek, there could be several reasons you're experiencing technical difficulties. Too clear things up, could you post the VBA Code here for analysation.
Thanks,
halfazner =]
| | halfazner | Sub populate()
'total reps in team a at cell 2,9
'total reps in team b at cell 2,10
Dim repsa As Integer
Dim repsb As Integer
Dim monthcaps As String
Dim monthproper As String
Dim monthint As Integer
'monthint = Workbooks("Team Competition").Worksheets("Main").Cells(2, 12)
monthint = Worksheets("Main").Cells(2, 12)
Select Case monthint
Case 1
monthcaps = "JANUARY"
monthproper = "January"
Case 2
monthcaps = "FEBRUARY"
monthproper = "February"
Case 3
monthcaps = "MARCH"
monthproper = "March"
Case 4
monthcaps = "APRIL"
monthproper = "April"
Case 5
monthcaps = "MAY"
monthproper = "May"
Case 6
monthcaps = "JUNE"
monthproper = "June"
Case 7
monthcaps = "JULY"
monthproper = "July"
Case 8
monthcaps = "AUGUST"
monthproper = "August"
Case 9
monthcaps = "SEPTEMBER"
monthproper = "September"
Case 10
monthcaps = "OCTOBER"
monthproper = "October"
Case 11
monthcaps = "NOVEMBER"
monthproper = "November"
Case 12
monthcaps = "DECEMBER"
monthproper = "December"
End Select
Dim tempstring As String
Dim yearint As Integer
Dim dayint As Integer
' yearint = Workbooks("Team Competition").Worksheets("Main").Cells(2, 14)
' dayint = Workbooks("Team Competition").Worksheets("Main").Cells(2, 13)
' repsa = Workbooks("Team Competition").Worksheets("Main").Cells(2, 10)
' repsb = Workbooks("Team Competition").Worksheets("Main").Cells(2, 11)
yearint = Worksheets("Main").Cells(2, 14)
dayint = Worksheets("Main").Cells(2, 13)
repsa = Worksheets("Main").Cells(2, 10)
repsb = Worksheets("Main").Cells(2, 11)
Dim tempint As Integer
For i = 5 To (repsa + 5)
' tempint = ((Workbooks("Team Competition").Worksheets("Main").Cells(i, 2)) + 3)
tempint = (Worksheets("Main").Cells(i, 2)) + 3
tempstring = "='G:\Customer Service\Daily Sales Tracking Sheets\" & monthcaps & " " & yearint & " Sales Tracking\Kumfer Team\[" & monthcaps
tempstring = tempstring & " Kumfer Issued Sales Roll Up.xls]" & monthint & "-" & dayint & "'!$Q$" & tempint
Worksheets("Main").Cells(i, 4) = tempstring
'Workbooks("Team Competition").Worksheets("Main").Cells(i, 4) = tempstring
Next i
' With Workbooks("Team Competition").Worksheets("Main").Range("b3:d30").Interior
With Worksheets("Main").Range("b3:d30").Interior
.ColorIndex = 38
.Pattern = xlSolid
End With
' With Workbooks("Team Competition").Worksheets("Main").Range("f3:h30").Interior
With Worksheets("Main").Range("f3:h30").Interior
.ColorIndex = 33
.Pattern = xlSolid
End With
For j = 5 To (repsa + 5)
tempint = Worksheets("Main").Cells(j, 6) + 3
' tempint = ((Workbooks("Team Competition").Worksheets("Main").Cells(j, 6)) + 3)
tempstring = "='G:\Customer Service\Daily Sales Tracking Sheets\" & monthcaps & " " & yearint & " Sales Tracking\Kumfer Team\[" & monthcaps
tempstring = tempstring & " Kumfer Issued Sales Roll Up.xls]" & monthint & "-" & dayint & "'!$Q$" & ((Worksheets("Main").Cells(j, 6)) + 3)
' tempstring = tempstring & " Kumfer Issued Sales Roll Up.xls]" & monthint & "-" & dayint & "'!$Q$" & ((Workbooks("Team Competition").Worksheets("Main").Cells(j, 6)) + 3)
Worksheets("Main").Cells(j, 8) = tempstring
' Workbooks("Team Competition").Worksheets("Main").Cells(j, 8) = tempstring
Next j
' Workbooks("Team Competition").Worksheets("Main").Cells(1, 2) = monthproper & " " & dayint & ", " & yearint & " Team Competition"
Worksheets("Main").Cells(1, 2) = monthproper & " " & dayint & ", " & yearint & " Team Competition"
'='G:\Customer Service\Daily Sales Tracking Sheets\AUGUST 2006 Sales Tracking\Kumfer Team\[AUGUST Kumfer Issued Sales Roll Up.xls]8-15'!$Q$4
End Sub | | Derek at Potters Clay | | Could security on the program cause an issue due to macros? | | Derek at Potters Clay |
|
index > Visual Basic for Applications (VBA) > VBA script not accessing another workbook