Transfer spreadsheet problem 3

[48Highlander]

I am tying to import data from an Excel spreadsheet and my code works if the spreadhseet is closed and also if it is open with the following exception: if the spreadsheet is in edit mode, Access goes into some sort of infinite loop. I read somewhere that the Transfer Spreadsheet method waits until the spreadhseet is no longer in edit mode.

How can I use Access code to determine if the spreadhseet is open in edit mode? If it is not possible to detect if it is in edit mode, how can I detect if the specific spreadhseet is open?

Bill J

 

[TheAceMan1]

How are ya 48Highlander . . .

Perhaps this will help: Determining if Excel is in Edit mode

[blue]Your Thoughts? . . .[/blue]

See Ya! . . . . . .

Be sure to see faq219-2884 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[kjv1611]

Another way that probably seems overkill, but should be accurate, I guess, would be to use the Excel Application object and workbook object to determine whether a workbook is open. If it is open, save and close it, then go back and reopen it after you finish your transferspreadsheet.

I'm sure it's not the ideal solution, but I would think it would work. Never tried it, but it seems that it would work.

So, to do that, you have to first set a reference to the Excel Library (Tools - References, check the box next to Microsoft Excel xx.x Object Library), then create the Excel Application and Excel Workbook objects... and then loop through any "ActiveWorkook" objects, and close them... maybe saving the names/paths in an Array variable, so you can reopen whatever was closed when finished.

Another way, I would think, would be to look at the Window handles with Shell, and see if anything with "*.xls*" is open, perhaps.. same thing - then close that application, reopen whatever is current/previously open.

This is all theory on my part, as I've not needed to do this so far to date.

Is it always the same person who would be running the database and have the Workbook open at the same time? Is there ever a possibility that PersonA will have the workbook open while PersonB will be using the database?

 

[PHV]

Have a look here:

http://www.xcelfiles.com/IsFileOpenVBA.htm

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[kjv1611]

Thanks for posting that link, PHV. That's something I've been curious about doing myself, but never took the time to look into - not only finding if a file is open, but who last had it open. That would REALLY come in handy at times.

 

[48Highlander]

Thanks AceMan and PHV. I wasn't able to get back to this until now. I have encountered this problem before and just ignored it. But now I can ensure my app doesn't crash. THanks again.

Bill J

 

[Knicks]

I just capture the error number when outputting to Excel and act accordingly. You just put in your error coding

Exit_Command125_err:
Exit Sub

Command125_err:


If Err.Number = 2302 Then
MsgBox ("Excel File is Already Open from a Previous Run!!" & vbCrLf & "Close Excel File and Rerun")
Err.Clear
Resume Next
Else
MsgBox (Err.Number & " " & Err.Description)
End If