UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
2 Pages V < 1 2  (Go to first unread post)
   Reply to this topicStart new topic
> Export To Excel Best Practice, Access 2013    
 
   
River59
post Feb 8 2019, 10:51 AM
Post#21



Posts: 1,581
Joined: 7-April 10
From: Detroit, MI


cheekybudda, for some reason I had the thought in my mind that user was left with an instance of Excel being left open in the background. I have no idea where that came from, either another post or I dreamed it last night.
I just reread his post and you are certainly correct about not closing Excel.

Oops!

--------------------
Remember ... Armstrong, Aldrin and Collins flew to the moon and back with a computer system less complex than a modern, programmable toaster ...
Go to the top of the page
 
gint32
post Feb 8 2019, 10:57 AM
Post#22



Posts: 340
Joined: 8-May 09
From: Australia


QUOTE
You wouldn't want to quit your Excel instance if you want to leave it open for the user, no?


Yep, need to leave it open as users need to input other data that I don't have access to be able to import.

thanks though.

Go to the top of the page
 
gint32
post Feb 8 2019, 11:00 AM
Post#23



Posts: 340
Joined: 8-May 09
From: Australia


QUOTE
You wouldn't want to quit your Excel instance if you want to leave it open for the user, no?


Yep, need to leave it open as users need to input other data that I don't have access to be able to import.

thanks though.

Go to the top of the page
 
cheekybuddha
post Feb 8 2019, 11:01 AM
Post#24


UtterAccess VIP
Posts: 10,880
Joined: 6-December 03
From: Telegraph Hill


>> for some reason I had the thought in my mind that user was left with an instance of Excel being left open in the background <<

You are also right about that! gint32 said in Post#8:
>> I checked processes and Excels still running even though all open/visible excel workbooks are closed.

Obviously, I can't just right the VBA code to kill off all excel processes<<

The reason for this is as I was trying to describe above - however, it is a difficult concept to understand.

thumbup.gif

d

--------------------


Regards,

David Marten
Go to the top of the page
 
gint32
post Feb 8 2019, 11:05 AM
Post#25



Posts: 340
Joined: 8-May 09
From: Australia


QUOTE
Think i have discovered the issue, but haven't solved it as yet...

I checked processes and Excels still running even though all open/visible excel workbooks are closed.

Obviously, I can't just right the VBA code to kill off all excel processes, ...in case users are multitasking as they would loose all there other excel work and my name would be mud:) ...

Does anyone with any ideas? on a way forward.


CheekyBuddha. ..Not dreaming ...and therefore you are correct there are instances still running in the background.
Go to the top of the page
 
gint32
post Feb 8 2019, 11:10 AM
Post#26



Posts: 340
Joined: 8-May 09
From: Australia


QUOTE
This code is a bit of a mess (as is always the way when just taking the output from the Excel macro recorder!) and could be tidied up further:


Well, thanks few! It did take me me a bit of work to go through what changes you had done and resolve all the little errors, but hey! after about an hour I finally got it to compile and run, so thanks

I don't see any more issues, so far! so when i get into work Sat I'll give it a go and get back to you if any issues....thanks
Go to the top of the page
 
cheekybuddha
post Feb 8 2019, 11:14 AM
Post#27


UtterAccess VIP
Posts: 10,880
Joined: 6-December 03
From: Telegraph Hill


@gint32,

Did you compare your code with my re-write? (Or try my re-write?)

