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
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