Hi,
I have enable Refresh on Open for my excel pivot table, but user need to click "Enable Automatic Refresh" , only solution i came across is to change the registry setting. Which i dont have access to edit registry(admin disable the access).
Alternate solution i try to use Access macro to automate the process and use Outputto save it as a excel file A. Then use excel file B to update pivot table from excel file A.(as excel A data is always latest)
The problem is i will get "....A file name already exist...do you want to overwrite.." prompt.
Which defeat the automate process.
Any other solution to enable the automatic refresh on open the excel workbook?
Or Access can overwrite the exist file or save it as another file name with timestamp ?
Thanks
You can use the Kill command to delete an existing file, if that helps.
So long, and thanks for all the fish.
more specific pls ?
Something like:
Code:strFile = "C:\folder1\workbook_name.xls" If Dir(strFile) <> "" then Kill strFile
So long, and thanks for all the fish.
sorry, where should i insert this code into ?
im in Macro design mode, cant find the view code to copy into ??
sorry. i got the code view .
where should i place it ?
Code:'------------------------------------------------------------ ' Macro11 ' '------------------------------------------------------------ Function Macro11() strFile = "C:\Users\okl\Desktop\book.xls" If Dir(strFile) <> "" Then Kill strFile On Error GoTo Macro11_Err DoCmd.SetWarnings False DoCmd.OutputTo acTable, "sdf", "MicrosoftExcelBiff8(*.xls)", "C:\Users\okl\Desktop\book.xls", False, "", 0 Macro11_Exit: Exit Function Macro11_Err: MsgBox Error$ Resume Macro11_Exit End Function
That should be fine, but you may as well use the file variable in the OutputTo method too:
Code:Function Macro11() strFile = "C:\Users\okl\Desktop\book.xls" If Dir(strFile) <> "" Then Kill strFile On Error GoTo Macro11_Err DoCmd.SetWarnings False DoCmd.OutputTo acTable, "sdf", "MicrosoftExcelBiff8(*.xls)", strFile, False, "", 0 Macro11_Exit: Exit Function Macro11_Err: MsgBox Error$ Resume Macro11_Exit End Function
So long, and thanks for all the fish.
no luck...still prompt to overwrite exit file
Last edited by okl; 02-01-2010 at 08:19 AM.
I have to assume that it's not that code that is being run when the message appears then - the DoCmd.SetWarnings code should be suppressing the prompt already.
So long, and thanks for all the fish.
setwarnings didn't suppress the prompt box...
It works for me (always has, and I just tested again using your code, but with different file path). Put a breakpoint in the code (select a line and press f9) and then run it as normal just to make sure this is actually the code that is being run!
So long, and thanks for all the fish.
in the Access->Tools->Macro->Convert Macros to Visual Basic
Then i copy and paste the code in right?
Last edited by okl; 02-01-2010 at 09:14 AM.
Yes - how are you actually running the code?
You may also want to try this version:
Code:Function Macro11() strFile = "C:\Users\okl\Desktop\book.xls" If Dir(strFile) <> "" Then Kill strFile On Error GoTo Macro11_Err DoCmd.SetWarnings False DoCmd.OutputTo acTable, "sdf", acFormatXLS, strFile, False, "", 0 Macro11_Exit: Exit Function Macro11_Err: MsgBox Error$ Resume Macro11_Exit End Function
So long, and thanks for all the fish.
i just go macro design view and press on the run button?
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Reply With Quote
Bookmarks