Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
BACKUP file created when saving Excel Workbook with VBA
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Problem saving backup file with VBA | Excel Programming | |||
how to create backup excel file & protect workbook from deletion | Excel Discussion (Misc queries) | |||
backup file created | Excel Discussion (Misc queries) | |||
Saving multi-tab excel file created from comma delimited text file | Excel Programming | |||
Backup in Saving a file in Excel 2002 | Excel Discussion (Misc queries) |