Access Modules

[smashclash]

I use Access Macro's to spit out a few reports all from one database. I just noticed the module tab in Access and I have a question. Currently, I run my macro and various tables are created that are excel spreadsheets. After I run these macro's all the Excel sheets are open. I then have Macro's in excel for each report I run to format them the way I want.

My question is, can I put those excel module (macro's) into the Access database so when the reports are finished running they auto format themselves in access rather than having me format them in Excel? I do not do any formatting of the reports after Access spits them out. I simply click on the run macro in excel and that's it.

 

[smashclash]

What sort of Formatting are you trying to achieve? and when you say that you

use Access Macro's to spit out a few reports

are you actualy generating reports using Reports in Access or just outputting the values from a query?

Peter

I just OutputTo the reports through a query. No functions are actually run, just different reports come out of a couple monster sized tables.

 

[smashclash]

Here is what I'm basically doing again. I download a report a few times a month that has all of the variable data I'm going to use. There are also 2 other tables that are connected to some of the other reports. These just have prior year data or a description of an account number. The queries do not perform any type of calculation. They just sort the data in different ways. I then have a Macro written in access that I run that gives me all the different reports and asks where I want to save them. It is just an OutputTo of selected queries. I save each report in its' respective folder. Once the Access macro is finished I run an Excel macro on each report. Each report has a different Excel macro. Once that macro is completed, I save the report and move on to the next report. Then repeat the process until I have no other reports left.

So what I'm asking is, is there a way to have Access or Excel run the Excel macro's without having to phyically click on Tools->macro's->Run in Excel for each report? I assume this can be done through the use of an Access module? However, I do not have any expertise with Access modules at all and am a complete noob. I've self taught myself a lot of the visual basic knowledge I have in Excel and can understand most things but with this I think I need to be babied along.

Thanks for all your help.

 

[smashclash]

OK, if this can't work, is it possible to copy my VB code for my excel macro and paste it into an Access Module. If so, how do I get the module to run after the query that spits out a report is finished?

 

[bat17]

To run XL stuff from Access you will need to set a reference to XL, see one of the earlier replies for details.

In the code below replace the bits in bold with the appropriate names. This code assumes that you use just one XL file that contains several macros to do the formatting.

You run the code the same as you would in an XL module, ie F5

if you still have problems can you post the full code that you are using plus what error messages it generates.


Sub TestXL()
Dim appXL As Excel.Application
Dim wk As Excel.Workbook
Set appXL = New Excel.Application
' The path/name of the XL file that holds your macro goes here.
Set wk = appXL.Workbooks.Open("C:\a2\temp.xls")

'@@@@@@ Your code to run your reports goes here @@@

'then list all of the macros that you use to format data here.

appXL.Run "Macro1"
appXL.Run "Macro2"
appXL.Run "Macro3"
appXL.Run "Macro4"
appXL.Run "Macro5"
appXL.Run "Macro6"
appXL.Run "Macro7"

'finaly clean up behind yourself and quit excel
appXL.Quit
Set appXL = Nothing
Set wk = Nothing

End Sub

HTH

Peter

 

[smashclash]

To run XL stuff from Access you will need to set a reference to XL, see one of the earlier replies for details.

In the code below replace the bits in bold with the appropriate names. This code assumes that you use just one XL file that contains several macros to do the formatting.

You run the code the same as you would in an XL module, ie F5

if you still have problems can you post the full code that you are using plus what error messages it generates.


Sub TestXL()
Dim appXL As Excel.Application
Dim wk As Excel.Workbook
Set appXL = New Excel.Application
' The path/name of the XL file that holds your macro goes here.
Set wk = appXL.Workbooks.Open("C:\a2\temp.xls")

'@@@@@@ Your code to run your reports goes here @@@

'then list all of the macros that you use to format data here.

appXL.Run "Macro1"
appXL.Run "Macro2"
appXL.Run "Macro3"
appXL.Run "Macro4"
appXL.Run "Macro5"
appXL.Run "Macro6"
appXL.Run "Macro7"

'finaly clean up behind yourself and quit excel
appXL.Quit
Set appXL = Nothing
Set wk = Nothing

End Sub

HTH

Peter

You are correct in your assumption that there is one file that contains all the Excel macros.

From doing searches and trying to understand the replies so far in this thread I believe the way to reference the access module to excel is to simply ensure that the Microsoft Library 9.0 box is checked. Everytime I look at it, it has been checked. Looking at the code you posted it looks to me like each report will run all the different macro's on every report. I have a separate macro for each report.

Also, in your post it says to paste the code for the reports. I don't know how to find out what the code is. Right now my Access macro is just a list of OutputTo of selected queries. There are approx. 7 of them and they each have their own macro.

What I'm having the most trouble with is figuring out how access knows to run the excel macro. I understand I just need to check the box in the Access VBE but it seems like I'm missing something.

 

[smashclash]

Below is how my macro looks in Access in Design view.

Action:

OutputTo Agency (Action Arguements: Object Type: Query, Object name: Agency Report, Output format: *.xls, Auto Start: Yes.)

OutputTo Fringe (Action Arguements: Object Type: Query, Object name: Fringe Report, Output format: *.xls, Auto Start: Yes.)

etc. for about 5 more reports.

Each of these OutputTo queries creates a new excel workbook. In each workbook I run an exclusive macro in excel to change the format. All I'm trying to do is have Access run the Excel macro as it creates the report. It would just save me a little bit of time. Thanks.

 

[bat17]

OK. There is a difference between macro's and code in Access which is probably causing some of the confusion.

let see if I understand the situation now.
Currently you have a XL file open that contains your macros.
In Access you run the first macro which gives you a dialog box to save the new xl file and then opens the file.
With this new file still open you select the macro to format it with.
Then you save and close this file.
Back to Access, run the next macro and keep repeating this until they are all done.

Is this about it?

Can you paste the code from one of the XL macros here so we can see how it relates to the file it is formating please.

Peter

 

[smashclash]

OK. There is a difference between macro's and code in Access which is probably causing some of the confusion.

let see if I understand the situation now.
Currently you have a XL file open that contains your macros.
In Access you run the first macro which gives you a dialog box to save the new xl file and then opens the file.
With this new file still open you select the macro to format it with.
Then you save and close this file.
Back to Access, run the next macro and keep repeating this until they are all done.

Is this about it?

Can you paste the code from one of the XL macros here so we can see how it relates to the file it is formating please.

Peter

Everything is correct except all reports run from the Access Macro and then when all are finished running, I run an excel macro on each of them. The macro's are really long and i'll paste the smallest one now. I know there is probably a lot of repetitious or primitive items but I'm fairly new the VB stuff so bear with me.

Sub Outside_Services()
'
' Outside_Services Macro
' Macro recorded 10/15/2004 by 
'

'
    Cells.Select
    With Selection
        .VerticalAlignment = xlBottom
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .ShrinkToFit = False
        .MergeCells = False
    End With
    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
    Range("C2").Select
    Range(Selection, Selection.End(xlToRight)).Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Replace What:="", Replacement:="0", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False
    Selection.NumberFormat = "#,##0_);(#,##0)"
    Range("A1").Select
    Selection.EntireRow.Insert
    Selection.EntireRow.Insert
    Selection.EntireRow.Insert
    Selection.EntireRow.Insert
    Selection.EntireRow.Insert
    Selection.EntireRow.Insert
    Selection.EntireRow.Insert
    Selection.EntireRow.Insert
    Selection.EntireRow.Insert
    Selection.EntireRow.Insert
    Selection.EntireColumn.Insert
    Columns("C:C").Select
    Selection.Insert Shift:=xlToRight
    Columns("A:A").Select
    Selection.Insert Shift:=xlToRight
    Range("C12").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.TextToColumns Destination:=Range("C12"), DataType:=xlFixedWidth, _
        FieldInfo:=Array(Array(0, 1), Array(2, 1))
    Range("B12").Select
    ActiveCell.FormulaR1C1 = _
        "=IF(RC[1]=10,""C"",IF(RC[1]=20,""B"",IF(RC[1]=30,""M"",IF(RC[1]=40,""E"",IF(RC[1]=50,""N"",IF(RC[1]=60,""R"",""""))))))"
    Range("B12").Select
    Selection.Copy
    Range("C12").Select
    Selection.End(xlDown).Select
    Range("B250").Select
    Range(Selection, Selection.End(xlUp)).Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
    Range("E8").Select
    Selection.EntireColumn.Insert
    Columns("G:G").Select
    Selection.Insert Shift:=xlToRight
    Columns("I:I").Select
    Selection.Insert Shift:=xlToRight
    
    Columns("K:K").Select
    Selection.Insert Shift:=xlToRight
        
    
    
    Columns("M:M").Select
    Selection.Insert Shift:=xlToRight
    Columns("O:O").Select
    Selection.Insert Shift:=xlToRight
    Columns("Q:Q").Select
    Selection.Insert Shift:=xlToRight
    Columns("S:S").Select
    Selection.Insert Shift:=xlToRight
    Columns("U:U").Select
    Selection.Insert Shift:=xlToRight
    
    
    
    Rows("11:11").Select
    Selection.ClearContents
    Columns("C:C").Select
    Selection.EntireColumn.Hidden = True
    Columns("B:B").EntireColumn.AutoFit
    Columns("D:D").EntireColumn.AutoFit
    Columns("F:F").EntireColumn.AutoFit
    Columns("H:H").EntireColumn.AutoFit
    ActiveWindow.SmallScroll Down:=-45
    Columns("H:H").ColumnWidth = 9
    Columns("C:C").ColumnWidth = 9
    Columns("J:J").ColumnWidth = 9
    Columns("L:L").ColumnWidth = 9
    
    
    
    Columns("C:C").Select
    Selection.EntireColumn.Hidden = True
    ActiveWindow.SmallScroll ToRight:=8
    Range("N:N,P:P,R:R,T:T,V:V").Select
    Range("P1").Activate
    Selection.ColumnWidth = 10
    ActiveWindow.LargeScroll ToRight:=-1
    ActiveWindow.SmallScroll Down:=-6
    Rows("1:10").Select
    Selection.Font.Bold = True
    Range("A1").Select
    ActiveCell.FormulaR1C1 = "SOUTH JERSEY HOSPITAL"
    Range("A2").Select
    ActiveCell.FormulaR1C1 = "OUTSIDE SERVICES DETAIL"
    Range("A3").Select
    ActiveCell.FormulaR1C1 = "AS OF "
    Range("B10").Select
    ActiveCell.FormulaR1C1 = "Account"
    Range("F10").Select
    ActiveCell.FormulaR1C1 = "Description"
    Range("B1:R10").Select
    With Selection
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlBottom
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .ShrinkToFit = False
        .MergeCells = False
    End With
    Range("B10:D10").Select
    With Selection
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlBottom
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .ShrinkToFit = False
        .MergeCells = False
    End With
    Selection.Merge
    Range("A:A,E:E,G:G,I:I,M:M").Select
    Range("M1").Activate
    ActiveWindow.SmallScroll ToRight:=8
    Range("A:A,E:E,G:G,I:I,K:K,M:M,O:O,Q:Q,S:S,U:U").Select
    Range("O1").Activate
    Selection.ColumnWidth = 2
    ActiveWindow.LargeScroll ToRight:=-1
    Range("H10").Select
    ActiveCell.FormulaR1C1 = "2003"
    Range("H10").Select
    ActiveCell.FormulaR1C1 = "Actual"
    Range("J10").Select
    ActiveCell.FormulaR1C1 = "Actual"
    Range("L10").Select
    ActiveCell.FormulaR1C1 = "Budget"
    Range("N10").Select
    ActiveCell.FormulaR1C1 = "Variance"
    Range("H10:N10").Select
    Selection.Copy
    Range("P10").Select
    ActiveSheet.Paste
    Range("H8").Select
    Application.CutCopyMode = False
    ActiveCell.FormulaR1C1 = "2003"
    Range("J8:N8").Select
    With Selection
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlBottom
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .ShrinkToFit = False
        .MergeCells = False
    End With
    Selection.Merge
    ActiveCell.FormulaR1C1 = "2004"
    Range("P8").Select
    ActiveCell.FormulaR1C1 = "2003"
    Range("R8:V8").Select
    With Selection
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlBottom
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .ShrinkToFit = False
        .MergeCells = False
    End With
    Selection.Merge
    ActiveCell.FormulaR1C1 = "2004"
    Range("H5:N5").Select
    With Selection
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlBottom
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .ShrinkToFit = False
        .MergeCells = False
    End With
    Selection.Merge
    ActiveCell.FormulaR1C1 = "Month - to - Date"
    Range("P5:V5").Select
    With Selection
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlBottom
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .ShrinkToFit = False
        .MergeCells = False
    End With
    Selection.Merge
    ActiveCell.FormulaR1C1 = "Year - to - Date"
    Range("P5:V5,H5:N5,H8,J8:N8,P8,R8:V8").Select
    Range("P8").Activate
    Selection.Borders(xlDiagonalDown).LineStyle = xlNone
    Selection.Borders(xlDiagonalUp).LineStyle = xlNone
    Selection.Borders(xlEdgeLeft).LineStyle = xlNone
    With Selection.Borders(xlEdgeTop)
        .LineStyle = xlContinuous
        .Weight = xlThin
        .ColorIndex = xlAutomatic
    End With
    With Selection.Borders(xlEdgeBottom)
        .LineStyle = xlContinuous
        .Weight = xlThin
        .ColorIndex = xlAutomatic
    End With
    Selection.Borders(xlEdgeRight).LineStyle = xlNone
    Selection.Borders(xlInsideVertical).LineStyle = xlNone
    Range("B10:D10,F10,H10,J10,L10,N10,P10,R10,T10,V10").Select
    Range("R10").Activate
    Selection.Borders(xlDiagonalDown).LineStyle = xlNone
    Selection.Borders(xlDiagonalUp).LineStyle = xlNone
    Selection.Borders(xlEdgeLeft).LineStyle = xlNone
    Selection.Borders(xlEdgeTop).LineStyle = xlNone
    With Selection.Borders(xlEdgeBottom)
        .LineStyle = xlContinuous
        .Weight = xlMedium
        .ColorIndex = xlAutomatic
    End With
    Selection.Borders(xlEdgeRight).LineStyle = xlNone
    Selection.Borders(xlInsideVertical).LineStyle = xlNone
    
    
    
     Range("h12").Select
    FIRST_ROW = ActiveCell.Row
    Selection.End(xlDown).Select
    LAST_ROW = ActiveCell.Row
    Selection.Offset(2, 0).Select
    ActiveCell.FormulaR1C1 = "=SUM(R[" & FIRST_ROW - LAST_ROW - 2 & "]C:R[-1]C)"
    Selection.Borders(xlDiagonalDown).LineStyle = xlNone
    Selection.Borders(xlDiagonalUp).LineStyle = xlNone
    Selection.Borders(xlEdgeLeft).LineStyle = xlNone
    Selection.Borders(xlEdgeTop).LineStyle = xlNone
    With Selection.Borders(xlEdgeBottom)
        .LineStyle = xlDouble
        .Weight = xlThick
        .ColorIndex = xlAutomatic
    End With
    Selection.Borders(xlEdgeRight).LineStyle = xlNone
    Selection.Offset(-1, 0).Select
    Selection.Borders(xlDiagonalDown).LineStyle = xlNone
    Selection.Borders(xlDiagonalUp).LineStyle = xlNone
    Selection.Borders(xlEdgeLeft).LineStyle = xlNone
    With Selection.Borders(xlEdgeTop)
        .LineStyle = xlContinuous
        .Weight = xlThin
        .ColorIndex = xlAutomatic
    End With
    Selection.Borders(xlEdgeBottom).LineStyle = xlNone
    Selection.Borders(xlEdgeRight).LineStyle = xlNone
    
     Range("j12").Select
    FIRST_ROW = ActiveCell.Row
    Selection.End(xlDown).Select
    LAST_ROW = ActiveCell.Row
    Selection.Offset(2, 0).Select
    ActiveCell.FormulaR1C1 = "=SUM(R[" & FIRST_ROW - LAST_ROW - 2 & "]C:R[-1]C)"
    Selection.Borders(xlDiagonalDown).LineStyle = xlNone
    Selection.Borders(xlDiagonalUp).LineStyle = xlNone
    Selection.Borders(xlEdgeLeft).LineStyle = xlNone
    Selection.Borders(xlEdgeTop).LineStyle = xlNone
    With Selection.Borders(xlEdgeBottom)
        .LineStyle = xlDouble
        .Weight = xlThick
        .ColorIndex = xlAutomatic
    End With
    Selection.Borders(xlEdgeRight).LineStyle = xlNone
    Selection.Offset(-1, 0).Select
    Selection.Borders(xlDiagonalDown).LineStyle = xlNone
    Selection.Borders(xlDiagonalUp).LineStyle = xlNone
    Selection.Borders(xlEdgeLeft).LineStyle = xlNone
    With Selection.Borders(xlEdgeTop)
        .LineStyle = xlContinuous
        .Weight = xlThin
        .ColorIndex = xlAutomatic
    End With
    Selection.Borders(xlEdgeBottom).LineStyle = xlNone
    Selection.Borders(xlEdgeRight).LineStyle = xlNone
    
     Range("l12").Select
    FIRST_ROW = ActiveCell.Row
    Selection.End(xlDown).Select
    LAST_ROW = ActiveCell.Row
    Selection.Offset(2, 0).Select
    ActiveCell.FormulaR1C1 = "=SUM(R[" & FIRST_ROW - LAST_ROW - 2 & "]C:R[-1]C)"
    Selection.Borders(xlDiagonalDown).LineStyle = xlNone
    Selection.Borders(xlDiagonalUp).LineStyle = xlNone
    Selection.Borders(xlEdgeLeft).LineStyle = xlNone
    Selection.Borders(xlEdgeTop).LineStyle = xlNone
    With Selection.Borders(xlEdgeBottom)
        .LineStyle = xlDouble
        .Weight = xlThick
        .ColorIndex = xlAutomatic
    End With
    Selection.Borders(xlEdgeRight).LineStyle = xlNone
    Selection.Offset(-1, 0).Select
    Selection.Borders(xlDiagonalDown).LineStyle = xlNone
    Selection.Borders(xlDiagonalUp).LineStyle = xlNone
    Selection.Borders(xlEdgeLeft).LineStyle = xlNone
    With Selection.Borders(xlEdgeTop)
        .LineStyle = xlContinuous
        .Weight = xlThin
        .ColorIndex = xlAutomatic
    End With
    Selection.Borders(xlEdgeBottom).LineStyle = xlNone
    Selection.Borders(xlEdgeRight).LineStyle = xlNone
    
     Range("n12").Select
    FIRST_ROW = ActiveCell.Row
    Selection.End(xlDown).Select
    LAST_ROW = ActiveCell.Row
    Selection.Offset(2, 0).Select
    ActiveCell.FormulaR1C1 = "=SUM(R[" & FIRST_ROW - LAST_ROW - 2 & "]C:R[-1]C)"
    Selection.Borders(xlDiagonalDown).LineStyle = xlNone
    Selection.Borders(xlDiagonalUp).LineStyle = xlNone
    Selection.Borders(xlEdgeLeft).LineStyle = xlNone
    Selection.Borders(xlEdgeTop).LineStyle = xlNone
    With Selection.Borders(xlEdgeBottom)
        .LineStyle = xlDouble
        .Weight = xlThick
        .ColorIndex = xlAutomatic
    End With
    Selection.Borders(xlEdgeRight).LineStyle = xlNone
    Selection.Offset(-1, 0).Select
    Selection.Borders(xlDiagonalDown).LineStyle = xlNone
    Selection.Borders(xlDiagonalUp).LineStyle = xlNone
    Selection.Borders(xlEdgeLeft).LineStyle = xlNone
    With Selection.Borders(xlEdgeTop)
        .LineStyle = xlContinuous
        .Weight = xlThin
        .ColorIndex = xlAutomatic
    End With
    Selection.Borders(xlEdgeBottom).LineStyle = xlNone
    Selection.Borders(xlEdgeRight).LineStyle = xlNone
    
     Range("p12").Select
    FIRST_ROW = ActiveCell.Row
    Selection.End(xlDown).Select
    LAST_ROW = ActiveCell.Row
    Selection.Offset(2, 0).Select
    ActiveCell.FormulaR1C1 = "=SUM(R[" & FIRST_ROW - LAST_ROW - 2 & "]C:R[-1]C)"
    Selection.Borders(xlDiagonalDown).LineStyle = xlNone
    Selection.Borders(xlDiagonalUp).LineStyle = xlNone
    Selection.Borders(xlEdgeLeft).LineStyle = xlNone
    Selection.Borders(xlEdgeTop).LineStyle = xlNone
    With Selection.Borders(xlEdgeBottom)
        .LineStyle = xlDouble
        .Weight = xlThick
        .ColorIndex = xlAutomatic
    End With
    Selection.Borders(xlEdgeRight).LineStyle = xlNone
    Selection.Offset(-1, 0).Select
    Selection.Borders(xlDiagonalDown).LineStyle = xlNone
    Selection.Borders(xlDiagonalUp).LineStyle = xlNone
    Selection.Borders(xlEdgeLeft).LineStyle = xlNone
    With Selection.Borders(xlEdgeTop)
        .LineStyle = xlContinuous
        .Weight = xlThin
        .ColorIndex = xlAutomatic
    End With
    Selection.Borders(xlEdgeBottom).LineStyle = xlNone
    Selection.Borders(xlEdgeRight).LineStyle = xlNone
    
     Range("r12").Select
    FIRST_ROW = ActiveCell.Row
    Selection.End(xlDown).Select
    LAST_ROW = ActiveCell.Row
    Selection.Offset(2, 0).Select
    ActiveCell.FormulaR1C1 = "=SUM(R[" & FIRST_ROW - LAST_ROW - 2 & "]C:R[-1]C)"
    Selection.Borders(xlDiagonalDown).LineStyle = xlNone
    Selection.Borders(xlDiagonalUp).LineStyle = xlNone
    Selection.Borders(xlEdgeLeft).LineStyle = xlNone
    Selection.Borders(xlEdgeTop).LineStyle = xlNone
    With Selection.Borders(xlEdgeBottom)
        .LineStyle = xlDouble
        .Weight = xlThick
        .ColorIndex = xlAutomatic
    End With
    Selection.Borders(xlEdgeRight).LineStyle = xlNone
    Selection.Offset(-1, 0).Select
    Selection.Borders(xlDiagonalDown).LineStyle = xlNone
    Selection.Borders(xlDiagonalUp).LineStyle = xlNone
    Selection.Borders(xlEdgeLeft).LineStyle = xlNone
    With Selection.Borders(xlEdgeTop)
        .LineStyle = xlContinuous
        .Weight = xlThin
        .ColorIndex = xlAutomatic
    End With
    Selection.Borders(xlEdgeBottom).LineStyle = xlNone
    Selection.Borders(xlEdgeRight).LineStyle = xlNone
    
     Range("t12").Select
    FIRST_ROW = ActiveCell.Row
    Selection.End(xlDown).Select
    LAST_ROW = ActiveCell.Row
    Selection.Offset(2, 0).Select
    ActiveCell.FormulaR1C1 = "=SUM(R[" & FIRST_ROW - LAST_ROW - 2 & "]C:R[-1]C)"
    Selection.Borders(xlDiagonalDown).LineStyle = xlNone
    Selection.Borders(xlDiagonalUp).LineStyle = xlNone
    Selection.Borders(xlEdgeLeft).LineStyle = xlNone
    Selection.Borders(xlEdgeTop).LineStyle = xlNone
    With Selection.Borders(xlEdgeBottom)
        .LineStyle = xlDouble
        .Weight = xlThick
        .ColorIndex = xlAutomatic
    End With
    Selection.Borders(xlEdgeRight).LineStyle = xlNone
    Selection.Offset(-1, 0).Select
    Selection.Borders(xlDiagonalDown).LineStyle = xlNone
    Selection.Borders(xlDiagonalUp).LineStyle = xlNone
    Selection.Borders(xlEdgeLeft).LineStyle = xlNone
    With Selection.Borders(xlEdgeTop)
        .LineStyle = xlContinuous
        .Weight = xlThin
        .ColorIndex = xlAutomatic
    End With
    Selection.Borders(xlEdgeBottom).LineStyle = xlNone
    Selection.Borders(xlEdgeRight).LineStyle = xlNone
    
     Range("v12").Select
    FIRST_ROW = ActiveCell.Row
    Selection.End(xlDown).Select
    LAST_ROW = ActiveCell.Row
    Selection.Offset(2, 0).Select
    ActiveCell.FormulaR1C1 = "=SUM(R[" & FIRST_ROW - LAST_ROW - 2 & "]C:R[-1]C)"
    Selection.Borders(xlDiagonalDown).LineStyle = xlNone
    Selection.Borders(xlDiagonalUp).LineStyle = xlNone
    Selection.Borders(xlEdgeLeft).LineStyle = xlNone
    Selection.Borders(xlEdgeTop).LineStyle = xlNone
    With Selection.Borders(xlEdgeBottom)
        .LineStyle = xlDouble
        .Weight = xlThick
        .ColorIndex = xlAutomatic
    End With
    Selection.Borders(xlEdgeRight).LineStyle = xlNone
    Selection.Offset(-1, 0).Select
    Selection.Borders(xlDiagonalDown).LineStyle = xlNone
    Selection.Borders(xlDiagonalUp).LineStyle = xlNone
    Selection.Borders(xlEdgeLeft).LineStyle = xlNone
    With Selection.Borders(xlEdgeTop)
        .LineStyle = xlContinuous
        .Weight = xlThin
        .ColorIndex = xlAutomatic
    End With
    Selection.Borders(xlEdgeBottom).LineStyle = xlNone
    Selection.Borders(xlEdgeRight).LineStyle = xlNone
    
    
    Selection.Offset(4, 0).Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.EntireRow.Delete
    ActiveCell.Select
    
    
    Range("A3").Select
    With ActiveSheet.PageSetup
        .PrintTitleRows = "$1:$11"
        .PrintTitleColumns = ""
    End With
    ActiveSheet.PageSetup.PrintArea = ""
    With ActiveSheet.PageSetup
        .LeftHeader = ""
        .CenterHeader = ""
        .RightHeader = ""
        .LeftFooter = ""
        .CenterFooter = ""
        .RightFooter = ""
        .LeftMargin = Application.InchesToPoints(0.75)
        .RightMargin = Application.InchesToPoints(0.75)
        .TopMargin = Application.InchesToPoints(1)
        .BottomMargin = Application.InchesToPoints(1)
        .HeaderMargin = Application.InchesToPoints(0.5)
        .FooterMargin = Application.InchesToPoints(0.5)
        .PrintHeadings = False
        .PrintGridlines = False
        .PrintComments = xlPrintNoComments
        .PrintQuality = 600
        .CenterHorizontally = False
        .CenterVertically = False
        .Orientation = xlLandscape
        .Draft = False
        .PaperSize = xlPaperLetter
        .FirstPageNumber = xlAutomatic
        .Order = xlDownThenOver
        .BlackAndWhite = False
        .Zoom = False
        .FitToPagesWide = 1
        .FitToPagesTall = 59
    End With
End Sub

 

[bat17]

I have reworked the code now. The macro's in the Excell file need to be in a module not a code page behind a sheet for it to work. you can always cut and paste them to a module if needed.

This code goes in a module in Access. you will need to add the names of the queries that you are outputting in the code.

I have added a button between each sheet being updated to stop it running away with itself. once the first sheet has been updated and you have saved the changes and closed it just click OK in access and it should process the next one.

Sub TestXl()

Dim appXL As Excel.Application
Dim wk As Excel.Workbook
Dim bolGo As Boolean
Set appXL = New Excel.Application

' The path/name of the XL file that holds your macro goes here.
Set wk = appXL.Workbooks.Open("C:\a2\book1.xls")
appXL.Visible = True
'then start listing all of the macros that you use to format data here.

DoCmd.OutputTo acQuery, "QueryNameHere", "MicrosoftExcel(*.xls)", "", True, ""
appXL.Run ("book1.xls!Outside_Services") ' change this for the book name and Macro required

bolGo = MsgBox("Next Sheet", vbOKOnly)
DoCmd.OutputTo acQuery, "QueryNameHere", "MicrosoftExcel(*.xls)", "", True, ""
appXL.Run ("book1.xls!Outside_Services") ' change this for the book name and Macro required

bolGo = MsgBox("Next Sheet", vbOKOnly)
DoCmd.OutputTo acQuery, "QueryNameHere", "MicrosoftExcel(*.xls)", "", True, ""
appXL.Run ("book1.xls!Outside_Services") ' change this for the book name and Macro required

bolGo = MsgBox("Next Sheet", vbOKOnly)
DoCmd.OutputTo acQuery, "QueryNameHere", "MicrosoftExcel(*.xls)", "", True, ""
appXL.Run ("book1.xls!Outside_Services") ' change this for the book name and Macro required

bolGo = MsgBox("Next Sheet", vbOKOnly)
DoCmd.OutputTo acQuery, "QueryNameHere", "MicrosoftExcel(*.xls)", "", True, ""
appXL.Run ("book1.xls!Outside_Services") ' change this for the book name and Macro required

bolGo = MsgBox("Next Sheet", vbOKOnly)
DoCmd.OutputTo acQuery, "QueryNameHere", "MicrosoftExcel(*.xls)", "", True, ""
appXL.Run ("book1.xls!Outside_Services") ' change this for the book name and Macro required

bolGo = MsgBox("Next Sheet", vbOKOnly)
DoCmd.OutputTo acQuery, "QueryNameHere", "MicrosoftExcel(*.xls)", "", True, ""
appXL.Run ("book1.xls!Outside_Services") ' change this for the book name and Macro required

bolGo = MsgBox("Next Sheet", vbOKOnly)
DoCmd.OutputTo acQuery, "QueryNameHere", "MicrosoftExcel(*.xls)", "", True, ""
appXL.Run ("book1.xls!Outside_Services") ' change this for the book name and Macro required


'finaly clean up behind yourself and quit excel
appXL.Quit
Set wk = Nothing
Set appXL = Nothing
End Sub


I have only had a quick look at your Excel code but it only looks as if it is arranging layout. It may be possible to to your reports in Access unless you specificaly need them in XL. The code you have will make it hard to maintain your sheets when you need to make changes.

HTH

Peter

 

[smashclash]

awesome bat, I got the first sheet to work. Now i'm going to try and get the others to work. One more question, do I have to go into the module now and just hit the "play" button to run this or is there another way where someone won't have to go into the VB.

Also, I don't mean to be bothersome, but is there anyway to have a textbox come up that asks for a date? For example, on ALL of these reports I have to update cell A3 to say: "AS OF FEBRUARY 29, 2004". Obviously this will vary depending on the date. I wasn't sure if there was a way I can have access popup a text box and ask what date you want to put in and have that be put in on all the sheets automatically.

Thanks for your help so far. I'm so excited I'm going to stay late and get the other sheets working.