Run Excel VBA from Access

[allenjasonbrown@gmail.com]

I have the following VBA code that works great in Excel. I would like to be able to run this code from within my Access DB. Is it possible?

(I dont even need to use the template. I just want to take a Master File and create individual excel files based on column A. Whenever column A changes, create a new file).

Sub Test()
Dim Sh   As Worksheet:       Set Sh = Worksheets("MasterFile_All")
Dim LR   As Long:            LR = Sh.Range("A" & Rows.Count).End(xlUp).Row
Dim Rng  As Range:           Set Rng = Sh.Range("A2:A" & LR)
Dim c    As Range
Dim List As New Collection
Dim Item As Variant
Dim WB   As Workbook
Application.ScreenUpdating = False
    
On Error Resume Next
    For Each c In Rng
        List.Add c.Value, CStr(c.Value)
    Next c
On Error GoTo 0
    Application.DisplayAlerts = False
    Set Rng = Sh.Range("A1:O" & LR)
    For Each Item In List
        Set WB = Workbooks.Add(template:=ThisWorkbook.Path & "\CLOSED ALL TEMPLATE.xlt")

        Rng.AutoFilter Field:=1, Criteria1:=Item
        Sh.UsedRange.SpecialCells(xlCellTypeVisible).Copy WB.Worksheets(1).Range("A5")
        Rng.AutoFilter
        With WB
            .SaveAs "C:\Open Projects\Reports\" & Item & "\June 2011\Individual Files_" & Item & ".xlsx"
            .Close
        End With
    Next Item
    Sh.Activate
    Application.DisplayAlerts = True
    Application.ScreenUpdating = True
End Sub

[allenjasonbrown@gmail.com]

26 views and not one response.....that is somewhat discouraging!

Let me see if I can ask a question in a different way. If I create a recordset, within another recordset........can I easily export results? Maybe using transferspreadsheet? Would prefer to use excel automation so I can add a header row but I am not sure how to incorporate that?

Example:
Recordset 1
select * from Master_Table
order by student
recordset 2
select * from Master_Table
where student = @recordset1_student

transferspreadsheet ..........."C:\Test\" & @recordset1_student & "test.xls"
end rs2
end rs1

Any help would be great!

[boblarson]

You can do what you want but probably like most I am kind of lost as to how to describe to you how to do it from Access. You can pretty much use the same code you are using in Excel with a few caveats.

The first is to read this about Excel objects in Access:
http://www.btabdevelopment.com/ts/excelinstance

The second is regarding your code question about recordsets.

Dim rst1 As DAO.Recordset
Dim rst2 As DAO.Recordset
Dim strSQL As String
Dim objXL As Object
 
Set objXL = CreateObject("Excel.Application")
 
Set rst1 = CurrentDb.OpenRecordset("Select Distinct student From MasterTable ORDER BY student")
 
Do Until rst1.EOF
   Set rst2 = Currentdb.OpenRecordset("Select * From MaterTable WHERE Student = " & Chr(34) & rst1!student & Chr(34) & " ORDER By Student")
      Do Until rst2.EOF
         objXL.Workbooks.Add
         objXL.Worksheets(1).Range("A1").CopyFromRecordset rst2
         objXL.ActiveWorkbook.SaveAs "C:\Test\" & rst1!student & "test.xls"
         objXL.ActiveWorkbook.Close
         rst2.MoveNext
      Loop
      rst2.Close
rst1.MoveNext
Loop
 
objXL.Quit
rst2.Close
rst1.Close
 
Set objXL = Nothing
Set rst2 = Nothing
Set rst1 = Nothing

That is the basic gist of it I believe. Not sure I hit it all but I hope so.