Export To Excel

[Pam]

Hi,

I have the following for a command button on a form that opens when a report
is selected. I have a user who can't get this to work. There is no error
message, his cursor just sits. It works fine for myself and other users.
I've tried his login on my computer and it still doesn't work.

Private Sub Command10_Click()

Dim strFolderAndFile As String
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9,
"qStatusDayCountAllJobs", "C:\DELTADB\AllJobs.xls"
Dim oApp As Object

Set oApp = CreateObject("Excel.Application")
oApp.Workbooks.Open "C:\deltadb\AllJobs.xls"
oApp.Visible = True

End Sub

Does anyone have any suggestions as to why it won't work for just this one
user's login?

Thanks,
Pam

 

[Phil Smith]

Hi,

I have the following for a command button on a form that opens when a report
is selected. I have a user who can't get this to work. There is no error
message, his cursor just sits. It works fine for myself and other users.
I've tried his login on my computer and it still doesn't work.

Private Sub Command10_Click()

Dim strFolderAndFile As String
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9,
"qStatusDayCountAllJobs", "C:\DELTADB\AllJobs.xls"
Dim oApp As Object

Set oApp = CreateObject("Excel.Application")
oApp.Workbooks.Open "C:\deltadb\AllJobs.xls"
oApp.Visible = True

End Sub

Does anyone have any suggestions as to why it won't work for just this one
user's login?

Thanks,
Pam

Any chance that his login is somehow restricted from writing to that
drive or directory?
Any chance that his login automatically opens that spreadsheet so it
can't be written to?
I don't know what kind of login you are using, but I would look closely
at the permission given to this account, and at any scripts that run for
that user.

 

[Pam]

Phil,

Thanks for replying. I've checked permissions for user and for any code
restricting his use for this particular event. He has the same permissions
as another Full Data User who can open and run the report.

Do you have any other suggestions I may be able to try?

Thanks,
Pam

 

[Phil Smith]

Are these people using full on workstations with Office fully loaded, or
some kind of remote solution which may not be running the same version
of access that the rest are? Are you sure it is the login not the
workstation that person is using where the problem is?

Actually, one idea does come to mind. I was assuming that your transfer
was failing, but maybe not...

Open up task manager before starting this macro. Verify that excel is
NOT running. Then launch the macro. At the point where it hangs, is
excel loaded according to the task manager? It may be loading, but not
visible, and then hanging the system for some reason. If so, you will
know where to look at least. Look for CPU activity to see if excel is
doing something or stops. Try removing the code to open Excel, and
opening that spreadsheet manually afterwards to see if it is OK.

Open the VB editor, and single step through the code for you, to get an
idea how long each step takes, then step through as the problem user,
and see which command it fails on.

Not solutions per se, but ideas to at least narrow the problem down..

Phil

 

[Pam]

Phil,

Thanks for the info. I don't know what happened, but I logged on as the
user with the problem and it worked as it should. What I could not
understand was why with my login it worked on my computer, but with his
login on his computer and my computer, it wouldn't. We all have full Office
and I checked to make sure we all had the same version. I will have him try
on his computer once he returns and hopefully it will work and continue to
do so.

I'll keep your suggestions in mind should I run into this again.
Thanks for your time and help.
Pam