Run Excel Macro from Access, but Pass FileName to Excel Macr

[Joe4]

I found a few threads about posts about running Excel macros from inside Access VBA (like this link: http://support.microsoft.com/kb/177760/ ), but I have a little "twist" to my problem.

My Access application exports an Excel file. However the file needs some formatting done to it (that cannot be done in Access). The twist is that I allow the user to enter the file name of the file that is being created. So when I call this Excel macro to run, I also need to pass it the file name that I want it to run against.

How can I do this?

Thanks.

 

[Norie]

Why not just do all the code in Access?

 

[Joe4]

Why not just do all the code in Access?

Quote from Original Post:

However the file needs some formatting done to it (that cannot be done in Access)

The file requires multiple specialized header rows as well as some formatting which is much easier to do in Excel.

 

[Norie]

What I'm suggesting is automating Excel from Access.

If you do that you can do everything you can in Excel VBA in Access VBA using the Excel object model.

Then you wouldn't need to pass the filename to an Excel macro.

Believe me I've done this sort of thing before.

For example exporting fund information to a workbook with each fund having it's own worksheet.

Then creating multiple pivot tables and charts for each worksheet/fund.

 

[Joe4]

I have never done that. The code that I have seen in controlling Excel form Access always looks a little different than straight Access VBA or Excel VBA. Can you provide some example code?

What I esentially need to do is:
1. Insert blank rows
2. Insert text (I think this part should be easy)
3. Insert a SUM formula on one of the columns
4. Apply some number formatting

Thanks.

 

[Norie]

I don't think I've got anything at hand, but I'll have a dig about.

It probably sounds a bit daunting/complicated to do this sort of thing but it's actually rather easy especially if you are already familiar with VBA.

The way I would generally do things would be to develop the required code in the 2 different applications and then combine the results.

OK, I found some sample code that I helped somebody with over on UtterAccess.

Private Sub Command1_Click()
    CreateXL
End Sub
Sub CreateXL()
Dim strSQL As String
Dim qdf As Object
Dim I As Long
Dim strFilename As String
Dim resp
    strFilename = "C:\" & [Forms]![Form]![TC] & ".xls"
    If Dir(strFilename) <> "" Then
        resp = MsgBox("This group's import already exists." & vbCrLf & "Do you wish to replace it?", vbYesNo)
        If resp = vbYes Then
            Kill strFilename
        Else
            Exit Sub
        End If
    End If
    For I = 1 To 12
        strSQL = "SELECT  Table1.Customer, Table1.ZipCode, Table1.ItemType, Table1.ExpDate "
        strSQL = strSQL & "FROM Table2 INNER JOIN Table1 ON Table2.GroupCode = Table1.GroupCode "
        strSQL = strSQL & "WHERE Table1.GroupCode='" & [Forms]![Form]![TC] & "' AND Month(Table1.Expdate)= " & I
        strSQL = strSQL & " ORDER BY Table1.Customer"
        Set qdf = CurrentDb.CreateQueryDef(Format(DateSerial(2006, I, 1), "mmm"), strSQL)
        DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, qdf.Name, strFilename
        CurrentDb.QueryDefs.Delete qdf.Name
    Next I
    
    FormatWB strFilename
    
End Sub

Sub FormatWB(strFilename As String)
Dim xlApp As Object
Dim xlWB As Object
Dim xlWS As Object
    Set xlApp = CreateObject("Excel.Application")
    Set xlWB = xlApp.Workbooks.Open(strFilename)
    For Each xlWS In xlWB.Worksheets
        xlWS.Range("A1:L1").Font.Bold = True
        xlWS.Range("A:L").Columns.AutoFit
        xlWS.Range("1:1").Insert
        With xlWS.Range("A1")
            .Value = [Forms]![Form]![TC]
            .Font.Size = 24
            .Font.Bold = True
        End With
    Next xlWS
    xlWB.Close True
    DoCmd.Close ' Close Form
    
End Sub
Private Sub Command2_Click()
    DoCmd.Close ' Close Form
End Sub

If you want I'll try and find the actual thread where you can download an attachment.

 

[Joe4]

I could probably muddle through it and figure it out, but as I am in kind of a time crunch, I was hoping for something a little quicker and easier.

From the link I referenced in my question, it looks like it is pretty easy to open an Excel file and run a macro (and the Excel code is very straighforward). I was just hoping to pass the filename to that macro.

Due to time constraints, for the time being I may just take away their ability to choose the file name and "lock" it in. Then, I will know the file name and don't need to pass it to the Excel macro; I can simply hard-code it in there.

Then when I get some more time, I can play around with the code/suggestions you provided to make it more dynamic.

 

[Norie]

Here's a link to one of the threads over at UtterAccess.

 

[SydneyGeek]

I wonder if there's a workaround you could use:

1. In Access, create the file. Write the name of the created file to a text file which always lives in a defined location.
Here you could (a) delete everything in a scratch table (b) write the new name -- possibly the full path (c) use TransferText to write out to the file.
Or, something like this --

Private Sub WriteDummyText(sName As String)
    Dim oFile As String
    oFile = CurrentProject.Path & "\NewFile.txt"
    
    'sName is the file name you want to write.
    'it is passed from another routine
    
    On Error Resume Next
    Kill (oFile)
    On Error GoTo 0
    
    Open oFile For Output As #1
    Print #1, sName
    Close 1
       
End Sub

2. From Excel, open that file and extract the filename. Then process the new file.

Denis

 

[SydneyGeek]

To extract the filename, you could use code like this (courtesy of Jon Peltier, on TechTrax) --

Sub TextIODemoRead()
  Dim sFile As String
  Dim sText As String
  Dim iFileNum As Integer

  sFile = "The path to the text file"

  iFileNum = FreeFile
  Open sFile For Input As iFileNum
  Input #iFileNum, sText
  Close #iFileNum
  'do stuff here with the file:
  Workbooks.Open(sText)
  'etc...

End Sub

Denis