Creating and Running Excel Macros using Access

[Sech]

Here is a true test for any Access Expert out there...

For a long time now I have been trying to find an answer to this problem but no-one seems to be able to help. I maintain and update a database that exports complex reports to customers. When these reports are output to Excel files, the values in any date field columns are not immediately recognised as date fields. They start off as text fields and only become date fields once they are selected. Note: This only happens when exporting an Excel file using a report as its basis rather than a query. No matter what settings or formats are set up on the report the values will always intially be text.

The customer needs to be able to sort by order of date at any time, they have complete flexibility to sort by any of the fields. However as they values are not recognised as dates, they sort in alphabetic order which is incorrect. I have tried many different ways of fixing this problem. I thought about formatting the newly created Excel file using code. The Format command didnt work. Then I tried Text To Columns. In Excel, this works and fixes the whole column back to Date fields. On a small test database I made this also worked in the code. However with the more complex reports of the actual database it does not seem to work correctly and only changes back some of the values into dates when done in code (When selected in Excel it changes them all like it should). There have been several other suggestions and all so far have failed to fix the problem. So moving on from what I have already tried, does anyone know how to get Access to open the Excel file, create a new physical macro attached to the file, run the macro and then delete it again (The deletion is not essential but preferred)? If I could work out how to do this I could create the Text To Columns code in a macro and then automatically run it on the file after export. Please help!

 

[slreynolds]

I have a couple of similar applications. In one I send the data from Access to a named range on a hidden worksheet and then have the report on a separate worksheet which links to the named range. The report worksheet maintains the formatting I set because I don't overwrite anything on that sheet. The report actually contains formulas, not data so this may not be what your client needs.

In the other application I copy and paste the Access data into previously formatted cells using "paste special" - "text". This gives actual data in cells that maintain their formatting.

 

[GHolden]

You could use automation to set the format of the cells to date format after export (assuming you know which cols/rows you need to format)..

eg.

Dim ExcelApp As New Excel.Application

With ExcelApp
'Open Excel
.Workbooks.Open "c:\test\test.xls"
'Make application visible
.Visible = True
'Either...
'Set a range to date format
.Range("A1:A10&quot.NumberFormat = "dd/mm/yy"

'Set a Row to date format
.Rows(1).NumberFormat = "dd/mm/yy/"

'Set a column to date format
.Columns("B&quot.NumberFormat = "dd/mm/yy"

'Close Excel and save changes
.ActiveWorkbook.Close True
.Quit

End With


If you want to do this you need to reference Microsoft Excel Object Library

Just possibly another way round the problem??

There are two ways to write error-free programs; only the third one works.