Prev: How can I get only visible cells using range.get_Value?
Next: how do I copy streaming data values into excel
From: Matthew Scheperle on 16 Feb 2010 12:04
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,
--
Matt Scheperle
mscheperle(a)gmail.com

 | 
Pages: 1
Prev: How can I get only visible cells using range.get_Value?
Next: how do I copy streaming data values into excel