Solved

VBA Delete Excel file before export

Posted on 2014-12-11
9
582 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 4
9 Comments
 
LVL 25

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 25

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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

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
 
LVL 25

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 25

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

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

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

Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
The Windows Phone Theme Colours is a tight, powerful, and well balanced palette. This tiny Access application makes it a snap to select and pick a value. And it doubles as an intro to implementing WithEvents, one of Access' hidden gems.
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

740 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