M
Matthew Scheperle
I have a system set up that uses both Access and Excel 2007 so this, from
what I can tell, is an access and excel question.
My code works, however when it saves and closes the excel file that is
created it also creates a copy of the file called BACKUP .....
Is there another step I need to check for files that are open? The code
below is from a module in Access. It runs a module that is in the Excel
workbook listed below as well. I will place some of the excel code below the
access code. If you need more detailed info on the code let me know.
Access VBA:
Option Compare Database
Function rptCommitmentReport2042()
On Error GoTo rptCommitmentReport2042_Err
Dim directory As String
directory = InputBox("Name your file")
directory = "J:\Matt\encumbrances\" & directory & ".xlsx"
""" Runs some queries that creates tables, etc then finishes with exporting
the data"""
DoCmd.TransferSpreadsheet acExport, 10, "qryReport_2042", directory,
False, "Commitment"
DoCmd.TransferSpreadsheet acExport, 10, "tblSummaryData", directory,
False, "12monthTotal"
Dim xl As Object
Set xl = CreateObject("Excel.Application")
xl.Workbooks.Open "J:\Matt\encumbrances\AppropAllotment2042.xlsm"
xl.Run "Module1.summarize", directory
xl.ActiveWorkBook.Save
xl.Quit
rptCommitmentReport2042_Exit:
Exit Function
rptCommitmentReport2042_Err:
MsgBox Error$
Resume rptCommitmentReport2042_Exit
End Function
Excel VBA:
""" Some code here then the next part"""
Workbooks.Open Filename:=dir
Sheets("_12monthTotal").Select
expenditures = Range("B2")
expenditures = expenditures * -1
Sheets("Commitment").Select
Selection.Subtotal GroupBy:=3, Function:=xlSum, TotalList:=Array(6, 7, 8,
9), _
Replace:=True, PageBreaks:=False, SummaryBelowData:=True
'this block subtotals everything needed
Columns("K:L").Select
Selection.Delete Shift:=xlToLeft
Columns("O:X").Select
Selection.Delete Shift:=xlToLeft 'this block deletes
un-needed columns
Columns("J:J").Select
Selection.Delete Shift:=xlToLeft
Columns("K:M").Select
Selection.Delete Shift:=xlToLeft
Dim row As Long
row = pFindRowPos("Grand Total") 'finds the grand total row in order to
give values to the summary tab
""" More code to modify data, totals, calculations, etc... """
Thanks,
what I can tell, is an access and excel question.
My code works, however when it saves and closes the excel file that is
created it also creates a copy of the file called BACKUP .....
Is there another step I need to check for files that are open? The code
below is from a module in Access. It runs a module that is in the Excel
workbook listed below as well. I will place some of the excel code below the
access code. If you need more detailed info on the code let me know.
Access VBA:
Option Compare Database
Function rptCommitmentReport2042()
On Error GoTo rptCommitmentReport2042_Err
Dim directory As String
directory = InputBox("Name your file")
directory = "J:\Matt\encumbrances\" & directory & ".xlsx"
""" Runs some queries that creates tables, etc then finishes with exporting
the data"""
DoCmd.TransferSpreadsheet acExport, 10, "qryReport_2042", directory,
False, "Commitment"
DoCmd.TransferSpreadsheet acExport, 10, "tblSummaryData", directory,
False, "12monthTotal"
Dim xl As Object
Set xl = CreateObject("Excel.Application")
xl.Workbooks.Open "J:\Matt\encumbrances\AppropAllotment2042.xlsm"
xl.Run "Module1.summarize", directory
xl.ActiveWorkBook.Save
xl.Quit
rptCommitmentReport2042_Exit:
Exit Function
rptCommitmentReport2042_Err:
MsgBox Error$
Resume rptCommitmentReport2042_Exit
End Function
Excel VBA:
""" Some code here then the next part"""
Workbooks.Open Filename:=dir
Sheets("_12monthTotal").Select
expenditures = Range("B2")
expenditures = expenditures * -1
Sheets("Commitment").Select
Selection.Subtotal GroupBy:=3, Function:=xlSum, TotalList:=Array(6, 7, 8,
9), _
Replace:=True, PageBreaks:=False, SummaryBelowData:=True
'this block subtotals everything needed
Columns("K:L").Select
Selection.Delete Shift:=xlToLeft
Columns("O:X").Select
Selection.Delete Shift:=xlToLeft 'this block deletes
un-needed columns
Columns("J:J").Select
Selection.Delete Shift:=xlToLeft
Columns("K:M").Select
Selection.Delete Shift:=xlToLeft
Dim row As Long
row = pFindRowPos("Grand Total") 'finds the grand total row in order to
give values to the summary tab
""" More code to modify data, totals, calculations, etc... """
Thanks,