Creating Excel macro expert help required!

[Sech]

I am currently trying to update the export of reports from a database. When the reports are exported to Excel files, any date fields in them are not initially counted as being dates. They act like text fields until the mouse is clicked inside them, at which point they revert to dates. Lots of various things have been tried to change them back into dates automatically. Formatting the Excel column using macro code in Access does not work. Settings and formatting in Access prior to export makes no difference. The Text To Columns function in Excel holds the key to sorting out the problem. When selected in Excel it works, changing the whole column back into dates. When done using the macro code in Access however it only partially works and many of the values remain as text. This does not happen with another simplified test database so it must be something to do with the complexity of the reports.

So after much help and testing I have come to believe that the only way to fix this problem is to create a new macro using Access code, run it on the new file and then delete it. I have now got most of the code in working order, but there are several things still not working:

1) The following line in the code is not working right, due to it not recognising vbext_ct_StdModule. This code was given to me in another thread on this forum but there was no explanation as to what this variable is or how to set it up:
oxlApp.VBE.ActiveVBProject.VBComponents.Add (vbext_ct_StdModule)

2) The below line runs the newly created macro on the file:
oxlApp.Run ("data.xls!FormatDates&quot
Is it possible to replace data.xls with oxlWorkbook or strFileName variables? I need to do this because the export files have many different names.

3) I still need to find out code to delete the FormatDates macro from the open Excel workbook. If anyone knows this can you please let me know.

If I can work out these things then the process should be complete. Please try to help if you can as I have been working on this problem now for months on and off. Anyway heres the code Ive created to sort out the exported Excel file (By running Text To Columns on Column C):


Sub FormatXLS(strFileName As String)

On Error GoTo Errcheck

Dim oxlApp As Excel.Application
Dim oxlWorkbook As Workbook
Dim oxlWorksheet As Worksheet
Dim strMacro As String

'Start Excel application in the background
Set oxlApp = New Excel.Application

'Open the Excel file
Set oxlWorkbook = oxlApp.Workbooks.Open(strFileName)

'Run MacroCode function to bring back code for the macro
strMacro = MacroCode

'Create new macro and name it FormatDates
oxlApp.ActiveWorkbook.VBProject.Name = "FormatDates"
oxlApp.VBE.ActiveVBProject.VBComponents.Add (vbext_ct_StdModule)

'Add code to macro
oxlApp.VBE.CodePanes(1).CodeModule.AddFromString strMacro

'Open Sheet1 of the workbook in a worksheet object
Set oxlWorksheet = oxlWorkbook.Sheets("Sheet1&quot

'Code to run FormatDates macro on
oxlApp.Run ("oxlWorkbook!FormatDates&quot

'Save the workbook
oxlWorkbook.Save

'Close the workbook and quit the Excel application
oxlWorkbook.Close False
oxlApp.Quit

'Clear vars
Set oxlWorksheet = Nothing
Set oxlWorkbook = Nothing
Set oxlApp = Nothing

Exit Sub

Errcheck:
oxlWorkbook.Close False
oxlApp.Quit
Set oxlWorksheet = Nothing
Set oxlWorkbook = Nothing
Set oxlApp = Nothing

End Sub

Function MacroCode() As String

'Set macro code
MacroCode = "Columns(""C:C"&quot.Select" & vbCrLf & "Selection.TextToColumns Destination:=Range(""C1"&quot, DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo :=Array(1, 4)"

End Function

 

[padinka]

Formatting the dates in the query works for me but I must use general date. e.g. Format([myField],"General Date&quot.

But there are many dates in the database so sometime I format them in excel using this... First select the column that you wish to format.


Selection.NumberFormat = "m/d/yy h:mm AM/PM"
Trisha
padinka@yahoo.com