Save as...

[whitoulias]

Sub Opendat()
Dim MyFolder As String
On Error GoTo Errorcatch
Dim MyFile As String
MyFolder = "C:\Documents and Settings\Xxxxx\Desktop\" 'put your folder here
MyFile = Dir(MyFolder & "\*.dat")
Do While MyFile <> ""
Workbooks.Open Filename:=MyFolder & "\" & MyFile
MyFile = Dir
Loop
ActiveWorkbook.SaveAs "report1", FileFormat:=xls, CreateBackup:=False

Errorcatch:
MsgBox Err.Description

End Sub

I have a .dat file which i open with the above code
The above code is run by another xls
The problem is that when i try to save the new xls i get the following message

Method 'Save as' of object '_Workbook' failed

Any ideas

THX

 

[VoG]

ry

ActiveWorkbook.SaveAs Filename:="report1.xls", FileFormat:=xlExcel8, CreateBackup:=False

 

[whitoulias]

ry

ActiveWorkbook.SaveAs Filename:="report1.xls", FileFormat:=xlExcel8, CreateBackup:=False

Vog Thx for your reply
I'm still getting the same message

 

[VoG]

This worked fine for me

Sub Opendat()
Dim MyFolder As String
On Error GoTo Errorcatch
Dim MyFile As String
MyFolder = "C:\Test" 'put your folder here
MyFile = Dir(MyFolder & "\*.dat")
Do While MyFile <> ""
    Workbooks.Open Filename:=MyFolder & "\" & MyFile
    MyFile = Dir
Loop
ActiveWorkbook.SaveAs Filename:=MyFolder & "\report1.xls", FileFormat:=xlExcel8, CreateBackup:=False
Errorcatch:
MsgBox Err.Description

End Sub

 

[whitoulias]

This worked fine for me

Sub Opendat()
Dim MyFolder As String
On Error GoTo Errorcatch
Dim MyFile As String
MyFolder = "C:\Test" 'put your folder here
MyFile = Dir(MyFolder & "\*.dat")
Do While MyFile <> ""
    Workbooks.Open Filename:=MyFolder & "\" & MyFile
    MyFile = Dir
Loop
ActiveWorkbook.SaveAs Filename:=MyFolder & "\report1.xls", FileFormat:=xlExcel8, CreateBackup:=False
Errorcatch:
MsgBox Err.Description

End Sub

I wish i could say the same thing

I tried to do it manually to see what happens. It seems that when making 'save as' manually the name of the file appears like this "filename.dat" and when i choose to save it as xls, excel is not capable of saving it as xls but as .dat

 

[VoG]

Try recording a macro when using Save AS and changing the Save As Type to .xls

 

[whitoulias]

Try recording a macro when using Save AS and changing the Save As Type to .xls

I'll give it a try
Thank you again

Do you think i might getting this because i'm trying save as from a different workbook?

 

[VoG]

I'll give it a try
Thank you again

Do you think i might getting this because i'm trying save as from a different workbook?

It worked for me from a different workbook.