Really, it is a bug in Excel automation (though MS won't admit it!), but you must make sure all Excel objects you reference are child properties of the original appExcel which you create.

Another useful thing, if you haven't already done so, is to ensure that you have Option Explicit declared at the top of every code module.

Go through the code, try and understand what's different/what's happening, test it, and come back with anything that you don't understand.

hth,

d

[EDIT: we crossed posts!]

--------------------


Regards,

David Marten
Go to the top of the page
 
gint32
post Feb 8 2019, 11:43 AM
Post#28



Posts: 340
Joined: 8-May 09
From: Australia


QUOTE
I thought I hade made it plain
.
thanks paul. but stuck with what i had
Go to the top of the page
 
River59
post Feb 8 2019, 12:06 PM
Post#29



Posts: 1,581
Joined: 7-April 10
From: Detroit, MI


I would eliminate all of these problems by using an Attachment table and attaching an Excel Template. Then you open a new instance of Excel, open the template, pass the recordset, the user saves where they need to under whatever they name it, then appExcel.Quit. That quits the instance of Excel with the template, not any other instance of Excel the user may have open. The template is a template, and never changes. You can Quit or not Quit Excel (if the user needs to enter data you wouldn't).

I know that some people avoid Attachment tables but they can be a good thing. I worked with a few databases that had 266 Excel templates in the Attachment table. This was absolutely necessary because these templates were in 35 languages. We had to open the appropriate template to pass the data to.

Glad OP got this working for his needs!
This post has been edited by River59: Feb 8 2019, 12:08 PM

--------------------
Remember ... Armstrong, Aldrin and Collins flew to the moon and back with a computer system less complex than a modern, programmable toaster ...
Go to the top of the page
 
cheekybuddha
post Feb 8 2019, 12:20 PM
Post#30


UtterAccess VIP
Posts: 10,880
Joined: 6-December 03
From: Telegraph Hill


>> That quits the instance of Excel with the template <<

Only if there are no unqualified references to Excel objects in the automation code!!

--------------------


Regards,

David Marten
Go to the top of the page
 
River59
post Feb 8 2019, 01:05 PM
Post#31



Posts: 1,581
Joined: 7-April 10
From: Detroit, MI


That is right, cheekybudda. You have to qualify Excel.

I used Set appExcel = New Excel.Application to open a new instance of Excel (in case users have other instances open)

Then, appExcel.Quit only closed this instance of Excel (not any instances the users may have had open doing other tasks)

CODE
Dim appExcel as Excel.Application
Set appExcel = New Excel.Application


Using Microsoft Excel 16.0 Object Library (OP needs to reference his version or user late binding)

--------------------
Remember ... Armstrong, Aldrin and Collins flew to the moon and back with a computer system less complex than a modern, programmable toaster ...
Go to the top of the page
 
gint32
post Feb 8 2019, 04:43 PM
Post#32



Posts: 340
Joined: 8-May 09
From: Australia


QUOTE
Using Microsoft Excel 16.0 Object Library

Will it also be the same syntax for the 15.0 object library
Go to the top of the page
 
WildBird
post Feb 10 2019, 08:23 PM
Post#33


UtterAccess VIP
Posts: 3,500
Joined: 19-August 03
From: Auckland, Little Australia


Sorry, was weekend and didnt get back to it.

As I said, I have multiple functions, so will try to list the main ones here and you may need to test and come back if there is more code needed.

Needs a ini file - same name as the application, with .ini extension.
Entries like
ExportPath=\\XXXXXXXXXXXXXXXXXXX\KPIs\Weekly Report Incidents\SLAReporting\DEVExport\
TemplatePath=\\XXXXXXXXXXXXXXXXX\IS_Service_Operations\KPIs\Weekly Report Incidents\SLAReporting\Template\
TemplateNameCMDB=CMDBTemplate.xlsm
ErrorPath=\\XXXXXXXXXXXXXX\IS_Service_Operations\KPIs\Weekly Report Incidents\

In this code, I would call something like this
If processCMDB then
msgbox "CMDB Reports run successfully"
end if

I use Excel here, no Access. The PopulateDataSheet function is setup for Worksheets, not Access queries. Easy to change though, just cant get to the old code here (as I dont have Access.) Basically query defs, instead of worksheets.

Actually, a couple of lines are Excel specific, not Access. If you have issues, let me know.

At work now, cant write much. Any issues with this, let me know though.

CODE
Function ProcessCMDB() As Boolean
'Date:          Wednesday, 05 November 2014 10:00:26 AM
'Author:        Stephen Cooper
'Email:         coopers@xxxxxxxxxxxxxxxx.com
'Ph:
'In parameters
'Output
'Description:   This will copy the template file, and then populate it
'Calls:
'Notes:
'Example:

On Error GoTo HandleError:

ProcessCMDB = True

Dim varNow As Variant
Dim intMouseType As Integer
Dim strErrorMsg As String
Dim varReturn As Variant
Dim objXLApp As Object
Dim objXLBook As Object
Dim objXLMainSheet As Object
Dim objFSO As Object
Dim strTemplatePath As String
Dim strExportPath As String
Dim strTemplateName As String
Dim strNewFileName As String
Dim strProcessedDate As String
Dim objFile As Object
Dim lngSeconds As Long

intMouseType = Application.Cursor

Application.Cursor = xlWait

DoEvents

varNow = Now

'Get locations of templates, and where to put the new files
strTemplatePath = CheckPath(GetString("TemplatePath"))
strExportPath = CheckPath(GetString("ExportPathCMDB"))
strTemplateName = GetString("TemplateNameCMDB")
strNewFileName = Format(Now, "yyyymmddhhmmss") & "CMDBReport.xlsm"

Set objFSO = CreateObject("Scripting.FileSystemObject")

'Write a block file that will stop GetLatest from opening it
WriteBlock CheckPath(strExportPath) & "GetLatest\"

'Check file exists first
If Not objFSO.FileExists(strTemplatePath & strTemplateName) Then
    MsgBox "Could not find file " & strTemplatePath & strTemplateName & ". Please check path and name and try again", vbCritical
    ProcessCMDB = False
    GoTo ExitHere
End If 'Not objFSO.FileExists(strTemplatePath & strTemplateName)

'Now check if the file has been created before, if so, delete it
If objFSO.FileExists(strExportPath & strNewFileName) Then
    objFSO.DeleteFile strExportPath & strNewFileName
End If 'objFSO.FileExists(strExportPath & strNewFileName)

'Sometimes overwrite doesnt work, so delete above to make sure
varReturn = Application.StatusBar = "Copying template file"
objFSO.CopyFile strTemplatePath & strTemplateName, strExportPath & strNewFileName, True

Set objFile = objFSO.getFile(strExportPath & strNewFileName)

'Now we have new file
Set objXLApp = CreateObject("Excel.Application")

'Make it non visible. Speeds it up
objXLApp.Application.Visible = False

Application.StatusBar = "Opening Excel file"

'Coop - 20/11/2014 - turn off events, so the file isnt looking for an ini file that possibly isnt there yet.
objXLApp.EnableEvents = False

Set objXLBook = objXLApp.Workbooks.Open(strExportPath & strNewFileName, False, False)
'Set objXLBook = objXLApp.Workbooks.Open(strExportPath & strNewFileName, True, False)

Set objXLMainSheet = objXLBook.Worksheets("Main")

strProcessedDate = "Processed " & Format(Now(), "ddd, dd mmm, yyyy hh:nn:ss")

'Set some values on the main sheet
With objXLMainSheet
    .Cells(4, 2) = strProcessedDate
End With

If Not PopulateDataSheet(objXLBook, "CMDB") Then
    ProcessCMDB = False
    GoTo ExitHere
End If 'Not PopulateDataSheet(objXLWorkbook, "xp")

'run external macro so it is run before it gets opened
objXLApp.Run (objXLBook.Name & "!RunAll")

ExitHere:
On Error Resume Next
'Close all recordsets etc here
Application.StatusBar = False
Application.Cursor = intMouseType
objXLApp.EnableEvents = True
objXLBook.Save
objXLBook.Close
Set objXLApp = Nothing
Set objXLBook = Nothing
'kill the block file
objFSO.DeleteFile strExportPath & "GetLatest\Block.txt"
Set objFSO = Nothing
'Added an audit trail to see who ran it
lngSeconds = DateDiff("s", varNow, Now)
'This is for a SQL Server stored proc to log the report has been run. Could do similar in Access I guess
'ExecuteRunReportSP "CMDBReport", strExportPath, strNewFileName, GetComputerName, strProcessedDate, objFile.Size, lngSeconds
Set objFile = Nothing
'Debug.Print "It took " & DateDiff("s", varNow, Now()) & " seconds to run ProcessCMDB()"
Exit Function

HandleError:
Select Case Err.Number
Case Else
    LogError "ProcessCMDB|" & ActiveWorkbook.Name & "|" & strErrorMsg & "|" & Err.Number & " - " & Err.Description & "| Line number " & Erl
    MsgBox strErrorMsg & " " & Err.Number & " " & Err.Description, vbInformation, "Error"
    ProcessCMDB = False
    Resume ExitHere
End Select

End Function



CODE
Function CheckPath(ByVal strPath As String) As String
'Checks to see if a path has a backslash. If so, leaves it, otherwise appends one
If Right(strPath, 1) = "\" Then
    CheckPath = strPath
Else
    CheckPath = strPath & "\"
End If

End Function



CODE
Function GetString(ByVal strSection As String) As String
'Date:          Monday, 13 January 2014 12:52:45 PM
'Author:        Stephen Cooper
'Email:         coopers@XXXXXXXXXXXXXXXXXXX.com
'Ph:
'In parameters
'Output
'Description:
'Calls:
'Notes:
'Example:

On Error GoTo HandleError:

Dim strErrorMsg As String
Dim varReturn As Variant
Dim intFileNo As Integer
Dim strResult As String
Dim strReadLine As String
Dim intPos As Integer
Dim strFile As String
Dim strIniName As String

DoEvents

Application.Cursor = xlWait

strIniName = Left(ThisWorkbook.Name, InStr(ThisWorkbook.Name, ".") - 1) & ".ini"

strFile = ThisWorkbook.Path & "\" & strIniName

If Dir(strFile) = "" Then
MsgBox "File " & strFile & " could not be located", vbCritical, "File Error"
GetString = ""
GoTo ExitHere
End If 'Dir(strFile) = ""

intFileNo = FreeFile
Open strFile For Input As intFileNo
Do While Not EOF(intFileNo)
    Line Input #intFileNo, strReadLine
    intPos = InStr(1, strReadLine, "=") - 1
    If intPos = 0 Then
        Exit Do
    End If 'intPos = 0
    If UCase(Trim(Left(strReadLine, intPos))) = UCase(Trim(strSection)) Then
        strResult = Mid(strReadLine, intPos + 2)
        Exit Do
    End If 'strReadLine = "[" & strSection & "]"
Loop 'While Not EOF(intFileNo)

GetString = Trim(strResult)

ExitHere:
On Error Resume Next
'Close all recordsets etc here
varReturn = Application.StatusBar = False
'application.Cursor = intMouseType
Application.Cursor = xlDefault
Close #intFileNo
Exit Function

HandleError:
Select Case Err.Number
Case Else
    LogError "GetString|" & ThisWorkbook.Name & "|" & strErrorMsg & "|" & Err.Number & " - " & Err.Description & "| Line number " & Erl
    MsgBox strErrorMsg & " " & Err.Number & " " & Err.Description, vbInformation, "Error"
    GetString = False
    'DoCmd.Close acForm, strUpdateForm, acSaveNo
    Resume ExitHere
End Select

End Function



CODE
Sub WriteBlock(ByVal strFilePath As String)
'Date:      11th June 4:38 PM
'Author:    Stephen Cooper
'Email:     stephen.cooper@xxx.com.au
'Ph:        8963
'In parameters  -   strErrorMSg - A string with values of variables
'Output
'Description:   This will write a file with the username and date time. GetLatest will check the existence of the file. Wont open if the file is present - prevents users from trying to open latest file while it is being created
'Notes:        
'Example        

Const ForReading = 1, ForWriting = 2, ForAppending = 8
Dim strFileName As String
Dim fso As Variant
Dim f As Variant
Dim strUserName As String

strUserName = GetUser
strFileName = "Block.txt"

Set fso = CreateObject("Scripting.FileSystemObject")
'Delete if it exists, appending will still work
If fso.FileExists(strFilePath & strFileName) Then
    fso.DeleteFile strFilePath & strFileName
End If

Set f = fso.OpenTextFile(strFilePath & strFileName, ForAppending, True)
f.Write strUserName & "|" & Now

f.Close

ExitHere:
    'DoCmd.Hourglass False
    Application.Cursor = xlDefault
    Exit Sub

HandleError:
    Select Case Err.Number
        Case Else
            MsgBox "Error in WriteBlock " & Err.Number & " " & Err.Description, vbInformation, "Error"
            Resume ExitHere
    End Select
End Sub



CODE
'New module, needs declaration at the top
Option Explicit

Declare Function GetUserName Lib "advapi32.dll" Alias "GetUserNameA" (ByVal lpBuffer As String, nSize As Long) As Long

'Could use a non API - like GetUser = environ("username"). Many examples on here

Function GetUser() As String
   Dim RetVal As Integer
   Dim UserName As String
   Dim Buffer As String
   Buffer = String(25, " ")
   RetVal = GetUserName(Buffer, 25)
   UserName = Strings.Left(Buffer, InStr(Buffer, Chr(0)) - 1)
   GetUser = UserName
End Function



CODE
Function PopulateDataSheet(ByRef objXLBook As Object, ByVal strPrefix As String) As Boolean
'Date:          Tuesday, 21 August 2018 9:12:43 AM
'Author:        Stephen Cooper
'Email:         coopers@consultant.com
'Ph:
'In parameters
'Output
'Description:   Will loop the worksheets and export to the workbook
'Calls:
'Notes:         Workbook template needs to have worksheets setup with name, and any associated macros
'Example:

On Error GoTo HandleError:

PopulateDataSheet = True

Dim intMouseType As Integer
Dim strErrorMsg As String
Dim wks As Worksheet
Dim strSheetName As String
Dim strHeader As String
Dim i As Integer
Dim strDBPath As String
Dim strDBName As String
Dim cn As ADODB.Connection
Dim rst As ADODB.Recordset
Dim strSQL As String
Dim strxpSheetName As String

Dim wksNew As Worksheet

intMouseType = Application.Cursor

Application.Cursor = xlWait

DoEvents

strDBPath = CheckPath(GetUNC(ActiveWorkbook.Path))

strDBName = ThisWorkbook.Name

Set cn = New ADODB.Connection

With cn
    .Provider = "Microsoft.ACE.OLEDB.12.0"
    .ConnectionString = "Data Source = " & strDBPath & strDBName & ";Extended Properties=Excel 12.0;"
    .Open
End With 'cn

For Each wks In ThisWorkbook.Worksheets
    strSheetName = wks.Name
    strErrorMsg = strSheetName
    'If Left(strSheetName, 2) = "xp" Then
    If Left(strSheetName, Len(strPrefix)) = strPrefix Then
    'Coop - 21/09/2018 - added a parameter so that 2 different kinds of exports can be done - SLA and Full reports
        Application.StatusBar = "Populating Excel File: " & strSheetName
        
        strSQL = "SELECT * FROM [" & strSheetName & "$]"
        Set rst = cn.Execute(strSQL)
        
        'Build header
        strHeader = ""
        With rst
            For i = 0 To rst.Fields.Count - 1
                strHeader = strHeader & .Fields(i).Name & ","
            Next i
            'remove trailing comma
            strHeader = Left(strHeader, Len(strHeader) - 1)
        End With 'rst

        'Sheet name must equal the same
        strxpSheetName = Mid(strSheetName, Len(strPrefix) + 1) & "Data"
        With objXLBook.Worksheets(strxpSheetName)
            .Activate
            For i = 1 To rst.Fields.Count
                'Build the header first
                While InStr(1, strHeader, ",") > 1
                    .Cells(1, i) = Left(strHeader, InStr(1, strHeader, ",") - 1)
                    strHeader = Mid(strHeader, InStr(strHeader, ",") + 1)
                    i = i + 1
                Wend 'InStr(1, strHeader, ".") > 1
                'Put in last part of header
                .Cells(1, i) = strHeader
            Next i
              
            .Cells(2, 1).CopyFromRecordset rst
        
        End With 'objXLBook.Worksheets(strxpSheetName)
    End If 'Left(strSheetName, Len(strPrefix)) = strPrefix
Next wks 'In ActiveWorkbook.Worksheets

ExitHere:
On Error Resume Next
'Close all recordsets etc here
Application.StatusBar = False
Application.Cursor = intMouseType
rst.Close
Set rst = Nothing
Exit Function

HandleError:
Select Case Err.Number
Case 9
'Subscript out of range
'add a new sheet
    Set wksNew = objXLBook.Sheets.Add
    wksNew.Name = strxpSheetName
    '    objXLBook.Worksheets.Add(, objXLBook.Worksheets(Worksheets.Count)).Name = strxpSheetName
    Resume

Case Else
    LogError "PopulateDataSheet|" & ActiveWorkbook.Name & "|" & strErrorMsg & "|" & Err.Number & " - " & Err.Description & "| Line number " & Erl
    MsgBox strErrorMsg & " " & Err.Number & " " & Err.Description, vbInformation, "Error"
    PopulateDataSheet = False
    Resume ExitHere
End Select

End Function



CODE
Sub LogError(Optional strErrorMsg As String)
'Date:      11th June 4:38 PM
'Author:    Stephen Cooper
'Email:     stephen.cooper@xxx.com.au
'Ph:        8963
'In parameters  -   strErrorMSg - A string with values of variables
'Output
'Description:   This will write to a pipe delimited text file all the errors and various
                'times, users etc.
'Notes:         'Error should have user name already.
'Example        LogError("SC5|3089 Object doesnt exist|strSQL = Delete * FROM tblNotHere")   )

Const ForReading = 1, ForWriting = 2, ForAppending = 8
Dim strFileName As String
Dim strFilePath As String
Dim fso As Variant
Dim f As Variant
Dim strUserName As String

strUserName = GetUser
strFileName = "ErrorLog.txt"
strFilePath = CheckPath(GetString("ErrorPath"))

Set fso = CreateObject("Scripting.FileSystemObject")
Set f = fso.OpenTextFile(strFilePath & strFileName, ForAppending, True)
f.Write strUserName & "|" & strErrorMsg & "|" & Now & vbCrLf

f.Close

ExitHere:
    'DoCmd.Hourglass False
    Application.Cursor = xlDefault
    Exit Sub

HandleError:
    Select Case Err.Number
        Case Else
            MsgBox "Ironically there was an error in the error handler " & Err.Number & " " & Err.Description, vbInformation, "Error"
            Resume ExitHere
    End Select
End Sub



CODE
Private Function TrimNull(ByVal strItem As String) As String
Dim intPos As Integer
    intPos = InStr(strItem, vbNullChar)
    If intPos > 0 Then
        TrimNull = Left(strItem, intPos - 1)
    Else
        TrimNull = strItem
    End If
End Function

Public Function GetUNC(ByVal strPath As String) As String
'Note, this function will only return the UNC for network drives.
'Non-net drives and errors get the original value returned to them
On Error GoTo Err_GetUNC
    Const lngcBuffer As Long = 257
    Dim strUNCPath As String
    Dim strDrive As String
    If Left(strPath, 2) Like "[a-z, A-Z]:" Then
        strDrive = Left(strPath, 2)
        strUNCPath = strUNCPath & Space(lngcBuffer)
        'The function will automatically fill the strUNCPath unless there
        'is an error (return<>0), fill strPath if error
        If apiWNetGetConnection(strDrive, strUNCPath, lngcBuffer) = 0 Then
            strUNCPath = TrimNull(strUNCPath) & Mid(strPath, 3)
        Else
            strUNCPath = strPath
        End If
    End If
    If Len(Trim(strUNCPath)) = 0 Then strUNCPath = strPath
    GetUNC = strUNCPath
Exit_GetUNC:
    Exit Function
Err_GetUNC:
    MsgBox "Error " & Err.Number & " " & Err.Description
    Resume Exit_GetUNC
End Function




--------------------
Beer, natures brain defragging tool.
Go to the top of the page
 
gint32
post Feb 12 2019, 06:26 PM
Post#34



Posts: 340
Joined: 8-May 09
From: Australia


QUOTE
re-Sorry, was weekend and didnt get back to it.

Thanks for posting the VBA, all sorted smile.gif
Go to the top of the page
 
cheekybuddha
post Feb 12 2019, 06:29 PM
Post#35


UtterAccess VIP
Posts: 10,880
Joined: 6-December 03
From: Telegraph Hill


Hi,

Congratulations on solving your issue!

Are you able to share you final solution so that others in a similar situation may benefit also?

thumbup.gif

d

--------------------


Regards,

David Marten
Go to the top of the page
 
gint32
post Feb 12 2019, 07:06 PM
Post#36



Posts: 340
Joined: 8-May 09
From: Australia


This issue was defo Excel process's still running the background, even though the VBA had ".close" , ".quit" and "= nothing set"

So, I am not 100% sure, but after the VBA was altered to ...
CODE
Set appExcel = New Excel.Application.


As I no longer had rouge process's still running in the background, and therefore had a clean quit on every instance execution of the code, regardless of what cells were being manipulated. Although from a users point of view it seemed like every instance of running the VBA was a bit hit and miss, but it was purely down to whether Excel has processes still running or not. which is what was misleading me to think that this method was not reliable or stable enough to use and therefor why I was seeking an alternative method.

Once again...thanks to all.


QUOTE
I checked processes and Excels still running even though all open/visible excel workbooks are closed.
River59 ..That is right, cheekybudda. You have to qualify Excel.

I used Set appExcel = New Excel.Application to open a new instance of Excel (in case users have other instances open)

Then, appExcel.Quit only closed this instance of Excel (not any instances the users may have had open doing other tasks)

CODE
Dim appExcel as Excel.Application
Set appExcel = New Excel.Application

This post has been edited by gint32: Feb 12 2019, 07:07 PM
Go to the top of the page
 
2 Pages V < 1 2


Custom Search


RSSSearch   Top   Lo-Fi    16th February 2019 - 01:12 PM