VBA Copy a file as a different file extension-CodePudding

I am trying to build a data formatter where the user selects a file of type .xlsx and then I format it and save it as type .csv. I am needing to convert the xlsx file to a csv before I can format it. To do this, I couldn't find anything apart from opening the file, copying the used range to a worksheet on the original file, saving that worksheet as csv and then referencing that file. Despite a lack of elegance, this would work fine for the use case. However, I cannot seem to get the copying of the worksheet to be formatted down.

Here's what I'm trying for copying:

        Dim active As Worksheet
        Set active = ActiveSheet
        
        With Workbooks.Open(myFile)
            ActiveSheet.UsedRange.Copy
            active.Paste
            .Close SaveChanges:=False
        End With

This, in theory, should copy the data from the file being opened to the already opened file, but it doesn't.

CodePudding user response：

This works great for me:

As a sub:

Sub ConvertToCSV()
    
    Dim MyFile As String
    Dim WBtmp As Workbook
    
    Application.EnableEvents = False
    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual
    
    MyFile = "C:\Users\cameron\Documents\IDs.xlsx"
    Set WBtmp = Workbooks.Open(MyFile)
    With WBtmp
        .SaveAs Replace(MyFile, ".xlsx", ""), xlCSV
        .Close
    End With
    
    Application.EnableEvents = True
    Application.ScreenUpdating = True
    Application.Calculation = xlCalculationAutomatic
    
End Sub

Or as a function

Sub TestFunc()
    fConvertToCSV "C:\Users\cameron\Documents\IDs.xlsx"
End Sub
Function fConvertToCSV(MyFile As String)
    
    Dim WBtmp As Workbook
    
    Application.EnableEvents = False
    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual
    
    Set WBtmp = Workbooks.Open(MyFile)
    With WBtmp
        .SaveAs Replace(MyFile, ".xlsx", ""), xlCSV
        .Close
    End With
    
    Application.EnableEvents = True
    Application.ScreenUpdating = True
    Application.Calculation = xlCalculationAutomatic
    
End Function