Solved

VBA Delete Excel file before export

Posted on 2014-12-11
9
469 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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
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

Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
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 …
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

708 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

17 Experts available now in Live!

Get 1:1 Help Now