Launching Excel from Access

[JesseH]

I am launching Excel from Access. I do it with a macro. The problem is that the macro contains the path to Excel, which may not be the same for all users. The ideal solution would be to launch Excel, regardless of the location on the users box, and execute "variable" macros at start up.

The mission is to gather the data using Access and create a pivot table in excel.

Thanks in Advance
JesseH

 

[davikokar]

Put this code on a button on click event:

'Define variables
Dim xlApp As Object
Dim Sht As Object
'Create Excel object
Set xlApp = CreateObject("Excel.Application")
xlApp.Workbooks.Add
xlApp.Visible = True
Set Sht = xlApp.ActiveWorkbook.Sheets(1)


Hope it helps

 

[JesseH]

karerda, can I add vba code to the Excel worksheet when I create it?

 

[davikokar]

What I know that you can do is to add information that you have in access tables and then formatting the excel worksheet, all through VBA.
Here is few example I used to loop through some tables in an access database and the values to an excel sheet with specific formatting:

' Loop through the a table and copy them to worksheet.         
            uRow = 4
            Do Until rs.EOF
            For i = 0 To rs.Fields.Count - 1

            'here is the reference to the worksheet
            Sht.Cells(uRow, i + 1).Value = rs(i)
            Next i
            rs.MoveNext
            uRow = uRow + 1
            Loop


' Another example of loop and copy 
            uRow2 = 2
            h = 3
            Do Until rs2.EOF
            Sht.Cells(uRow2, h).Value = rs2(i2)
            rs2.MoveNext
            h = h + 3
            Loop
            With Sht
            .Rows("2").NumberFormat = "hh:mm"
            End With


' Example of formatting the excelsheet
           With Sht
           .Name = "Timetable"
           .Rows("4:" & RecordQuantity).RowHeight = 36
           .Rows(2).Font.Bold = True
           .Columns(1).Font.Bold = True
           .Cells.Interior.ColorIndex = "2"
             Do Until b = RecordQuantity2 * 3 + 3 + 1
             .Range(.Cells(2, c), .Cells(2, b)).Merge
             b = b + 3
             c = c + 3
             Loop 
             Do Until d = RecordQuantity2 * 3 + 3
           .Range(.Cells(1, d), .Cells(RecordQuantity, d)).Interior.ColorIndex = "6"
            d = d + 3
            Loop
           .Columns("A").Autofit
           .Rows("2").HorizontalAlignment = 3
           .Rows("2").VerticalAlignment = 2          
           .Rows("4:" & RecordQuantity).VerticalAlignment = 2
            Do Until a = RecordQuantity2 * 3 + 3
            .Columns(a).ColumnWidth = 0.4
            .Columns(a - 1).ColumnWidth = 2.5
            .Columns(a + 1).ColumnWidth = 2.5
            a = a + 3
            Loop
            Sht.PageSetup.Orientation = 2 'xlLandscape
            End With

 

[davidmo]

I am trying to create a VBA that would run a query to open up as a Pivot Table and then add the appropriate fields in the Row Axis and the Column Axis. I can open it up as a pivot table but can't add the fields.

Below is the code, any ideas would be great. Thanks.

' Rebill_Query1
'
'------------------------------------------------------------
Function Rebill_Query1()
On Error GoTo Rebill_Query1_Err

' Opens the Rebill Query
DoCmd.OpenQuery "Rebilled Aged Summary", acViewPivotTable, acEdit
DoCmd.Requery ""


Rebill_Query1_Exit:
Exit Function

Rebill_Query1_Err:
MsgBox Error$
Resume Rebill_Query1_Exit

End Function



Public Sub PivotRows()
ViewPivotTable.AddFields (RowFields = "Work Status")
End Sub