1

My goal is to open multiple excel files from input folder and convert them to .csv in output folder. I am facing minor issues where

  • code converts .xlsx to .csv , converts .xls to .csv but in output folder, it stores .csv and .xls files. I can't figure out why .xls files are also getting stored. I only wish to store .csv files
  • I only want to convert data from excel 'Sheet1' but the code is converting data from an active sheet. How to specify to convert data only from 'Sheet1'?
Option Explicit

Sub ImportMultipleCsvFile()

    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual
    Application.DisplayAlerts = False

    Dim InputCsvFile As Variant
    Dim InputFolder As String, OutputFolder As String

    InputFolder = "C:\Users\excel_format"
    OutputFolder = "C:\Users\csv_format"

    InputCsvFile = Dir(InputFolder & "\*.xl??")

    While InputCsvFile <> ""
        Workbooks.OpenText Filename:=InputFolder & "\" & InputCsvFile, DataType:=xlDelimited, Comma:=True

        ActiveWorkbook.SaveAs Filename:=OutputFolder & "\" & Replace(ActiveWorkbook.Name, ".xlsx", ".csv"), FileFormat:=xlCSV, CreateBackup:=False
        ActiveWorkbook.SaveAs Filename:=OutputFolder & "\" & Replace(ActiveWorkbook.Name, ".xls", ".csv"), FileFormat:=xlCSV, CreateBackup:=False

        ActiveWorkbook.Close
        InputCsvFile = Dir
    Wend
    Application.Calculation = xlCalculationAutomatic

End Sub
Improve this question
3
  • 1
    You have two .SaveAs lines - one to save as xlsx, and the other to save as xls, which is why you are getting the extra xls files created. Commented May 13, 2020 at 21:40
  • @Applecore If I removed the second .SaveAs, it was not converting the .xls files. Commented May 14, 2020 at 16:50
  • I am sure Applecore didn't mean to remove the second line... I think his/her point was, that you cannot have both ran if you don't want that extra .xls file created. That is why it is the way it is in my answer, so only one of them will ever be executed, based on the original filename condition... I hope you analyse the answer to death, when you get one from someone, so you know why things happen the way they happen so. Commented May 15, 2020 at 9:54

1 Answer 1

Reset to default
1

If you change this:

ActiveWorkbook.SaveAs Filename:=OutputFolder & "\" & Replace(ActiveWorkbook.Name, ".xlsx", ".csv"), FileFormat:=xlCSV, CreateBackup:=False
ActiveWorkbook.SaveAs Filename:=OutputFolder & "\" & Replace(ActiveWorkbook.Name, ".xls", ".csv"), FileFormat:=xlCSV, CreateBackup:=False

to that:

--- to remove VBA codeblocks from Workbook before saving them as .csv, I have used this stackoverflow answer, then I checked it to make sense by using the info from here ---

Dim StartWb As Workbook
Dim TempWb As Workbook

Set StartWb = ActiveWorkbook
Set TempWb = Application.Workbooks.Add
StartWb.Worksheets("Sheet1").Copy Before:=TempWb.Worksheets(1)

If TempWb.Worksheets.Count > 1 Then
    Do While (TempWb.Worksheets.Count > 1)
        TempWb.Worksheets(TempWb.Worksheets.Count).Delete
    Loop
End If

' ----- This is new to delete the codeblocks from your Sheets -----------
Dim Element As Object
For Each Element In TempWb.VBProject.VBComponents
    'For Each Item In Element.Collection ' This For loop wasn't needed at the and but I forgot it in
        Element.CodeModule.DeleteLines 1, Element.CodeModule.CountOfLines
    'Next   ' It has most likely thrown up Undeclared Variable error with Option Eplicit
Next
' -----------------------------------------------------------------------

If InStr(StartWb.Name, ".xlsx") Then
    TempWb.SaveAs Filename:=OutputFolder & "\" & Replace(StartWb.Name, ".xlsx", ".csv"), FileFormat:=xlCSV, CreateBackup:=False
ElseIf InStr(StartWb.Name, ".xls") Then
    TempWb.SaveAs Filename:=OutputFolder & "\" & Replace(StartWb.Name, ".xls", ".csv"), FileFormat:=xlCSV, CreateBackup:=False
End If

TempWb.Close (xlNo)

then you will only get your .csv file saved, as well as it will only contain that first sheet.

I would also put these:

Application.ScreenUpdating = True
Application.DisplayAlerts = True

at the end of your code before or after:

Application.Calculation = xlCalculationAutomatic

Also swap this:

ActiveWorkbook.Close

to that:

StartWb.Close (xlNo)

To remove code from workbooks by code you have to change settings in Excel: enter image description here

Improve this answer
Sign up to request clarification or add additional context in comments.

5 Comments

This is awesome, it works very well. I will accept your answer. However, the .xls files have macros and it's creating a compiling error - 'user defined type not defined'. So I need to find a way to remove the default macros from these files first and then run the code, then the task is completed. Thank you!
Thank you so much for the help. I am not a VBA coder but I am learning a lot about it since last 2 weeks. I have at least 57 tabs open to fix code blocks which are failing. Please don't take it otherwise. The code you added, failed, unfortunately. Run time error 1004: Programmatic access to VB Project is not trusted. Also 'Item' was not defined so I defined Item as an Object. I am now researching more about the error I faced. Thank you!
Thank you! The error got resolved. But ... with .xls files, its still not working. This is what I see: imgur.com/a/c7X0m3C
your code and recommendations have worked perfectly. I commented out the extra loop and yet getting the same error. I run the macro and the error (imgur.com/a/c7X0m3C) pops up.Until I hit 'OK' it stays there and once I hit OK it gives me the expected .csv output. However, I hit 'Help' and it said: 'Your organization's policies are preventing us from completing this action for you. For more info, please contact your help desk.' I don't think we can further do anything. I am satisfied with what I have for now as a solution. Please accept my sincere thank you for the help.
You're welcome... This is probably obvious to you, but I just have to ask: The error message seems to be about the line m_objHTMLDocument As HTMLDocument in Sheet2(Code). So does that mean there is no HTMLDocument built in type? But you say .xls is wat it isn't working with... Have you tried to debug the code by stepping through it while it is trying to execute with one of your .xls files?

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.