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

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
58,315
Office Version
  1. 365
Platform
  1. Windows
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.
 

Excel Facts

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,300
Office Version
  1. 365
Platform
  1. Windows
Why not just do all the code in Access?
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
58,315
Office Version
  1. 365
Platform
  1. Windows
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

Well-known Member
Joined
Apr 28, 2004
Messages
76,300
Office Version
  1. 365
Platform
  1. Windows
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

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
58,315
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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

Well-known Member
Joined
Apr 28, 2004
Messages
76,300
Office Version
  1. 365
Platform
  1. Windows
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.
Code:
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

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
58,315
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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

Well-known Member
Joined
Apr 28, 2004
Messages
76,300
Office Version
  1. 365
Platform
  1. Windows
Here's a link to one of the threads over at UtterAccess.
 

SydneyGeek

MrExcel MVP
Joined
Aug 5, 2003
Messages
12,251
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 --
Code:
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

MrExcel MVP
Joined
Aug 5, 2003
Messages
12,251
To extract the filename, you could use code like this (courtesy of Jon Peltier, on TechTrax) --
Code:
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
 

Forum statistics

Threads
1,136,654
Messages
5,677,012
Members
419,668
Latest member
DharmaK

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top