vba data conversion

Question

I have a macro that takes a file inside a folder, converts it from txt with fixed width to csv and saves it in another folder. I get about 400 to 450 files in this format for conversion, the macro got very large and divided into 4 parts. until ai ok pq works well, but there is a variation in the example files: last month I received the file djacy_txt and this month I did not receive, so when the macro arrives in the file djacy_txt and does not find it to tell me that there was an error because it did not find the file. would like to know how I do to put a (if) in the macro p that she does so:

if you find the file XXXX.txt do (conversion macro) or search for next file.

<
    Workbooks.OpenText Filename:="P:\MACRO P CONVERSÃO\VBA\BRUTO\djacy.txt", _
        Origin:=xlMSDOS, StartRow:=1, DataType:=xlFixedWidth, FieldInfo:=Array( _
        Array(0, 1), Array(5, 1), Array(12, 1), Array(20, 1), Array(29, 1), Array(31, 1), Array(76, _
        1), Array(87, 1), Array(92, 1), Array(93, 1), Array(104, 1), Array(107, 1), Array(113, 1), _
        Array(125, 1), Array(145, 1)), TrailingMinusNumbers:=True
    Columns("O:O").Select
    Selection.ClearContents
    Range("Q5").Select
      ActiveWindow.SmallScroll Down:=-18
    dirCopia = "P:\MACRO P CONVERSÃO\VBA\CONVERTIDO\"
    nomeCopia = "djacy"
ActiveWorkbook.SaveAs Filename:= _
dirCopia + nomeCopia, _
FileFormat:=xlCSV, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False
Workbooks("marcelo.csv").Close SaveChanges:=False
>

Answer 1

Hello! Yes, it is possible to insert a clause if to solve this case.

First, reading this part of the code you posted, I got the impression that you created a code block for each file name to be converted. To make the code faster, I created a repeating structure Do While...Loop to open and save a copy of the converted file according to the file name.

For this structure to work, in my spreadsheet, I made a tab "Search" of the spreadsheet with a list with the name of each file (I used the fictitious names A, B and C, which is only replaced by the name of your files without the extension).

To treat the error of not finding the file, I entered a clause if that skips a line in the spreadsheet if error 1004 (Runtime error, which occurs when an object is not identified).

The code is this below. You will need to overwrite the file paths according to the way the original files are saved on your PC and to which folder the copies should be saved:

Private Sub btBusca_Click()

'Declara as variáveis
Dim W           As Worksheet
Dim Arquivo     As String
Dim dirArquivo  As String
Dim Copia       As String
Dim dirCopia    As String

Application.ScreenUpdating = False

'Atribui valores as variávies
Set W = Sheets("Busca") 'Aba Busca, em que está a lista de arquivos
dirArquivo = "C:\Users\Admin\Documents\Stack Overflow\Arquivos\" 'Caminho onde os arquivos .txt estão salvos
dirCopia = "C:\Users\Admin\Documents\Stack Overflow\Cópias\" 'Caminho onde os arquivos .csv deverão ser salvos

'Esvazia a variável Arquivo
Arquivo = Empty

'Seleciona a célula A2 na aba Busca
W.Range("A2").Select

Application.DisplayAlerts = False

'Realiza a estrutura de repetição para abrir e converter cada arquivo .txt
Do While ActiveCell.Value <> ""

    Arquivo = ActiveCell.Value
    
    'No caso de erro, continua o código sem interrução
    On Error Resume Next
    
    'Abre o arquivo .txt especificado
    Workbooks.OpenText Filename:=dirArquivo & Arquivo & ".txt"
    
    'No caso de erro 1004 (Tempo de Execução), pula uma linha na aba Busca e continua o código
    If Err.Number = 1004 Then
    
        ActiveCell.Offset(1, 0).Select
    
    Else
    
        'Converte e salva o arquivo no formato .csv
        ActiveWorkbook.SaveAs Filename:=dirCopia & Arquivo & ".csv", FileFormat:=xlCSV
        ActiveWorkbook.Close SaveChanges:=True
        ActiveCell.Offset(1, 0).Select
    
    End If
    
Loop

Application.DisplayAlerts = True

Application.ScreenUpdating = True

MsgBox "Cópias criadas", vbOKOnly, "Processo Concluído!"

End Sub

The answer got big, but the code is fast and works.

Any problems, let me know.