How to set an autofilter on an excel spreadsheet

[Phil Smith]

I have a routine that ultimately results in a query being sent to an
Excel spreadsheet.

How can I, from Access, turn on "autofilter" for that spreadsheet so the
Excel challenged user does not have to?

Plhi

 

[Klatuu]

It will require Automation to do this. That means creating an Excel object,
opening the spreadsheet, setting the auto filter on, saving the spreadsheet,
and destroying all your Excel object references. You will need to be
competent in VBA.

Do you want to try it?

 

[Phil Smith]

I am "functional" in VB. I know how it works, and can write basic code,
but I do not have a whole lot of experience with objects manipulation.
I learn by doing, and am quite good with a reference, so a little
guidance goes a long way...

 

[Klatuu]

Here is some example code that does the work. This seems like a lot, but the
issue is that if not done correctly, an instance of Excel will stay in memory
and cause problems.

You can tell if this has happened if you try to open an excel document from
the desktop and it hangs up. If this happens, go to Task Manager, select the
Processes tab and delete Excel.exe.

Dim xlApp As Object ' Reference to Microsoft Excel.
Dim blnExcelWasNotRunning As Boolean ' Flag for final release.
Dim xlBook As Object 'Workbook Object
Dim xlSheet As Object 'Worksheet Object

On Error Resume Next ' Defer error trapping.
Set xlApp = GetObject(, "Excel.Application")
If Err.Number <> 0 Then
blnExcelWasNotRunning = True
Set xlApp = CreateObject("excel.application")
Else
DetectExcel
End If
Err.Clear ' Clear Err object in case error occurred.
On Error GoTo cmdImport_Click_Exit

Set xlBook = xlApp.Workbooks.Open("Put the file name here")
Set xlSheet = xlBook.Sheets(1)

xlSheet.Select
Selection.Autofilter

'Close the workbook and quit Excel
xlBook.Close
Set xlBook = Nothing
Set xlSheet = Nothing
If blnExcelWasNotRunning = True Then
xlApp.Quit
End If
Set xlApp = Nothing

The above code calls this routine:

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

 

[Phil Smith]

Actually, it looks pretty concise. It appears that if Escel was open
before this subroutine is run, it does not attempt to close it. So this
is a fairly safe routine to run if Excel IS open?

Now, here is a question, for curiosity at this point, I would just be
interested in a little guidance, simple because I expect it to be much
more intense... in terms of time and code, but I may experiment with it
as a learning process...

I think I understand from your example how to open Excel and a specific
spreadsheet, and how to set the properties of that spreadsheet, like
Autofilter. I will look for an online reference for some of the other
items that can be similarly set. The question is:

Is it possible to Build a Pivot table in a similar fashion?

Regardless, thank you very much. I will massage this into my system
tonight or tomorrow, and give it a go.

Phil

 

[Phil Smith]

First of all, thanx for the assist.

Second of all, the code fails. It points to this line
hWnd = FindWindow("XLMAIN", 0)
as an undefined function. FindWindow() is a native call(?), so perhaps
some library is not installed?

Third of all, I would expect
On Error GoTo cmdImport_Click_Exit
To give me trouble, as that function is not part of the code you gave me...

and finally, (just curious,) is it possible to use Automation to build a
pivot table?

 

[Klatuu]

Actually, it looks pretty concise. It appears that if Escel was open
before this subroutine is run, it does not attempt to close it. So this
is a fairly safe routine to run if Excel IS open?

Yes it is. That is the purpose of the code you are asking about. Notice it
first tries a GetObject. The GetObject requires the process already be in
memory. If it fails, it uses the CreateObject to start up an instance of
Excel

Now, here is a question, for curiosity at this point, I would just be
interested in a little guidance, simple because I expect it to be much
more intense... in terms of time and code, but I may experiment with it
as a learning process...

I think I understand from your example how to open Excel and a specific
spreadsheet, and how to set the properties of that spreadsheet, like
Autofilter. I will look for an online reference for some of the other
items that can be similarly set. The question is:

Is it possible to Build a Pivot table in a similar fashion?

Anything you can do directly in Excel, You can do through Automation.
Manipulating and Excel object is code intensive, however. If you want to
learn more about how the Excel Object model is constructed, use the Object
Browser in the VBA editor.

Another trick that will help is to open a spreadsheet in Excel, start
recording a macro, do what you want to do, then stop the recording. Then go
into the macro editor, and you can copy the code it created and use that. It
will take some modification to run it from Access so the object references
are correct, but it is a great learning tool and a decent short cut.

 

[Phil Smith]

Something is up with the server. I posted another response two hours
after this one you responded to, and this one was not there. Now, the
last one I posted is not there.

