import data from access table to excel pivot table - Enable Auto Refresh

okl · #1 02-01-2010, 06:41 AM

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

romperstomper · #2 02-01-2010, 07:24 AM

You can use the Kill command to delete an existing file, if that helps.

okl · #3 02-01-2010, 07:26 AM

more specific pls ?

romperstomper · #4 02-01-2010, 07:30 AM

Something like:

Code:

strFile = "C:\folder1\workbook_name.xls"
If Dir(strFile) <> "" then Kill strFile

okl · #5 02-01-2010, 07:37 AM

sorry, where should i insert this code into ?

im in Macro design mode, cant find the view code to copy into ??

okl · #6 02-01-2010, 07:46 AM

sorry. i got the code view .

okl · #7 02-01-2010, 07:54 AM

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

romperstomper · #8 02-01-2010, 07:58 AM

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

okl · #9 02-01-2010, 08:12 AM

no luck...still prompt to overwrite exit file

romperstomper · #10 02-01-2010, 08:23 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.

okl · #11 02-01-2010, 08:26 AM

setwarnings didn't suppress the prompt box...

romperstomper · #12 02-01-2010, 08:44 AM

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!

okl · #13 02-01-2010, 09:00 AM

in the Access->Tools->Macro->Convert Macros to Visual Basic

Then i copy and paste the code in right?

romperstomper · #14 02-01-2010, 09:20 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

okl · #15 02-01-2010, 09:26 AM

i just go macro design view and press on the run button?

Welcome to the Excel Forum

Forum Statistics