opening excel file in separate windows

A

Alex

Could anybody advise why when I run the function the excel
file is being opened in one window on my computer but on
another computer it's opend in separate windows with every
function's run. How could I avoid the opening in separate
windows.

Thanks

Function fnRecipes(Var_ProductComp, Var_RecipeNum)
On Error GoTo fnRecipes_Err

Dim xlApp As Excel.Application

Dim xlBook As Excel.Workbook
Dim xlSheet As Excel.Worksheet
Dim strRecipeNum As String, strFileName As String, _
logFileIsOpened As Boolean
Dim strProductComp As String
Dim logGetExcel As Boolean

strProductComp = Var_ProductComp
strRecipeNum = Var_RecipeNum

Screen.MousePointer = 11

strFileName = "FileName"
logFileIsOpend = False


If Not IsNull(strProductComp) Then
Set xlApp = CreateObject("Excel.Application")
DoCmd.SetWarnings False
xlApp.DisplayAlerts = False
xlApp.AskToUpdateLinks = False

logFileIsOpened = FileLocked(strFileName)

If logFileIsOpened = True Then

logGetExcel = GetExcel(strFileName, strRecipeNum)

Else

Set xlBook = xlApp.Workbooks.Open(strFileName)
Set xlSheet = xlBook.Worksheets(strRecipeNum)
xlSheet.Activate
DoCmd.SetWarnings True
xlApp.DisplayAlerts = True

xlApp.AskToUpdateLinks = True
xlApp.Visible = True
xlSheet.Visible = xlSheetVisible
End If


End If
Screen.MousePointer = 1
'xlApp.Quit

fnRecipes_exit:
'xlApp.Quit

Exit Function
fnRecipes_Err:
Screen.MousePointer = 1
DoCmd.SetWarnings True
DoCmd.SetWarnings True
xlApp.DisplayAlerts = True
xlApp.AskToUpdateLinks = True
'''''''''''Insert checking whether the file is
open'''''''''''''''''''
logFileIsOpend = FileLocked(strFileName)

If logFileIsOpened = True Then
xlApp.Quit
End If
MsgBox Err.Description
Resume fnRecipes_exit

End Function

Option Compare Database

' Declare necessary API routines:
Declare Function FindWindow Lib "user32" Alias _
"FindWindowA" (ByVal lpClassName As String, _
ByVal lpWindowName As Long) As Long

Declare Function SendMessage Lib "user32" Alias _
"SendMessageA" (ByVal hWnd As Long, ByVal wMsg As Long, _
ByVal wParam As Long, _
ByVal lParam As Long) As Long

Function GetExcel(Var_FileName, Var_RecipeNumber)
Dim MyXL As Object ' Variable to hold reference
' to Microsoft Excel.
Dim xlBook As Excel.Workbook
Dim xlSheet As Excel.Worksheet

Dim ExcelWasNotRunning As Boolean ' Flag for final
release.

Dim strFileName As String, strRecipeNumber As String
' Test to see if there is a copy of Microsoft Excel
already running.
On Error Resume Next ' Defer error trapping.
' Getobject function called without the first argument
returns a
' reference to an instance of the application. If the
application isn't
' running, an error occurs.

Set MyXL = GetObject(, "Excel.Application")
If Err.Number <> 0 Then ExcelWasNotRunning = True
Err.Clear ' Clear Err object in case error occurred.

' Check for Microsoft Excel. If Microsoft Excel is running,
' enter it into the Running Object table.
DetectExcel

strFileName = Var_FileName
strRecipeNumber = Var_RecipeNumber
' Set the object variable to reference the file you want
to see.
Set MyXL = GetObject(strFileName)

' Show Microsoft Excel through its Application property.
Then
' show the actual window containing the file using the
Windows
' collection of the MyXL object reference.
MyXL.Application.Visible = True
MyXL.Parent.Windows(1).Visible = True
'Do manipulations of your file here.
' ...
Set xlSheet = MyXL.Worksheets(strRecipeNumber)
'Set xlSheet = xlBook.Worksheets(strRecipeNum)

xlSheet.Activate
DoCmd.SetWarnings True
MyXL.Application.DisplayAlerts = True

MyXL.Application.AskToUpdateLinks = True
MyXL.Application.Visible = True
xlSheet.Visible = xlSheetVisible

' If this copy of Microsoft Excel was not running when you
' started, close it using the Application property's Quit
method.
' Note that when you try to quit Microsoft Excel, the
' title bar blinks and a message is displayed asking if you
' want to save any loaded files.
If ExcelWasNotRunning = True Then
MyXL.Application.Quit
End If

Set MyXL = Nothing ' Release reference to the
' application and
spreadsheet.
End Function


Sub DetectExcel()
' Procedure dectects a running Excel and registers it.
Const WM_USER = 1024
Dim hWnd As Long
' If Excel is running this API call returns its handle.
hWnd = FindWindow("XLMAIN", 0)
If hWnd = 0 Then ' 0 means Excel not running.
Exit Sub
Else
' Excel is running so use the SendMessage API
' function to enter it in the Running Object Table.
SendMessage hWnd, WM_USER + 18, 0, 0
End If
End Sub
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top