'Export all charts from multiple Excel files in one folder
I want to create a macro for exporting all Excel charts from several workbooks in one folder. I'm a beginner in VBA and I need your help with the following code:
P.S. The code seems to work (I don't have errors) but does not export any graph to the selected folder. Could you, please, help me with some hints? I don`t know where is the problem. Thank you in advance! :)
Sub LoopAllExcelFilesInFolder()
'PURPOSE: To loop through all Excel files in a user specified folder and perform a set task on them (export all charts in one folder)
Dim wb As Workbook
Dim myPath As String
Dim myFile As String
Dim myExtension As String
Dim FldrPicker As FileDialog
Dim objChart As Excel.Chart
'Optimize Macro Speed
Application.ScreenUpdating = False
Application.EnableEvents = False
Application.Calculation = xlCalculationManual
'Retrieve Target Folder Path From User
Set FldrPicker = Application.FileDialog(msoFileDialogFolderPicker)
With FldrPicker
.Title = "Select A Target Folder"
.AllowMultiSelect = False
If .Show <> -1 Then GoTo NextCode
myPath = .SelectedItems(1) & "\"
End With
'In Case of Cancel
NextCode:
myPath = myPath
If myPath = "" Then GoTo ResetSettings
'Target File Extension (must include wildcard "*")
'myExtension = "*.xls*"
myExtension = "*.xlsx"
'Target Path with Ending Extention
myFile = Dir(myPath & myExtension)
'Loop through each Excel file in folder
Do While myFile <> ""
'Set variable equal to opened workbook
Set wb = Workbooks.Open(Filename:=myPath & myFile)
'Ensure Workbook has opened before moving on to next line of code
DoEvents
For Each objChart In wb.Charts
objChart.Export myPath & Left(wb.Name, Len(wb.Name) - 5) & "_" & objChart.Name & ".png"
Next objChart
For Each objSheet In wb.Worksheets
For Each objChartObject In objSheet.ChartObjects
With objChartObject.Chart
.Export myPath & Left(wb.Name, Len(wb.Name) - 4) & "_" & .Name & "png" '/export graphs with WorkbookName + _worksheet name
End With
Next
Next
'Save and Close Workbook
wb.Close SaveChanges:=True
'Ensure Workbook has closed before moving on to next line of code
DoEvents
'Get next file name
myFile = Dir
Loop
'Message Box when tasks are completed
MsgBox "Task Complete!"
ResetSettings:
'Reset Macro Optimization Settings
Application.EnableEvents = True
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
'Open the windows folder
Shell "Explorer.exe" & " " & myPath, vbNormalFocus
End Sub
Maybe is useful to mention that I wanted to adapt one of my oldest macro (this one export all the charts from a workbook in one folder. Now I need to export all the charts from multiple workbooks in one folder).
Sub ExportAllCharts()
'
' '
' This macro extracts all the graphs from an Excel document and imports them into the selected folder as .PNG images. '
'
' '
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Dim objShell As Object
Dim objWindowsFolder As Object
Dim strWindowsFolder As String
Dim objSheet As Excel.Worksheet
Dim objChartObject As Excel.ChartObject
Dim objChart As Excel.Chart
'Sheets.Select
'ActiveSheet.Select
'Select a Windows folder
Set objShell = CreateObject("Shell.Application")
Set objWindowsFolder = objShell.BrowseForFolder(0, "Select a Windows folder:", 0, "")
If Not objWindowsFolder Is Nothing Then
strWindowsFolder = objWindowsFolder.self.Path & "\"
''''''''''''''''''''''''''
' charts on chart sheets '
''''''''''''''''''''''''''
'For Each objChart In ThisWorkbook.Charts
For Each objChart In ActiveWorkbook.Charts
objChart.Export strWindowsFolder & Left(ActiveWorkbook.Name, Len(ActiveWorkbook.Name) - 5) & "_" & objChart.Name & ".png" '/export graphs with workbook name prefix + _ + worksheet name ( ex: WorkbookName_WorksheetName.png ---> OK)
Next objChart
'Open the windows folder
Shell "Explorer.exe" & " " & strWindowsFolder, vbNormalFocus
End If
End Sub
Sources
This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.
Source: Stack Overflow
| Solution | Source |
|---|
