Macro to convert multiple files to text file

[Shack1993]

Hi,

I have a macro which currently allows me to select a folder contain several files, perform a task(line deletion) on all the files and save each one in turn. This works great but the file type I need to save as is a text file and currently my file type is an .spe file. I've looked at many threads which speak about single and multiple conversion on excel files to text but I'm not sure how to do this for my file type as it is not a usual one. To summarise what I'm trying to achieve:
* Choose folder containing several spe files
* perform task on each file
* save file with same name but as text file

Any help would be greatly appreciated. Here's what I have so far by using other sources:

Sub MACROTEST()'
' MACROTEST Macro
'


'
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 = True
        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 = "*.spe*"


'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
    
    'delete lines
    
    Range("A1:A40").Select
    Selection.Delete Shift:=xlUp
    Range("A15999:A16371").Select
    Range("A16371").Activate
    Selection.Delete Shift:=xlUp


    'Save and Close Workbook
      wb.Close SaveChange:=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 "Finished!"


ResetSettings:
  'Reset Macro Optimization Settings
    Application.EnableEvents = True
    Application.Calculation = xlCalculationAutomatic
    Application.ScreenUpdating = True


End Sub

All the best,
Shack

 

[Domenic]

Try replacing...

[COLOR=#574123]wb.Close SaveChange:=True[/COLOR]

with

    wb.SaveAs Filename:=Left(wb.FullName, InStrRev(wb.FullName, "[COLOR=#574123].spe[/COLOR]", , vbTextCompare) - 1) & ".csv", FileFormat:=xlCSV
    
    wb.Close SaveChanges:=False

 

[Shack1993]

Hi Domenic,

I changed the line of code as you suggested but what this does is open my file, perform the task an then saves as a new file but in excel format rather than a text file.

I tried changing ".csv" to ".txt" and also "xlCSV" to "xlTXT" but this just causes an error. I'm new to VBA but I assume this is an obvious error considering the change I made.

Anything else I could try here?

All the best,
Shack

 

[Domenic]

After changing the extension to .txt, try using xlTextWindows for the file format. You can get a list of available file formats in Excel help under xlFileFormat.

 

[rlv01]

It's likely some problem with the file name you are trying to save to. Try using a variable Fname to build the file name first so that you can inspect it in the debugger before using it with wb.SaveAs

Dim Fname as string
Fname = Left(wb.FullName, InStrRev(wb.FullName, ".")) & "txt"
wb.SaveAs Filename:=Fname, FileFormat:=xlTextMSDOS

or

Dim Fname as string
Fname = Left(wb.FullName, InStrRev(wb.FullName, ".")) & "txt"
wb.SaveAs Filename:=Fname, FileFormat:=xlTextWindows