Writing to Excel from Access - strange problem....

  • Thread starter Thread starter rs0905
  • Start date Start date
R

rs0905

Here's the mystery:

I have code that creates a series of worksheets in an Excel Workbook, one
for each of a team of employees. The code typically creates 5-6 worksheets
for employees that are specified by the db user. For about 1/2 of the db
users, this process works without a hitch. For the other half of the users,
it overwrites the same worksheet over and over instead of creating new ones
for each person, so they are left with only one worksheet for the last person
on their list. I am not in the office, but the IT support at the office has
confirmed that they are all running the same version of MS Office. Here is
the code that creates the worksheets (this is within a Do Loop and repeats
for each employee specified):

Dim xlBook, xlSheet, xlSheet2

Set xlBook = appExcel.Workbooks.Open(STORE_PATH & "store.xls", , True)
Set xlSheet = xlBook.Worksheets(1)
Set xlSheet2 = xlBook.Worksheets("template")

' ' ' the above is set before the loop. the following is within the loop '
' '

xlSheet2.Activate
xlSheet2.Copy Before:=Worksheets("Template")
Set xlSheet = ActiveWorkbook.Worksheets("Template (2)") ' ' ' this is the
copy ' ' '
xlSheet.Activate
xlSheet.Name = strCounselor

Does anyone know why this would work for some users but not for others??
 
Oddly enough, this is what I had originally, and it didn't work for any of
the users (i.e., all of them were having the overwrite problem). The code
debugs fine, but it is just weird that this works for some users but not for
others. If it works for some, I figure that the code must be ok, otherwise
it wouldn't work for anyone. Are you aware of any add-ons or differences in
Office or Access setups that would affect how Access and Excel communicate
with one another? It's the only thing that I can figure that would be
different between the users.

Thanks!
 
Back
Top