Update Excel Pivotcharts from Access?

pasquis

New member
Local time
Today, 04:36
Joined
Aug 15, 2007
Messages
5
Hi!

I don't know if I'm making it too difficult, but the thing is that I need to create a procedure to make some graphics with the results of a survey. The survey is introduced in a database, but due to my little skills with Access the only way I thought it would be easy to take the results was exporting the table to an excel file where the Pivottables where already defined. Ok, this is working quite good, the problem I have is to update the pivot tables. As there are around 60 different tables I wanted to know how to update them using VBA code.

Also my skills on VBA are really reduced, so all the procedure I've done is just copied from forums and so. But now, the only thing I could not manage is updating this from Access. Is it possible?
I write here under the code I am using. This works pretty good...

Code:
DoCmd.Hourglass (HourglassOn) 'Show timer working

'declare filename variables
Dim stTABLENAME As String
Dim stNEWTABLENAME As String
Dim stvillage As String
Dim stsubvillage As String

'set up village and subvillage name for the file
If Forms![baseline grafics]![village] = "" Then
    stvillage = "all_villages"
    Else
    stvillage = Forms![baseline grafics]![village]
End If
If Forms![baseline grafics]!subvillage = "" Then
    stsubvillage = ""
    Else
    stsubvillage = "_" & Forms![baseline grafics]![subvillage]
End If

'set up variables filename
stTABLENAME = CurrentProject.Path & "\not touch!\" & "predefined graphics" & ".xls"
stNEWTABLENAME = CurrentProject.Path & "\graphics\" & stvillage & stsubvillage & ".xls"
    
' Copy the file where graphics are created to the folder graphics
Set objFSO = CreateObject("Scripting.FileSystemObject")
objFSO.copyFile stTABLENAME, stNEWTABLENAME, True
Set objFSO = Nothing

' Export table to excel
DoCmd.SetWarnings False
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "Household_Hygienic_Baseline query", stNEWTABLENAME, , "baseline"
DoCmd.SetWarnings True

DoCmd.Hourglass (HourglassOff)

Thanks!
 

Users who are viewing this thread

Back
Top Bottom