error 1004 while saving worksheet

[Hans810]

Dear All,

Using VBA code as given below, I get an error message (error 1004, Method SaveAs of object_Workbook failed) while saving the second worksheet (marked with *** behind the code line).
It seems it has to do with saving twice in a row from the same Workbook.
At least that is what I have read on the MS site.

Any idea how to solve this or how to make a workaround?

Any help is appreciated.

Regards from The Netherlands


VBA Code START
Sub Save()

Sheets("Data").Select
locatie1 = (Range("O3").Value)
'locatie1=C:\Uren\Reguliere uren
Sheets("Import 1").Select
Application.DisplayAlerts = False
ActiveSheet.Copy
Columns(1).SpecialCells(xlCellTypeBlanks).EntireRow.Delete
ActiveWorkbook.SaveAs locatie1, xlTextWindows
ActiveWorkbook.Close
Application.DisplayAlerts = True

Sheets("Data").Select
locatie2 = (Range("O4").Value)
'locatie2 = "C:\Uren\Reguliere uren\Reguliere uren "&Gegevens!E1&"-"&Gegevens!C1&".txt"
Sheets("Gegevens").Select
Application.DisplayAlerts = False
ActiveSheet.Copy
Columns(1).SpecialCells(xlCellTypeBlanks).EntireRow.Delete
ActiveWorkbook.SaveAs locatie2, xlTextWindows ***
ActiveWorkbook.Close
Application.DisplayAlerts = True
Sheets("Import 1").Select
Range("A1").Select
End Sub

VBA Code END

 

[Norie]

What's the value of locatie2 when you get the error?

 

[Hans810]

error 1004, Method SaveAs of object_Workbook failed

 

[Hans810]

'locatie2 = "C:\Uren\Reguliere uren\Reguliere uren "&Gegevens!E1&"-"&Gegevens!C1&".txt"

 

[Norie]

That line is commented out, this is the line that sets the value of locatie2.

locatie2 = (Range("O4").Value)

Even if that line wasn't commented I don't think it would work.

The reason I say that is that the highlighted parts below appear to be cell references but VBA wouldn't see them as such.

'locatie2 = "C:\Uren\Reguliere uren\Reguliere uren "&Gegevens!E1&"-"&Gegevens!C1&".txt"

Where are you actually trying to get the filename from?

 

[Hans810]

from a worksheet with all the variables.
The funny thing is that the first one works!
And I have this problem only on one computer....very strange.

 

[Norie]

Have you checked the value of locatie2 when you get the error?

 

[Hans810]

: locatie2 : "C:\Uren\Reguliere uren\Reguliere uren Juni-15.txt" : Variant/String

that is what I get when the error occurs. (from local variables)

 

[Hans810]

Hi Norie,

I found the solution: there was a spelling error in the destination path ..........so silly.

Anyway thanks for your help. At least your comment forced me to rechecking the spelling.

Have a nice day.