B
BillyRogers
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
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