Solved

VBA Delete Excel file before export

Posted on 2014-12-11
9
491 Views
Last Modified: 2014-12-12
I am needing to delete or clear the file.xlsx before export.

Function ExportALLRCPAGRReports()

DoCmd.TransferSpreadsheet acExport, , "Query-1", "\\path\file.xlsx"
DoCmd.TransferSpreadsheet acExport, , "Query-2", "\\path\file.xlsx"
DoCmd.TransferSpreadsheet acExport, , "Query-3", "\\path\file.xlsx"
DoCmd.TransferSpreadsheet acExport, , "Query-4", "\\path\file.xlsx"
DoCmd.TransferSpreadsheet acExport, , "Query-5", "\\path\file.xlsx"

MsgBox "Reports File Updated!"
End Function
0
Comment
Question by:CMILLER
  • 4
  • 4
9 Comments
 
LVL 24

Accepted Solution

by:
chaau earned 500 total points
ID: 40495056
To delete a file you need to use Kill function:

Function ExportALLRCPAGRReports()
Dim exportFile As String
exportFile = "\\path\file.xlsx"
If Len(Dir$(exportFile)) > 0 Then
     Kill exportFile
End If
DoCmd.TransferSpreadsheet acExport, , "Query-1", exportFile
DoCmd.TransferSpreadsheet acExport, , "Query-2", exportFile
DoCmd.TransferSpreadsheet acExport, , "Query-3", exportFile
DoCmd.TransferSpreadsheet acExport, , "Query-4", exportFile
DoCmd.TransferSpreadsheet acExport, , "Query-5", exportFile

MsgBox "Reports File Updated!"
End Function 

Open in new window

0
 
LVL 84
ID: 40495136
chaau has shown you how to delete the file, but wouldn't your multiple calls to TransferSpreadsheet result in errors, or overwrite your Export file (the "Export.xlsx file")?
0
 
LVL 24

Expert Comment

by:chaau
ID: 40495142
I think the multiple exports will create multiple sheets in the Excel file. Have not tested it, just assuming
0
 

Author Comment

by:CMILLER
ID: 40496307
I want it to overwrite. I want it to export the most current.

So far it is working great. Thanks.

I hate to ask but what is the code to have the export file open after all is exported.
0
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

 
LVL 24

Expert Comment

by:chaau
ID: 40497132
I'd use ShellExecute for it. It is a universal code that works for any type of documents, even for the URLs.
In the VBA module go to the very beginning (i.e. press Ctrl+Home) and add these lines:
Option Explicit

      Declare Function ShellExecute Lib "shell32.dll" Alias _
         "ShellExecuteA" (ByVal Hwnd As Long, ByVal lpOperation _
         As String, ByVal lpFile As String, ByVal lpParameters _
         As String, ByVal lpDirectory As String, ByVal nShowCmd _
         As Long) As Long

      Global Const SW_SHOWNORMAL = 1

Open in new window

And now, in your function add this line to open the file:
' put this line before the End Function :
ShellExecute(Application.hWndAccessApp, "Open", exportFile, "", "C:\", SW_SHOWNORMAL)

Open in new window

0
 

Author Comment

by:CMILLER
ID: 40497372
chaau,

I am getting an error when adding the following.

ShellExecute(Application.hWndAccessApp, "Open", exportFile, "", "C:\", SW_SHOWNORMAL)
End Function

Error: Compile error: Expected: =
0
 

Author Comment

by:CMILLER
ID: 40497384
I removed the parentheses and it works, is that correct.

ShellExecute Application.hWndAccessApp, "Open", exportFile, "", "C:\", SW_SHOWNORMAL
0
 
LVL 24

Expert Comment

by:chaau
ID: 40497393
Yes. It is correct. Sorry, inserted a vb code here instead of vba
0
 

Author Comment

by:CMILLER
ID: 40497401
Cool, Thanks.
0

Featured Post

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Direct Mail software 4 42
Auto Filter in Combo Box 7 32
MS Access XML Export Query Setup Multiple Tag Values 15 28
VBA code won't run Delete Query 5 20
The first two articles in this short series — Using a Criteria Form to Filter Records (http://www.experts-exchange.com/A_6069.html) and Building a Custom Filter (http://www.experts-exchange.com/A_6070.html) — discuss in some detail how a form can be…
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…

911 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now