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
Seamus2626Asked:
Who is Participating?
 
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
 
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
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
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
 
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
 
Seamus2626Author Commented:
Perfect!

Thanks for your patience!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.