Creating Excel macros in Access - Expert Help Requ

  • 18 years ago

    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 vbextctStdModule. 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 (vbextctStdModule)

    2) The below line runs the newly created macro on the file:
    oxlApp.Run ("data.xls!FormatDates")
    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 (vbextctStdModule)

    '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")

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

    'Save the workbook

    'Close the workbook and quit the Excel application
    oxlWorkbook.Close False
    'Clear vars
    Set oxlWorksheet = Nothing
    Set oxlWorkbook = Nothing
    Set oxlApp = Nothing
    Exit Sub

    oxlWorkbook.Close False
    Set oxlWorksheet = Nothing
    Set oxlWorkbook = Nothing
    Set oxlApp = Nothing

    End Sub

    Function MacroCode() As String

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

    End Function

Post a reply

No one has replied yet! Why not be the first?

Sign in or Join us (it's free).


Why not write for us? Or you could submit an event or a user group in your area. Alternatively just tell us what you think!

Our tools

We've got automatic conversion tools to convert C# to VB.NET, VB.NET to C#. Also you can compress javascript and compress css and generate sql connection strings.

“An idiot with a computer is a faster, better idiot” - Rich Julius