• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 537
  • Last Modified:

Overwrite Spreadsheet in ACCESS Macro

Hi,

I have a Macro, it runs on AutoExec and i schedule it overnight.

It exports a spreadsheet after a query has run, it wont overwrite the spreadsheet though.

I have attached a screenshot of the macro. Is there a way i can instruct the macro to overwrite the spreadsheet?

Thanks
Seanys
Doc3.doc
0
Seamus2626
Asked:
Seamus2626
  • 3
  • 3
1 Solution
 
Rey Obrero (Capricorn1)Commented:
*change the Action part of the macro that export the query to a "RunCode" action
*create a Function in a regular module to handle the exporting of the query

function exportQuery()
'test if excel file exists

if dir(folderpath & "\excel.xls")<>"" then
  kill folderpath & "\excel.xls"
end if

'export code here

end function

* in the macro, place this to replace the Transferspreadsheet action

RunCode



Function Name  exportQuery()


0
 
Seamus2626Author Commented:
Hi Capricorn1, im quite a novice to this, ive put the below code in and i get the message

"Expected:Line number or label or statement or end of statement"

Have you a line of code for 'expoert code'?

Thanks
Seamus
Option Compare Database

Function exportQuery()
'test if excel file exists

If Dir(G:\Asset Services MI\Unmatched Merit\Balances & "\balances.xls") <> "" Then
  Kill G:\Asset Services MI\Unmatched Merit\Balances & "\balances.xls"
End If

'export code here

End Function

Open in new window

0
 
Rey Obrero (Capricorn1)Commented:
the folder path must be in " "

Function exportQuery()
'test if excel file exists

If Dir("G:\Asset Services MI\Unmatched Merit\Balances\balances.xls") <> "" Then
  Kill "G:\Asset Services MI\Unmatched Merit\Balances\balances.xls"
End If

'export code here

End Function

Open in new window



or you can use this

Function exportQuery()
'test if excel file exists
dim folderPath as string
folderPath="G:\Asset Services MI\Unmatched Merit\Balances"
If Dir(folderPath & "\balances.xls") <> "" Then
  Kill folderPath & "\balances.xls"
End If

'export code here

End Function

Open in new window

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.

 
Seamus2626Author Commented:
Thanks Capricorn1, i put the below into a module, hit run and nothing is happening, its not overwriting the file and then when i deleted the file, it doesnt create the file either.

Am i missing some code?

Thanks
Seamus
Option Compare Database

Function ExportQuery()
'test if excel file exists
Dim folderPath As String
folderPath = "G:\Asset Services MI\Unmatched Merit\Balances"
If Dir(folderPath & "\balances.xls") <> "" Then
  Kill folderPath & "\balances.xls"
End If
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel8, "Balances", "\\ukhibmdata02\rights\Asset Services MI\Unmatched Merit\Balances\Balances.xls", True, "balances"

End Function

Open in new window

0
 
Rey Obrero (Capricorn1)Commented:
you are not using the same folder path....

you are deleting a file from a different location and trying to export the query in a different location.
0
 
Seamus2626Author Commented:
Perfect!

Thanks for your patience!
0

Featured Post

Industry Leaders: 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!

  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now