Controlling other Applications

[JimLecocq]

Hi All,

I have a database that produces information and runs reports. The reports have to go out in Excel but it appears you can only create templates if you are using html.

The Excel files have to be adjusted from time to time and I have some Macros in the Excel files to make those adjustments.

Is it possible to run the Macros in the Excel files from Access or is it possible to control what other applications do from Access?

Hope that is enough info. If not I will try to supply more.

 

[boblarson]

is it possible to control what other applications do from Access?

Yes, it's called the COM Object Model and there's a LOT you can control with Access, or Office, or VB, etc.

Here's an export sample from one of my databases (probably not optimized as I did this one quite a while ago):

    Dim objExcel As Excel.Application, xlWB As Workbook, xlWS As Worksheet
    Dim strCenter As String, strLName As String, strFName As String, strAdd1 As String
    Dim strAdd2 As String, strCity As String, strState As String, strZip As String
    Dim strPhone As String, strEmail As String, intCount As Integer, intRow As Integer
    Dim strAddress As String, strAddress1 As String, strRole As String
    Dim strFax As String
    
    Set objExcel = New Excel.Application
    Set xlWB = objExcel.Workbooks.Add
    Set xlWS = xlWB.Worksheets.Application.ActiveSheet
    With xlWS
        .Range("A1").Value = "Health Center Name"
        .Range("A1").Cells.Font.Bold = True
        .Range("B1").Value = "Last Name"
        .Range("B1").Cells.Font.Bold = True
        .Range("C1").Value = "First Name"
        .Range("C1").Cells.Font.Bold = True
        .Range("D1").Value = "Role"
        .Range("D1").Cells.Font.Bold = True
        .Range("E1").Value = "Address1"
        .Range("E1").Cells.Font.Bold = True
        .Range("F1").Value = "Address2"
        .Range("F1").Cells.Font.Bold = True
        .Range("G1").Value = "City"
        .Range("G1").Cells.Font.Bold = True
        .Range("H1").Value = "State"
        .Range("H1").Cells.Font.Bold = True
        .Range("I1").Value = "Zip Code"
        .Range("I1").Cells.Font.Bold = True
        .Range("J1").Value = "Phone"
        .Range("J1").Cells.Font.Bold = True
        .Range("K1").Value = "Email"
        .Range("K1").Cells.Font.Bold = True
        .Range("L1").Value = "FAX"
        .Range("L1").Cells.Font.Bold = True
        .Range("A1:L1").HorizontalAlignment = xlCenter
        .Range("A2").Select
    End With
    intCount = 1
    intRow = 2
    intPosition = 1
    intPosition2 = 1
    Do Until intCount = Me.lstSiteContacts.ListCount
        strLName = Me.lstSiteContacts.Column(0, (intRow - 1))
        strFName = Me.lstSiteContacts.Column(1, (intRow - 1))
        strRole = Me.lstSiteContacts.Column(2, (intRow - 1))
        strAddress1 = Me.lstSiteContacts.Column(3, (intRow - 1))
        strAddress2 = Me.lstSiteContacts.Column(4, (intRow - 1))
        strCity = Me.lstSiteContacts.Column(5, (intRow - 1))
        strState = Me.lstSiteContacts.Column(6, (intRow - 1))
        strZip = Me.lstSiteContacts.Column(7, (intRow - 1))
        strPhone = Me.lstSiteContacts.Column(8, (intRow - 1))
        strEmail = Me.lstSiteContacts.Column(9, (intRow - 1))
        strFax = Me.lstSiteContacts.Column(10, (intRow - 1))
        ' copies the Center Name
        With xlWS
            .Range("A" & CStr(intRow)).Value = Me.lstGranteeSearch.Column(1)
            ' copies the Contact Name
            .Range("B" & CStr(intRow)).Value = strLName
            .Range("C" & CStr(intRow)).Value = strFName
            ' copies the role
            .Range("D" & CStr(intRow)).Value = strRole
            ' copies the Address information -
            ' Address 1
            .Range("E" & CStr(intRow)).Value = strAddress1
            ' Address 2
            .Range("F" & CStr(intRow)).Value = strAddress2
            ' City
            .Range("G" & CStr(intRow)).Value = strCity
            ' State
            .Range("H" & CStr(intRow)).Value = strState
            ' Zip Code
            .Range("I" & CStr(intRow)).Value = strZip
            ' Phone
            .Range("J" & CStr(intRow)).Value = strPhone
            ' Email Address
            .Range("K" & CStr(intRow)).Value = strEmail
            ' FAX
            .Range("L" & CStr(intRow)).Value = strFax
        End With
        intCount = intCount + 1
        intRow = intRow + 1
    Loop
    With xlWS
        .Cells.Select
        .Cells.EntireColumn.AutoFit
        .Range("A1").Select
    End Select
    objExcel.WindowState = xlMaximized
    objExcel.Visible = True

 

[JimLecocq]

Thanks Bob,

Looks like I have a ton of stuff to work on now.


jim

 

[boblarson]

Thanks Bob,

Looks like I have a ton of stuff to work on now.


jim

Sure thing. It may look like a lot, but once you've done it a couple of times it really starts to make sense and is not really that hard to do. However, it can take a lot of coding, depending on what you want to actually do.

I'll give you a tip, though -

If you're not sure how to do something in the Excel Object model, just go into Excel and record a macro of what it is you want to do. Then, go to the VBA Editor in Excel and get that code. It takes a little modification to use in Access but you can almost write your code there, bring it over and have it work.

 

[aikea]

I am not sure whether this is only available in Access 2007. You can use "DoCmd.OutputTo" command to save a table or a query straight into Excel format.

 

[aikea]

I find the article here: OutputTo

In both 2003 and 2007

 

[boblarson]

It is in 2000 too.

 

[JimLecocq]

Thanks All,

I am using the "DoCmd.OutputTo" now. And I am temporarily running this system with that command. The problem is it transfers more data than I need and will not put it were I want it. I need to do much more than this cmd will allow. It looks like the Excel Object Model will do that. However, I do appreciate the input.

 

[aikea]

Create a query with the data you want to output, the output that query with "docmd.outputto" command.

 

[JimLecocq]

High Bob and aikea,

Thanks for the help from both of you. Once I put the question on the forum I got pushed to other things. I know about he docmd.outputto but I have used it lots of times but I need more control than that allows.

Bob,

In you code above you list something call 1stSiteContacts (and 1stGranteeSearch) is that a table. What I am attempting to do is move data in to an Excel workbook and control a lot of other things at the same time. I have figured out how to do a lot of this. I tried to enter Me.tblTicket.listcount and got an invalid use of keyword error.

I was trying to move columns from tblTickets to Excel. Maybe I don't need the listcount. If you get this and have any thought I would appreciate it.

 

[boblarson]

Bob,

In you code above you list something call 1stSiteContacts

lstSiteContacts is a listbox on the form.

(and 1stGranteeSearch) is that a table.

No, another listbox.

What I am attempting to do is move data in to an Excel workbook and control a lot of other things at the same time. I have figured out how to do a lot of this. I tried to enter Me.tblTicket.listcount and got an invalid use of keyword error.

A table doesn't have a listcount, but a listbox does. The listbox would have the result from the SQL string I set it's rowsource to and the code takes those displayed results from the list box and puts it into Excel.



I hope that helps.

 

[JimLecocq]

Thanks Bob

I think I know what I can do now.