Access 2k
I haven't yet seen anything really good on this question so I thought that I'd pose it myself.
I create a lot of queries as excel spreadsheets because they are far easier to sork with for clients etc and unfortunately, not everyone has access.
A write a lot of little functions similar to the snippet shown below for querying a table for information and copying all that information to a temp table and then I export the contents of the table to an excel spreadsheet.
Private Sub DailyItmsDisp_Click()
A = "SELECT tbl_RMADetails.DispatchDate, tbl_RMADetails.Part, tbl_RMADetails.Serial, " & _
"tbl_RMADetails.CtnID INTO tmp_DailyItemsDisp " & _
"FROM tbl_RMADetails " & _
"WHERE (((tbl_RMADetails.DispatchDate) Between " & _
"[Forms]![frm_ItemsDispatched]![DailyItemsDisp] And " & _
"[Forms]![frm_ItemsDispatched]![DailyItemsDisp] & ' 23:59') AND " & _
"((tbl_RMADetails.Flag)='6')) " & _
"ORDER BY tbl_RMADetails.Part"
DoCmd.SetWarnings False
DoCmd.RunSQL A
DoCmd.OutputTo acTable, "tmp_DailyItemsDisp", "MicrosoftExcel(*.xls)", "", True, ""
DoCmd.SetWarnings True
Step two, once the file is created. Is to run a macro in that I've created in excel to format the spreadsheet in terms of presentation. Below is the VBA code behind the macro in excel that I run.
Sub Format_weekly_dispatch()
'
' Format_weekly_dispatch Macro
' Macro recorded 28/09/2004 by Daemynn Walker
'
'
Rows("1:1").Select
Selection.Insert Shift:=xlDown
Selection.Insert Shift:=xlDown
Selection.Insert Shift:=xlDown
Range("A1").Select
ActiveCell.FormulaR1C1 = "Weekly Dispatches"
Range("A1").Select
With Selection.Font
.Name = "Arial"
.Size = 16
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = 1
End With
Selection.Font.Bold = True
Range("A4:C4").Select
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
Selection.Borders(xlEdgeLeft).LineStyle = xlNone
Selection.Borders(xlEdgeTop).LineStyle = xlNone
Selection.Borders(xlEdgeBottom).LineStyle = xlNone
Selection.Borders(xlEdgeRight).LineStyle = xlNone
Selection.Borders(xlInsideVertical).LineStyle = xlNone
Selection.Borders(xlInsideHorizontal).LineStyle = xlNone
Selection.Interior.ColorIndex = xlNone
Selection.Font.Bold = True
Columns("A:C").Select
Selection.ColumnWidth = 20.29
Range("A4:C4").Select
With Selection
.HorizontalAlignment = xlLeft
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.MergeCells = False
End With
Rows("3:3").Select
Selection.Insert Shift:=xlDown
Range("A3").Select
ActiveCell.FormulaR1C1 = "Date:"
Range("A3").Select
Selection.Font.Bold = True
Range("B3").Select
ActiveCell.FormulaR1C1 = "=TODAY()"
Range("B3").Select
With Selection
.HorizontalAlignment = xlLeft
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.MergeCells = False
End With
Range("A5:C5").Select
With ActiveSheet.PageSetup
.PrintTitleRows = "$5:$5"
.PrintTitleColumns = ""
End With
ActiveSheet.PageSetup.PrintArea = ""
With ActiveSheet.PageSetup
.LeftHeader = ""
.CenterHeader = ""
.RightHeader = ""
.LeftFooter = ""
.CenterFooter = "Page &P of &N"
.RightFooter = ""
.LeftMargin = Application.InchesToPoints(0.748031496062992)
.RightMargin = Application.InchesToPoints(0.748031496062992)
.TopMargin = Application.InchesToPoints(0.984251968503937)
.BottomMargin = Application.InchesToPoints(0.984251968503937)
.HeaderMargin = Application.InchesToPoints(0.511811023622047)
.FooterMargin = Application.InchesToPoints(0.511811023622047)
.PrintHeadings = False
.PrintGridlines = False
.PrintComments = xlPrintNoComments
.PrintQuality = 600
.CenterHorizontally = False
.CenterVertically = False
.Orientation = xlPortrait
.Draft = False
.PaperSize = xlPaperA4
.FirstPageNumber = xlAutomatic
.Order = xlDownThenOver
.BlackAndWhite = False
.Zoom = 100
End With
Range("A10").Select
End Sub
Is there a way to bridge both these two pieces of code so that I creating and export data into excel and then format the spreadsheet all at the press of a button?
I don't really want to use templates because my database is often sent through to other people to use. I find it enough of a hassle just getting the macro set up on their PC's alone.
This must have been done before, can anyone help me by telling me what code I should include between the 2 bits of code to join the whole lot together?
Rgrds Tadynn
I haven't yet seen anything really good on this question so I thought that I'd pose it myself.
I create a lot of queries as excel spreadsheets because they are far easier to sork with for clients etc and unfortunately, not everyone has access.
A write a lot of little functions similar to the snippet shown below for querying a table for information and copying all that information to a temp table and then I export the contents of the table to an excel spreadsheet.
Private Sub DailyItmsDisp_Click()
A = "SELECT tbl_RMADetails.DispatchDate, tbl_RMADetails.Part, tbl_RMADetails.Serial, " & _
"tbl_RMADetails.CtnID INTO tmp_DailyItemsDisp " & _
"FROM tbl_RMADetails " & _
"WHERE (((tbl_RMADetails.DispatchDate) Between " & _
"[Forms]![frm_ItemsDispatched]![DailyItemsDisp] And " & _
"[Forms]![frm_ItemsDispatched]![DailyItemsDisp] & ' 23:59') AND " & _
"((tbl_RMADetails.Flag)='6')) " & _
"ORDER BY tbl_RMADetails.Part"
DoCmd.SetWarnings False
DoCmd.RunSQL A
DoCmd.OutputTo acTable, "tmp_DailyItemsDisp", "MicrosoftExcel(*.xls)", "", True, ""
DoCmd.SetWarnings True
Step two, once the file is created. Is to run a macro in that I've created in excel to format the spreadsheet in terms of presentation. Below is the VBA code behind the macro in excel that I run.
Sub Format_weekly_dispatch()
'
' Format_weekly_dispatch Macro
' Macro recorded 28/09/2004 by Daemynn Walker
'
'
Rows("1:1").Select
Selection.Insert Shift:=xlDown
Selection.Insert Shift:=xlDown
Selection.Insert Shift:=xlDown
Range("A1").Select
ActiveCell.FormulaR1C1 = "Weekly Dispatches"
Range("A1").Select
With Selection.Font
.Name = "Arial"
.Size = 16
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = 1
End With
Selection.Font.Bold = True
Range("A4:C4").Select
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
Selection.Borders(xlEdgeLeft).LineStyle = xlNone
Selection.Borders(xlEdgeTop).LineStyle = xlNone
Selection.Borders(xlEdgeBottom).LineStyle = xlNone
Selection.Borders(xlEdgeRight).LineStyle = xlNone
Selection.Borders(xlInsideVertical).LineStyle = xlNone
Selection.Borders(xlInsideHorizontal).LineStyle = xlNone
Selection.Interior.ColorIndex = xlNone
Selection.Font.Bold = True
Columns("A:C").Select
Selection.ColumnWidth = 20.29
Range("A4:C4").Select
With Selection
.HorizontalAlignment = xlLeft
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.MergeCells = False
End With
Rows("3:3").Select
Selection.Insert Shift:=xlDown
Range("A3").Select
ActiveCell.FormulaR1C1 = "Date:"
Range("A3").Select
Selection.Font.Bold = True
Range("B3").Select
ActiveCell.FormulaR1C1 = "=TODAY()"
Range("B3").Select
With Selection
.HorizontalAlignment = xlLeft
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.MergeCells = False
End With
Range("A5:C5").Select
With ActiveSheet.PageSetup
.PrintTitleRows = "$5:$5"
.PrintTitleColumns = ""
End With
ActiveSheet.PageSetup.PrintArea = ""
With ActiveSheet.PageSetup
.LeftHeader = ""
.CenterHeader = ""
.RightHeader = ""
.LeftFooter = ""
.CenterFooter = "Page &P of &N"
.RightFooter = ""
.LeftMargin = Application.InchesToPoints(0.748031496062992)
.RightMargin = Application.InchesToPoints(0.748031496062992)
.TopMargin = Application.InchesToPoints(0.984251968503937)
.BottomMargin = Application.InchesToPoints(0.984251968503937)
.HeaderMargin = Application.InchesToPoints(0.511811023622047)
.FooterMargin = Application.InchesToPoints(0.511811023622047)
.PrintHeadings = False
.PrintGridlines = False
.PrintComments = xlPrintNoComments
.PrintQuality = 600
.CenterHorizontally = False
.CenterVertically = False
.Orientation = xlPortrait
.Draft = False
.PaperSize = xlPaperA4
.FirstPageNumber = xlAutomatic
.Order = xlDownThenOver
.BlackAndWhite = False
.Zoom = 100
End With
Range("A10").Select
End Sub
Is there a way to bridge both these two pieces of code so that I creating and export data into excel and then format the spreadsheet all at the press of a button?
I don't really want to use templates because my database is often sent through to other people to use. I find it enough of a hassle just getting the macro set up on their PC's alone.
This must have been done before, can anyone help me by telling me what code I should include between the 2 bits of code to join the whole lot together?
Rgrds Tadynn