VBA script that opens TXT file and saves as CSV file

[User Name]

This code cycles through a list of names which represent folders. Each folder contains weather records. My goal is to go through each folder, open each of these files, do a small amount of formating (deleting first column) and then save the files as CSV format rather than TXT. The code seems to work up until I get to:

ActiveWorkbook.SaveAs Filename:=Filename2, FileFormat:=xlCSV, CreateBackup:=False

This line of code gives me the following message: "File cannot be accessed. The file may be corrupted, located on a server that is not responding, or read-only."

I've copied the save sequence in this script from the macro recorder. It works ok when I try to save the txt file as csv manually. There are two messages about losing formatting but ultimately a txt file gets saved as a csv file which is the goal. If I can automate these saves I'm golden. Any thoughts?

Private Sub CommandButton1_Click()
Dim I As Long
Dim Station As String
'Dim wbNew As Workbook

'Freeze on screen, events, calculations (speeding up)
With Application
    .ScreenUpdating = False
    .EnableEvents = False
    .Calculation = xlCalculationManual
End With

    For I = 2 To 249
        Station = Sheet2.Range("A" & I).Value

        Dim Year As Integer
        Dim Filename1 As String
        Dim Filename2 As String
        Dim LongYear As String
    
        Filename1 = "C:\NSRDB\TXT\"
        Filename2 = "C:\NSRDB\CSV\"
        
        
        Year = 61
        For Year = 61 To 90
        LongYear = 19 & Year
        
        Filename1 = Filename1 & Station & "\" & Station & "_" & Year & ".txt"
        Filename2 = Filename2 & Station & "\" & Station & "_" & LongYear

        
        
        Workbooks.OpenText Filename:=Filename1, Origin _
        :=437, StartRow:=1, DataType:=xlDelimited, TextQualifier:=xlDoubleQuote _
        , ConsecutiveDelimiter:=True, Tab:=False, Semicolon:=False, Comma:= _
        False, Space:=True, Other:=False, FieldInfo:=Array(Array(1, 9), Array(2, 1), _
        Array(3, 1), Array(4, 1), Array(5, 1), Array(6, 1), Array(7, 1), Array(8, 1), Array(9, 1), _
        Array(10, 1), Array(11, 1), Array(12, 1), Array(13, 1)), TrailingMinusNumbers:=True
        
        ActiveWorkbook.SaveAs Filename:=Filename2, _
        FileFormat:=xlCSV, CreateBackup:=False
        ActiveWorkbook.Save
        ActiveWindow.Close
    
        Next Year

    Next I
 
With Application
    .Calculation = xlCalculationAutomatic
    .CutCopyMode = False
    .DisplayAlerts = True
    .EnableEvents = True
    .ScreenUpdating = True
End With
 
End Sub

 

[xylopagus]

This may be a shot in the dark, but try adding lines "Application.DisplayAlerts = False" to the top of the macro and "Application.DisplayAlerts = True" to the bottom of the macro. This stops any warning messages from halting the macro.

I've used this to get past warnings about deleting sheets that "may contain data".

 

[User Name]

Not a bad suggestion. Definitely helps with debugging but the problem lies with the SaveAs line of code. If I remove that line and have it so that I open then close one file after another the code works fine - it even saves the small bit of formatting I added which is nice.

 

[User Name]

Meh... Turns out I was telling vba to save in a particular folder that didn't exist.

This

Filename2 = Filename2 & Station & "\" & Station & "_" & LongYear

Should have been this

Filename2 = Filename2 & Station & "_" & LongYear

Oh well... problem solved.