?
Solved

VBA Delete Excel file before export

Posted on 2014-12-11
9
Medium Priority
?
657 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 2000 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 85
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
NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

 

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

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

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

This article describes two methods for creating a combo box that can be used to add new items to the row source -- one for simple lookup tables, and one for a more complex row source where the new item needs data for several fields.
Code that checks the QuickBooks schema table for non-updateable fields and then disables those controls on a form so users don't try to update them.
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
Suggested Courses

765 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