export to excel from acces and format

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have some code in an access button that exports some tables (actually
linked views) into an excel spreadsheet and saves it in a folder on the
server. I then run a formatting macro that is saved in my personal.xls
workbook. I would like the code in access to automatically run the
formatting macro in excel before saving the workbook.

I've learned how to run an outlook macro from excel by using a reference and
think there should be some similar way of doing this with access but I can't
seem to figure it out.

Thanks,
Billy
Dallas,TX


Here's the Access Code

Private Sub cmdAssocGenerator_Click()
Dim FullFileName, Table1, Table2, Table3, Table4 As String

Table1 = "dbo_Assoc10-Demographics&Volume&CB"
Table2 = "dbo_Assoc10-Equipment"
Table3 = "dbo_Assoc10-InvoiceMast"
Table4 = "dbo_Assoc10-InvoiceMast QA"
FullFileName = "n:\Data Warehouse\Dallas\Canada Ad Hocs\" &
txtFileName.Value & ".xls"

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, Table1,
FullFileName, Yes
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, Table2,
FullFileName, Yes
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, Table3,
FullFileName, Yes
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, Table4,
FullFileName, Yes




MsgBox "DONE!", vbOKOnly, "Exporting Finished"


End Sub


Here is the Excel Formatting Macro


Sub Canada_Assoc_Ad_Hoc_Format()
'
' Canada_Assoc_Ad_Hoc_Format Macro
' Formats Canada Ad HocAssoc Report
'


Sheets("dbo_Assoc10_Demographics_Volume").Select
With ActiveSheet.PageSetup
.LeftHeader = "CannedReportingMASSCanada.mdb"
.CenterHeader = Workbooks.Application.ActiveWorkbook.Name
.RightHeader = "&P" & " of " & "&N"
.LeftFooter = "N:\Data Warehouse\Dallas\Canada Ad Hocs"
.CenterFooter = ""
.RightFooter = "Created by " & Application.UserName & ", on &D"


.FirstPageNumber = xlAutomatic
End With



Range("A1", Range("IV1").End(xlToLeft)).Select

' Rows("1:1").Select
With Selection.Interior
.ColorIndex = 15
.Pattern = xlSolid
End With
Sheets("dbo_Assoc10_Equipment").Select
Range("A1", Range("IV1").End(xlToLeft)).Select
'Rows("1:1").Select
With Selection.Interior
.ColorIndex = 15
.Pattern = xlSolid
End With
Sheets("dbo_Assoc10_InvoiceMast").Select
Range("A1", Range("IV1").End(xlToLeft)).Select
'Rows("1:1").Select
With Selection.Interior
.ColorIndex = 15
.Pattern = xlSolid
End With
Sheets("dbo_Assoc10_InvoiceMast_QA").Select

Rows("1:1").Select
ActiveWindow.TabRatio = 0.853
ActiveWindow.ScrollWorkbookTabs Position:=xlFirst
Sheets("dbo_Assoc10_Demographics_Volume").Select
Rows("2:2").Select
ActiveWindow.FreezePanes = True
Sheets("dbo_Assoc10_Equipment").Select
Rows("2:2").Select
ActiveWindow.FreezePanes = True
Sheets("dbo_Assoc10_InvoiceMast").Select
Rows("2:2").Select
ActiveWindow.FreezePanes = True
Sheets("dbo_Assoc10_InvoiceMast_QA").Select
Rows("2:2").Select
ActiveWindow.FreezePanes = True
Range("A1", Range("IV1").End(xlToLeft)).Select
' Rows("1:1").Select

With Selection.Interior
.ColorIndex = 15
.Pattern = xlSolid
End With
Selection.AutoFilter
Cells.Select
Range("B1").Activate
Cells.EntireColumn.AutoFit
Range("C2").Select
ActiveWindow.SmallScroll Down:=0
Sheets("dbo_Assoc10_InvoiceMast").Select
Cells.Select
Cells.EntireColumn.AutoFit
Range("C2").Select
ActiveWindow.SmallScroll Down:=0
Sheets("dbo_Assoc10_Equipment").Select
Cells.Select
Cells.EntireColumn.AutoFit
Range("C2").Select
ActiveWindow.ScrollWorkbookTabs Sheets:=-1
Sheets("dbo_Assoc10_Demographics_Volume").Select
Cells.Select
Cells.EntireColumn.AutoFit
ActiveWindow.SmallScroll ToRight:=9
Columns("N:O").Select
Selection.NumberFormat = "0.00"
ActiveWindow.SmallScroll ToRight:=-9
Range("D2").Select



Sheets("dbo_Assoc10_InvoiceMast").Select
ColumnTotal "F"
ColumnTotal "G"
ColumnTotal "H"
ColumnTotal "I"
Sheets("dbo_Assoc10_InvoiceMast_QA").Select
ColumnTotal "H"
ColumnTotal "I"
ColumnTotal "J"
ColumnTotal "K"

End Sub

Sub ColumnTotal(ByVal strColumn As String)
Cells(Rows.Count, strColumn).End(xlUp).Offset(2, 0).Value = _
Application.Sum(Columns(strColumn))
End Sub
 
Billy,

There are neater ways of doing this but with the code you have in place;

In Access VBA add a reference to the Excel library (Tools - References -
Microsoft Excel X.X Object Library).

After your final Docmd.TransferSpreadsheet add the following;

Dim oXLS As New Excel.Application
Dim xlsWorkbook As Excel.Workbook

Set xlsWorkbook=oXLS.Workbooks.Open(FullFileName)

oXLS.Workbooks.Open "personal xls file path\personal.xls"

xlsWorkbook.Sheets(1).Cells(1, 1).Select

oXLS.Run "personal.xls!Canada_Assoc_Ad_Hoc_Format"

xlsWorkbook.Close SaveChanges:=True

oXLS.Quit

You need to open the personal.xls, as automated Excel (this way) from Access
does not open start-up and add-ins.

Regards,

Chris.
 
Back
Top