Excel AutoFilter

[niall29]

Hi Again,
and thanks for all your help so far with this project. So far I have been able to pull all the info I need and export it to an excel file. My question is How can I activate auto filter in my code because when my mangers gets the file that is the first thing they do and if possible I would like to automate that. but am having no luck trying to figure it out.

Thanks in advance for any help.

 

[Mike_R]

Have you tried the Macro Recorder? If you turn the Macro Recorder on, and then take the steps that your Managers are making, and then turn the Macro Recorder off, you should get an idea of what are some of the basic commands.

The other way to get initial help is to use the Object Browser and search on AutoFilter. This will kick out a few Properties as well as one AutoFilter Class. Hit <F1> for help on these topics.

Get as far as you can with this and when you get stuck again, show us what code you have and describe the problems that you are having...

-- Mike

 

[niall29]

I am actually having 2 problems:
1 When I do the export to SQL the Accnt # (16 digits) eports to excel like 1.11111E +15.
2. Turning on Auto filter

The code I am using is like.

Response.ContentType = "application/vnd.ms-excel"
Remove the charset from the Content-Type header.

Response.Charset = ""

Turn off the view state.
Me.EnableViewState = True
Dim tw As New System.IO.StringWriter
Dim hw As New System.Web.UI.HtmlTextWriter(tw)

Get the HTML for the control.
DataGrid1.RenderControl(hw)
Write the HTML back to the browser.
Response.Write(tw.ToString())
End the response.

Response.End()


Response.ContentType("A:A")Selected)
End If


Conn.Close()
End Sub

If you need any more let me know but I think this should give the basic idea.

 

[Mike_R]

Unfortunately, I am very poor at ADO/SQL, so I do not know that I will be able to help much, other than for the Excel aspects... But of what I can see in your code:

(1) Excel Cells hold numbers as Double Data Types and so it is only 64 bits. This means that it only has precision out to 15 Decimal values. This is why your Data is being Truncated and you are losing the 16th digit.

If the result from your Recordset is actually a String with 16 digits in it, you will want to prepend a Single Quote () character to the beginning of this String. Then pass that result to the xlRng.Value. Something like this:

Dim xlRng as Excel.Range
xlRng = xlApp.Range("A1")
xlRng.Value = "" & RecordSetResult.ToString

This is the basic idea, I hope this was clear?

(2) The 2nd part I cant figure out at all. It looks like youve left out your SQL String and other factors, so I really dont know what its trying to do. That said, Im a n00b in ADO and so Im not sure how much it would help me.

The bigger issue, though, is that I dont see any Excel Code in there. If using ADO to read the Worksheet, this should be provided within the ConnectionStr. But if you are using a RecordSet to get your values and then post the results to the Worksheet, I dont see where that is happening.

I also dont see any Excel.AutoFilter code in there. If you wish to use Excels AutoFilter, I would try opening up Excel itself and using the Macro Recorder with Alt|Tools|Macros|RecordMacros... and then use the AutoFilter properties you want. When done, Stop the Recorder and hit Alt+F11 to see the resulting code. It will not be code that will run in .Net, but it will give you a pretty good idea of whats going on. Hit F1 on any of the Properties, Methods or Objects that look of interest in order to get Help on these topics. Also, use the Object Browser, searching on "AutoFilter", then get help on those Objects and Properties. This should start to give you a good feel...

-- Mike

 

[niall29]

But I dont have Excel installed on the IIS Server.
It doesnt let me call Excel.AutoFilter. Do you know, Do I have to install Excel on the IIS server.

 

[Mike_R]

Yes, correct. You cannot Automate MS Office programs without it actually being present on the machine.

Further, if you are looking to provide web services while Automating Excel on the back end, there are a number of of issues to keep in mind... I would have a read here: INFO: Considerations for Server-Side Automation of Office.

-- Mike