Using Excel macro code in Access - please help!

[Sech]

Hi all,

I've created a database that has a number of reports. These are exported out to Excel spreadsheets on the press of a button using the OutputTo command (Note: The report is exported NOT the query behind the report - I need the report layout). After export I then do various formatting by linking into Excel using macro code and a reference to the Microsoft Excel Object Library. This formatting comprises of realigning columns and changing titles into bold font etc. Many of the reports have date fields and this is where the problem occurs. I want to be able to sort in date order on these fields immediately after opening the files. However when they are first exported, the date fields do not begin as date format, instead they remain as text until you click inside each of the cells with the mouse. This appears to be a general problem with Access - try exporting an Excel with a date field through a report yourself!

There is a process known as "Convert Text to Columns" which can be selected from the Data menu. In Excel, if I select one of the date columns and then run Text To Columns on it, all the values immediately become proper dates instead of text values. SoI thought if this works in Excel why not record a macro, grab the code and use it in Access to convert the dates automatically after the file is created...so I've done this and set up the code. In the following example as a test Ive set it up to convert the 7th column (column G) which is a date field...


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

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

'Open the workbook passed as the parameter in workbook object
Set oxlWorkbook = oxlApp.Workbooks.Open(strFileName)

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

'Select date column
oxlWorksheet.Columns("G:G&quot.Select

'Run Text To Columns on it to fix dates
oxlApp.Selection.TextToColumns Destination:=Range("G1&quot, DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, Semicolon:=False, Comma:=False, space:=False, Other:=False, FieldInfo:=Array(1, 4)

'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


The trouble is that the Text To Columns line does not seem to work. When I run it inside Excel the column is updated and the values change into dates correctly. But when I run the macro's code in access nothing happens. Does anyone know why? Has anyone ever needed to do this themselves or know an alternatie solution to fixing the dates? Note that the solution needs to be made up of actual Access code as I do not have the option of adding in extra files, dlls, or using actual stored Excel macros, due to the fact I'm on a networked PC with restrictions. Please let me know if you have any ideas as this problem is driving me insane...

 

[simmsey]

Why arent you sorting by date in the Access report BEFORE you output to Excel?

 

[Sech]

I can do but because the user needs the ability to sort by any field at any time and there are hundreds of records this is irrelevant. Also there are many reports most of which have multiple date fields that can be sorted on. In other words the user needs maximum flexibility...

 

[jebry]

Hi!

Try it like this:

oxlWorksheet.Columns("G:G&quot.NumberFormat = "m/d/yy"

this should change column G to the short date format.

hth
Jeff Bridgham
bridgham@purdue.edu

 

[MichaelRed]

Another approach would be to create the 'Macro' action in the Excel sheet and have it execute on opening of the sheet.

If you only want it to run once, you could delete it (and the startup reference) after the initial execution.


MichaelRed
m.red@att.net

Searching for employment in all the wrong places

 

[Sech]

To Jeff - This doesn't work. When the Excel file is opened the date fields are actually text values until you click in them with the mouse, at which point they turn into dates.

To Michael - I cannot create macros and store them in Excel, and anyway the Excel spreadsheet is created new each time, not a template (See end of original post)

Thanks for your ideas...any others?

 

[MichaelRed]

Some CAN create the "Macro" in the NEW Excel spreadsheet. As Illustrated in the following Procedure. The second procedure simply generates the formatted "String" which is the code to be placed in the module generated by the first.

Sub Export_Excel_VBA(qualifier As String)
    Dim ExApp As Excel.Application
    Dim x As String

    Set ExApp = New Excel.Application
    ChDir "C:\My Documents\"

    x = basString4Excel

    With ExApp
        .Workbooks.Open FileName:=qualifier
        .ActiveWorkbook.VBProject.Name = _
        "Exported_VBA_" & Chr(34) & Left(qualifier, Len(qualifier) - 4) & Chr(34)
        .VBE.ActiveVBProject.VBComponents.Add (vbext_ct_StdModule)
        .VBE.CodePanes(1).CodeModule.AddFromString x
        .ActiveWorkbook.Save
        .Quit
    End With


End Sub

Public Function basString4Excel() As String

    Dim strX(26) As String
    Dim x As String
    Dim Idx As Integer

    strX(0) = "Public X As String"
    strX(1) = "Sub auto_open()"
    strX(2) = "on error resume next"
    strX(3) = "Call msg"
    strX(4) = "If X = ""Y"" Then"
    strX(5) = "    For i = 3 To 24"
    strX(6) = "        Application.VBE.SelectedVBComponent.CodeModule.DeleteLines 3"
    strX(7) = "    Next i"
    strX(8) = "End If"
    strX(9) = "End Sub"
    strX(10) = ""

    strX(11) = "Sub msg()"
    strX(12) = "    If ActiveSheet.Name = ""Qry_Data_Export"" Then"
    strX(13) = "        a = ""Welcome to the DBS Export File."""
    strX(14) = "        a = a & ""Please feel free to make any updates you wish;"""
    strX(15) = "        a = a & ""however, please do not..."""
    strX(16) = "        a = a & ""1. Delete Row 1"""
    strX(17) = "        a = a & ""2. Alter the Rec_# field (Column A)"""
    strX(18) = "        a = a & ""If you wish to mark a record for deletion,"""
    strX(19) = "        a = a & ""please place a 'XX' in the Rec_Type field (Column C)."""
    strX(20) = "        a = a & ""Do you wish to remove this message from this file?"""
    strX(21) = "        If MsgBox(a, vbYesNo, ""DBS - Import / Export File"") = 6 Then "

    strX(22) = "           X = Y"
    strX(23) = "        End If"
    strX(24) = "    End If"
    strX(25) = "End Sub"
    strX(26) = ""

    For Idx = 1 To 10
        x = x & strX(Idx) & vbCrLf
    Next Idx

    x = x & vbCrLf

    For Idx = 11 To UBound(strX)
        x = x & strX(Idx) & vbCrLf
    Next Idx

    basString4Excel = x

End Function

MichaelRed
m.red@att.net

Searching for employment in all the wrong places

 

[jebry]

Hi!

I have to agree with MichaelRed on this one. You should also be able to perform formatting in a new Excel spreadsheet. The reason you give for not being able to do these things is your security level. Maybe you should talk to your employer about how the level of access you have is affecting your ability to complete your work. If your access isn't going to change, then write code to do what you need and test it on your hard drive, then give the app to someone with the necessary access to do the work on the Network.

hth
Jeff Bridgham
bridgham@purdue.edu

 

[Sech]

I am very interested in the above two responses. If I can create a new maco inside Excel linked to the new sheet every time one is exported, and then run it automatically then this should solve the problem. But I need more assistance with the code, as I do not have much experience in linking Excel with Access. The code for the macro that I want to create is as follows:

Columns("G:G&quot.Select
Selection.TextToColumns Destination:=Range("G1&quot, DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo:=Array(1, 4)

How do I go about creating this module, linking it to a newly created sheet (Named data.xls), and running it within Excel, all using Access code?

If you can help me sort this out I will be eternally grateful! This is the best chance yet that I have seen for finding a solution....