Error - macro save as xlxs

[nasire]

Dear all,

it's the first time i'm using a macro in excel and i don't know VBA very well, yet. I need to save all files in some different folders from .dta to .xlxs. I opened the first file and create a new macro in the folder 'PERSONAL.xlsb' and tried with this program:

Sub m()



Dim sFileName As Str


sFileName = Dir(ActiveWorkbook.Path & "*.dta*")

Do While (Len(sFileName) > 0)

ActiveWorkbooks.SaveAs sFileName, xlsx
ActiveWorkboos.Close
sFileName = Dir
Loop

End Sub

But I visualize an error message that I try to translate in english:"compilation error user-defined type not defined".

After reading some posts, I checked 'Microsoft Visual Basic for Applications.....' in References, from Tools Menu but I'm having the same error.

Could you please help me to solve this issue?

Thanks,
Irene

 

[Fluff]

You've got a typo on this line

ActiveWorkboos.Close

You're missing a k. Amongst other things
That said you're not actually opening any of the files.

Untested but try

Sub m()

    Dim sFileName As String
    
    
    sFileName = Dir(ActiveWorkbook.Path & "*.dta*")
    
    Do While (Len(sFileName) > 0)
        Workbooks.Open sFileName
        ActiveWorkbook.SaveAs sFileName, FileFormat:=51
        ActiveWorkbook.Close
    sFileName = Dir
    Loop

End Sub

 

[nasire]

Thanks for your quickly answer

Yes, I was trying to do it without opening...like a sort of 'external' conversion but I think it's not possible.

By the way, I don't have any message error now but nothing happens when I run the macro...

 

[Fluff]

I missed a bit. Try this

Sub m()

    Dim sFileName As String
    
    
    sFileName = Dir(ActiveWorkbook.Path & "\*.dta*")
    
    Do While (Len(sFileName) > 0)
        Workbooks.Open sFileName
        ActiveWorkbook.SaveAs sFileName, FileFormat:=51
        ActiveWorkbook.Close
    sFileName = Dir
    Loop

End Sub

 

[nasire]

it works now but it cannot find the file

 

[Fluff]

it works now but it cannot find the file

Do you mean the macro can't find any files, or you can't find the renamed files?

 

[nasire]

the macro

 

[Fluff]

Are there files in the same folder as the active workbook

 

[nasire]

I mean there are only .dta original files in the folder, even after launch the macro. Do you think they are saved in some other folder probably?

 

[Fluff]

For the macro to work you need to open one of the files in the folder & then run the macro