EXPORTING TO EXCEL AND FORMATTING IT 4

[fsweb2002]

Hi

I know how to export a query/table to an excel file.
I would also like to format the excel spreadsheet, mainly adding an autofilter (selection.autofilter with Excel VBA) from within Access.

I would also like to BOLD the text on a certain column, etc.
Basically I need to use the VBA comands for Excel from within Access, after the excel file has been created...

Is this possible ?
Thanks in advance

 

[cheerio]

The simple but mean answer is Yes.

What you need to do is in the vba editor go to tools, references and tick a reference to Excel. This will make the Excel object library available to your code so you can write code as if you were in Excel. The only difference is that in Excel itself there is an Application object and from another application you will need to create that object.

Be aware that it is easy to create an instance of Excel that is invisible so it does not show on the task bar so you must take care to quit Excel using code when you have finished.

Ken

 

[fsweb2002]

thanks cheerio,

If I go to the vba editor go to tools, references and tick a reference to Excel, does this "stay" with the DB so if I install it in other PC's the reference to Excel is automatically ticked, or do I have to tick on every PC I install it on ???
Thanks in advance.

 

[AWithers]

The answer here is 'hopefully'. I have had problem before where different users have different versions of excel installed e.g. some excel 97 some excel 2000. I got over this by copying the excel object library to a shared network drive and referencing that rather than a local version.

Hope it helps

Andy

 

[fsweb2002]

thanks AWithers
I like that idea. I will be having a backend database on the server, with multiple front ends running from it.
Your concept sounds great.
Could you please explain how to reference to the Excel library ???

Also, anyone, I am trying to do the following:

DoCmd.OpenQuery "Global BR Excel Export", acNormal, acAdd
DoCmd.TransferSpreadsheet acExport, 8, "Export2Excel", "c:\Matrix.xls", False, ""

Dim dbs As Database
Dim rst As Recordset

Set dbs = OpenDatabase("C:\Matrix.xls", False, False, "Excel 8.0;HDR=Yes;&quot

Selection.AutoFilter ****
*** I WANT TO APPLY THIS VBA CODE FROM EXCEL TO THE NEWLY MADE EXCEL SHEET (C:\MATRIX.XLS) BUT IT DOESN´T WORK. COULD ANYONE TELL ME HOW USE EXCEL VBA WITHIN ACCESS ????
dbs.Close

Your incredible help is always appreciated.

 

[AWithers]

As cheerio suggests when in a code module go to tools references and rather than ticking the excel library in the list (i.e. local registry entry) (in fact you need to de-select it) choose the browse option to the file located on the network

Re working with excel, I have attached some code that may be useful

Hope it helps

Andy

'set up variables for working with Excel

Dim objExcel As New Excel.Application ' Excel application object
Dim objExcelDoc As Object ' Excel object - workbook

' create Excel
Set objExcel = New Excel.Application

'open relevant file

Set objExcelDoc = objExcel.Workbooks.Open("C:\Matrix.xls&quot 'yourfile

Set objExcelDoc = Nothing 'clear memory
objExcel.Visible = True 'show excel

'now working in Excel

With objExcel
.Selection.AutoFilter
.Selection.AutoFilter Field:=1, Criteria1:="0"
.Selection.AutoFilter Field:=2, Criteria1:="New"
'replace 1 and 2 etc with the oolumns you want to filter on

end with
objExcel.ActiveWorkbook.Close SaveChanges:=True

objExcel.Quit 'close Excel
Set objExcel = Nothing 'clear memory

 

[fsweb2002]

dear AWithers

You´ve made my day!
This is exactly what I wanted!. I tried and it works perfectly!
You are a legend !

 

[fsweb2002]

hi AWithers and all

I have used your code and it works great on my machine.
However on someone elses PC it gives me an error (WinNT dr. Watson) and closes down Access without any further info.
I am also using WinNT (same system, so I am not sure what could be wrong.

any ideas?

 

[fsweb2002]

don´t worry, I worked out the problem, I had an error handling routine error...

 

[flaniganmj]

Howdy,

AWithers, your post above is very helpful. I have been trying to figure out a solution for this problem for quite some time. Thank you. I got a follow up though. I've already set up an Excel file that is used in the above code; how can I test to see if the Excel doc is already open or not? So if another use has the doc open reviewing charts, I want to stop another user from kicking of the sub. Any help or other suggestions would be greatly appriciated.

Thanks,

flan

 

[cheerio]

Flan

In the right hand column above it says

Who's Marked
This Thread?
cheerio
fsweb2002

so Awithers is not actively looking for activity on this thread and you may be better off starting a new thread.

Your question is not one I have previously had to address so I do not know how other people try to solve it.

So far as I can see there is no direct way to tell if a file is in use. However, you can probably use Windows itself to tell you. My idea would be to try to rename the file (renaming to the existing name might work even). The point is that Windows will generate an error if you try to rename a file that is in use.

So the logic would be

try to rename the file
if that fails the file is in use
otherwise rename it back (if necessary) and open the file.

Because this is conceptual and I don't have ready made code you may still be better off opening another thread if you do not get other contributions.

Ken

 

[AWithers]

Sorry dont think I can help that much

one way that might help would be to seperate out the data and the charts...put the data in one file and have the charts based on that file??? I think (??!!!??) in this way the users are unlikely to upset the datafile

Andy