Incorporating my VBA code (that saves all sheets from an excel workbook to CSV) into a looping VBA program (just have myself stumped!)

[reuben_rambo]

Hi all:

I'm hoping that one of the amazing VBA macro gurus can help me. I have two VBA programs that work, but can't seem to combine them.
I save all my VBA programs in PERSONAL.xlsmb

Would anyone be able to help combine both the VBA programs placed here?
I look forward to get some help (if possible). Thanks!

Best,
Reuben

[1] VBA program (saves all sheets from an excel workbook to CSV) - IT WORKS! (after alot of testing)

Sub ExportSheetsToCSV()
Dim xWs As Worksheet
Dim xcsvFile As String
Dim originalFileName As String
originalFileName = ActiveWorkbook.Path & "\" & Left(ActiveWorkbook.Name, InStr(ActiveWorkbook.Name, ".") - 1)
For Each xWs In Application.ActiveWorkbook.Worksheets
xWs.Activate
xcsvFile = ActiveWorkbook.Path & "\" & originalFileName & "_" & xWs.Name & ".csv"
Application.ActiveWorkbook.SaveAs Filename:=xcsvFile, _
FileFormat:=xlCSV, CreateBackup:=False
Next
End Sub


[2] VBA program (Loops through all excel files in a given folder) ... IT WORKS! .... obtained from Loop Through All Excel Files In A Given Folder

This VBA macro will let you select a folder path and loop through each of the Excel files in the folder. The following macro code

• Opens each Excel file within the user-selected folder
• Performs a task
• Saves the file
• Closes the workbook

Sub LoopAllExcelFilesInFolder()
'PURPOSE: To loop through all Excel files in a user specified folder and perform a set task on them
'SOURCE: www.TheSpreadsheetGuru.com

Dim wb As Workbook
Dim myPath As String
Dim myFile As String
Dim myExtension As String
Dim FldrPicker As FileDialog

'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*"

'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

'Change First Worksheet's Background Fill Blue
wb.Worksheets(1).Range("A1:Z1").Interior.Color = RGB(51, 98, 174)

'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

End Sub

 

[Anthony47]

Try calling Sub ExportSheetsToCSV from Sub LoopAllExcelFilesInFolder by adding the following 2 lines in the shown position:

'Change First Worksheet's Background Fill Blue
wb.Worksheets(1).Range("A1:Z1").Interior.Color = RGB(51, 98, 174)

Call ExportSheetsToCSV          '<<< INSERT THIS LINE HERE
Stop                            '<---- This is for debugging only

'Save and Close Workbook
wb.Close SaveChanges:=True

The macro will halt on the Stop line, so that you can check that the result is correct for the current file. After the check, continue the macro by hitting F5 from the vba window. After a few positive checks you may delete the Stop and complete the macro

 

[reuben_rambo]

Try calling Sub ExportSheetsToCSV from Sub LoopAllExcelFilesInFolder by adding the following 2 lines in the shown position:

'Change First Worksheet's Background Fill Blue
wb.Worksheets(1).Range("A1:Z1").Interior.Color = RGB(51, 98, 174)

Call ExportSheetsToCSV          '<<< INSERT THIS LINE HERE
Stop                            '<---- This is for debugging only

'Save and Close Workbook
wb.Close SaveChanges:=True

The macro will halt on the Stop line, so that you can check that the result is correct for the current file. After the check, continue the macro by hitting F5 from the vba window. After a few positive checks you may delete the Stop and complete the macro

Thanks Anthony! It's been bugging me (literally) for the last few hours as to how to incorporate both macros. You're an absolute legend. Thanks for the advice and help .... both macros work now and will save me heaps of time when converting tonnes of xlms files with tonnes of worksheets into CSV files (with appropriate naming convention)

cheers,
R

 

[Anthony47]

Thanks for the feedback
(I'm an apprentice, the legends are ten floors above)