You may be right, June7. Thanks for looking at it.
You may be right, June7. Thanks for looking at it.
I've tested your code on 2 workstations in both Access 365 (32/64 bit) & Access 2010 (32-bit)
Same result in both cases
When first run, the Excel file flashes in the taskbar (as you get). However when the same or a different Excel file is then opened, it takes focus successfully
Reopening the Access file, causes the above behaviour again
Adding the xlapp.ActiveWindow.Activate line has no effect on the above.
Some of the code is I think superfluous but removing it hasn't changed the behaviour:
If not already fixed, I'll look up my own code later today but it probably won't be for a few hours.Code:Public Function fOpenWorkbook(pstrPath As String) As Boolean'Dim lboolOpenWorkbook As Boolean Set xlapp = Excel.Application Set wbWorkbook = xlapp.Workbooks.Open(pstrPath) xlapp.Visible = True xlapp.WindowState = xlMaximized xlapp.ActiveWindow.Activate 'lboolOpenWorkbook = True ExitOpenWorkbook: fOpenWorkbook = False 'lboolOpenWorkbook Exit Function ErrOpenWorkbook: MsgBox Err.Description, vbInformation, "Error in fOpenWorkbook" Resume ExitOpenWorkbook End Function
In the meantime, did you try the SampleFileViewer app.
I did try SampleFileViewer. Still playing with it.
Several other observations with my app.
- If, after opening the app but before attempting to open an excel file through it, I open the vba coding window and then close it, the excel file then displays on the screen like it should . I don't have to make any change to the code, just open and close the VBA screen.
- Tried it on more orthodox databases, with no user-defined properties in the database, and also with a table as a source for the form. No change.It is though I need something to bring the excel window to the front after it opens.
It seems as though I need something to bring the excel window to the front after it opens.
A couple of observations on your app from me.
1. The error handling in the procedures I looked at wasn't functional. It should be something like:
You have omitted the On Error GoTo ... line so errors will not be properly hanledCode:Function FunctionName() On Error GoTo Err_Handler .... your code here Exit_Handler: Exit Function Err_Handler: MsgBox Err & " " & err.description & " in FunctionName procedure",vbExclamation, "Error" Resume Exit_Hand;er End Function
2. The code is unnecessarily complex and it may be that which causes the issue you have
The file selection code could be dramatically simplified using FileSystemObject code
The file open code would be much simpler if you used GetObject/CreateObject. Nothing else is needed
3. I recommend you change to using late binding. Doing so means the version specific Excel reference can be removed
I intentionally left off the on error goto so that I could better debug the code.
I will try your suggestions. They will allow me to eliminate the Office and Excel references.
Omitting the OnError line MAY have helped you but not any of us testing it!
Example late binding code for opening Excel file:
I suspect if you use that and FSO code instead of everything you have now, the problem will be solved. Good luck!Code:Dim objXL As Object Dim objWB As Object On Error Resume Next Set objXL = GetObject(,"Excel.Application") On Error GoTo 0 If objXL Is Nothing Then Set objXL = CreateObject("Excel.Application") objXL.Visible =True End If Set objWB = objXL.Workbooks.Open(Me.txtFileName) 'replace with your textbox name from the form
NOTE: The code editor played havoc with what I pasted in, removing spaces from most lines - hopefully now restored!
How do I get a file dialog box using the FSO?
I still cannot replicate issue. I open db and click buttons on form, select Excel file, open Excel file - file is visible.
You already have code using FSO.
How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.
I eliminated the references to Excel and Office libraries. I set fDialog = Application.FileDialog(msoFileDialogFilePicker) with msoFileDialogFilePicker defined as a constant = 3 within the module, so the only libraries in use are those that are present when a database is newly created. I also cleaned up some of the code.
Still exhibits same behavior. I can live with it. However, if anyone runs across someone else complaining of this behavior, I would be interested.
TestOpenExcelFileLateBinding.zip
I was able to replicate the behaviour once so far. I was stepping through the code so I'm not sure if that is relevant. Will play around some more.
EDIT - is there a reason why you have the workbook code in a standard module? If there is only going to be 1 call to the function per button click, then my first thought would be to keep it all in the click event and see if it makes a difference. There might be a timing issue between the calls in the stack - button click, then 'remote' function, then back to the click event.
EDIT2 - by any chance does this only happen to you if you are working with the vbe (code editor) window i.e. stepping through the code?
The application that I posted was distilled out of two larger applications that I use daily in my work and in which I encountered the problem. The modules in the posted app were adapted from modules in those larger apps. I use mdlOfficeFileDialogMod in its original form in several of my apps that I have developed to assist me in my work. I try to arrange my vba modules so that I can reuse them as needed in different apps without creating conflicts. In fact, I just divided one of the modules into two in one of the aforementioned apps so that I can port one of them, which contains key functionality for the app, into another that I have used for some time, thus eliminating the need for the separate app. My goal is to eventually develop these apps into something that both I and my colleague can use in our work.
The app that I posted basically was one I especially created for this discussion, only containing the essential functionality pertaining to the question asked. Throughout my career, when I have encountered problems with software, I have tried to develop test platforms where only the functionality in question is demonstrated. Besides ruling out other factors, it makes it simpler to analyze the problem, whether it be the result of a deficiency in the microcode on the computer, or a bug in the software used to run the established where I worked before I retired.
The answer to your second question is that I encountered the problem when not working in the VBE. In fact, in my hands, opening and then closing the VBE prior to opening the file seems to solve the problem.
Then I'm afraid I've run into a wall. Obviously we don't have the same environment variables such as version of Office/Access, network structure or permissions, hardware, etc. so it's probably not something we can pinpoint given that 3 (?) of us can run it with no issues. The only time I can come close is if I'm following code in the vbe. My last kick at the can would be to suggest an API to try to activate an application window, assuming your users can't just live with clicking on the task bar. Who knows, you might find that if you take that approach, the offending factor might be removed on some sort of update. An example of an API:
https://www.everythingaccess.com/tut...the-foreground
The more we hear silence, the more we begin to think about our value in this universe.
Paraphrase of Professor Brian Cox.
Since I am the only user, I'll live with it. Thanks for your help.
Incidentally, I try to avoid APIs these days, since they have to be coded for both 32 bit and 64 bit systems. I know how to use them, having been through the exercise several years ago with an app I wrote that was used where I worked for over 10 years. I had to port it to Access 7 and have it work on both types of systems. The API calls are often different and take some research to find which ones are applicable to 64 bit systems. Right now, the only App I use with an API call is the one that lists a database's properties and it is coded for both (#IF WIN64 Then...#Else...#End If).
NP. Good luck going forward.
The more we hear silence, the more we begin to think about our value in this universe.
Paraphrase of Professor Brian Cox.