Thanx for the excellent advice. Two problems with the code. It bombs
saying
hWnd = FindWindow("XLMAIN", 0)
is an invaLID FUNCTION. This is a native function call, so perhaps I
need a library installed?

Also,
On Error GoTo cmdImport_Click_Exit
I think refers to a routine that is NOT a native function call, and not
within the code you provided. I assumed it was not important, and wrote
my own error handler to just bomb with a message...

 

[Klatuu]

Oops! I left that part out. It is an API call. Here is the code for it.
Put it in its own standard module. Mine is named modApplicationIsRunning.

*************Code starts**************
Option Compare Database
Option Explicit

'This code was originally written by Dev Ashish.
'It is not to be altered or distributed,
'except as part of an application.
'You are free to use it in any application,
'provided the copyright notice is left unchanged.
'
'Code Courtesy of
'Dev Ashish
'
Private Const SW_HIDE = 0
Private Const SW_SHOWNORMAL = 1
Private Const SW_NORMAL = 1
Private Const SW_SHOWMINIMIZED = 2
Private Const SW_SHOWMAXIMIZED = 3
Private Const SW_MAXIMIZE = 3
Private Const SW_SHOWNOACTIVATE = 4
Private Const SW_SHOW = 5
Private Const SW_MINIMIZE = 6
Private Const SW_SHOWMINNOACTIVE = 7
Private Const SW_SHOWNA = 8
Private Const SW_RESTORE = 9
Private Const SW_SHOWDEFAULT = 10
Private Const SW_MAX = 10

Private Declare Function apiFindWindow Lib "user32" Alias _
"FindWindowA" (ByVal strClass As String, _
ByVal lpWindow As String) As Long

Private Declare Function apiSendMessage Lib "user32" Alias _
"SendMessageA" (ByVal Hwnd As Long, ByVal Msg As Long, ByVal _
wParam As Long, lParam As Long) As Long

Private Declare Function apiSetForegroundWindow Lib "user32" Alias _
"SetForegroundWindow" (ByVal Hwnd As Long) As Long

Private Declare Function apiShowWindow Lib "user32" Alias _
"ShowWindow" (ByVal Hwnd As Long, ByVal nCmdShow As Long) As Long

Private Declare Function apiIsIconic Lib "user32" Alias _
"IsIconic" (ByVal Hwnd As Long) As Long

Function fIsAppRunning(ByVal strAppName As String, _
Optional fActivate As Boolean) As Boolean
Dim lngH As Long, strClassName As String
Dim lngX As Long, lngTmp As Long
Const WM_USER = 1024
On Local Error GoTo fIsAppRunning_Err
fIsAppRunning = False
Select Case LCase$(strAppName)
Case "excel": strClassName = "XLMain"
Case "word": strClassName = "OpusApp"
Case "access": strClassName = "OMain"
Case "powerpoint95": strClassName = "PP7FrameClass"
Case "powerpoint97": strClassName = "PP97FrameClass"
Case "notepad": strClassName = "NOTEPAD"
Case "paintbrush": strClassName = "pbParent"
Case "wordpad": strClassName = "WordPadClass"
Case Else: strClassName = vbNullString
End Select

If strClassName = "" Then
lngH = apiFindWindow(vbNullString, strAppName)
Else
lngH = apiFindWindow(strClassName, vbNullString)
End If
If lngH <> 0 Then
apiSendMessage lngH, WM_USER + 18, 0, 0
lngX = apiIsIconic(lngH)
If lngX <> 0 Then
lngTmp = apiShowWindow(lngH, SW_SHOWNORMAL)
End If
If fActivate Then
lngTmp = apiSetForegroundWindow(lngH)
End If
fIsAppRunning = True
End If
fIsAppRunning_Exit:
Exit Function
fIsAppRunning_Err:
fIsAppRunning = False
Resume fIsAppRunning_Exit
End Function
************Code Ends*********************

The other line you mentioned is setting the On Error to a tag in my code.
You will need to change it to the name of your error handle tag.

 

[Phil Smith]

I did as you suggested, but I still get that line as an undefined sub or
function, and I do not see FindWindow in this latest piece of code.

 

[Klatuu]

LOL, how did I do that? I posted the wrong code. Here it is:

' 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

Sorry, Phil.

 

[Phil Smith]

I hate to impose upon your generousity, but there is still a problem,
and I am unable to figure it out.

I am getting error 424 "Object required" when I get to

Selection.Autofilter

I did it in Excel and looked at the code, and it looked identical.
Thinking it may be a problem with the worksheet name, I changed

Set xlSheet = xlBook.Sheets(1)
to

Set xlSheet = xlBook.Sheets("Forecast"), which is the name of the
worksheet tab in question. No joy.

I know it's close...

 

[Klatuu]

try changing the line to
xlApp.Selection.Autofilter

 

[Phil Smith]

That did it perfectly. Thank you ever so much.