Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

VBA Delete Excel file before export

Posted on 2014-12-11
9
Medium Priority
?
709 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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 

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

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

Question has a verified solution.

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

In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
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…
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.

610 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