VBA script not accessing another workbook  
Author Message
Derek at Potters Clay





PostPosted: Visual Basic for Applications (VBA), VBA script not accessing another workbook Top

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.


Microsoft ISV Community Center Forums1  
 
 
halfazner





PostPosted: Visual Basic for Applications (VBA), VBA script not accessing another workbook Top

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 =]

 
 
Derek at Potters Clay





PostPosted: Visual Basic for Applications (VBA), VBA script not accessing another workbook Top

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





PostPosted: Visual Basic for Applications (VBA), VBA script not accessing another workbook Top

Could security on the program cause an issue due to macros