Odd problem exporting csv files from Excel.

[Mightyginger]

So I have a spreadsheet that saves closing levels to CSV files. It was working fine until I rebooted the other day and ever since then every CSV file it saves is empty. Not sure if company IT have somehow "upgraded" my Excel without me knowing. We are running 2003 11.8169.8172 SP3.

If I step through the code and then manually save the csv file then it works fine. Any thoughts guys? I'm stumped.

Sub SaveClosingSpreads()

Application.ScreenUpdating = False
Application.DisplayAlerts = False

ActiveSheet.Calculate

Dim Savepath As String

Dim ThisBook As Workbook
Set ThisBook = ActiveWorkbook
Savepath = "H:\tempfiles\"

''1m
Workbooks.Add
Sheets(1).Range("A1:A42").Value = ThisBook.Sheets("h.Publishing").Range("AT5:AT46").Value
Sheets(1).Range("B1").Value = "input"
Sheets(1).Range("B2:B42").Value = ThisBook.Sheets("h.Publishing").Range("AW6:AW46").Value
ActiveWorkbook.SaveAs FileName:=Savepath & "1m.csv", FileFormat:=xlCSV, CreateBackup:=False
ActiveWorkbook.Close

Application.ScreenUpdating = True
Application.DisplayAlerts = True

End Sub

 

[SkipVought]

hi,

You do not need ThisBook...

Sub SaveClosingSpreads()
    
    Application.ScreenUpdating = False
    Application.DisplayAlerts = False
    
    ActiveSheet.Calculate
    
    Dim Savepath As String

    Savepath = "H:\tempfiles\"
    
''1m
    With Workbooks.Add
        .Sheets(1).Range("A1:A42").Value = ThisWorkbook.Sheets("h.Publishing").Range("AT5:AT46").Value
        
        .Sheets(1).Range("B1").Value = "input"
        .Sheets(1).Range("B2:B42").Value = ThisWorkbook.Sheets("h.Publishing").Range("AW6:AW46").Value
        .SaveAs Filename:=Savepath & "1m.csv", FileFormat:=xlCSV, CreateBackup:=False
        .Close
    End With

    Application.ScreenUpdating = True
    Application.DisplayAlerts = True

End Sub

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]