CODEWITHSUNDEEP
excelvbaruntime-error
Pieter725
Pieter725

Reputation: 33

How to apply Worksheets.SaveAs function?

I am working on a script to export a range from my basesheet to a specific worksheet. (The names for the worksheets are checked and created with a while loop. If the name is already there, the content will be cleared and repasted.)

The last step is saving a range to a .txt file, but it gives

'Run-time error '1004'
Application-defined or object-defined' error

I searched for similar error but those couldn't fix it.

#UPDATE

I updated my code to show the changes after the suggestions made in the comments.

I get

Run-time error '438'
Object doesn't support this property or method

Option Explicit ' Force explicit variable declaration.

Sub test_wh()    
    Dim exportFolder As String
    Dim filedialog As filedialog
    Dim fd
   
    Set fd = Application.filedialog(msoFileDialogFolderPicker)
    
    With fd
        .Title = "Select folder for export wh and wg files"
        If .Show = True Then
            exportFolder = .SelectedItems(1)
        End If
    End With
    
     '%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%

    Dim nameSheet As String
    Dim baseSheet As String: baseSheet = "BASE"
    Dim actSheet As String
    Dim f As Integer
    Dim i As Integer
    Dim x As String
        
    actSheet = ActiveSheet.Name
    
    i = 30
    f = 0

    Do While i < 361
        x = "wh" & i

        If Len(nameSheet) < 5 Then
            nameSheet = "wh0" & i
        End If

        If DoesSheetExists(nameSheet) Then
            Worksheets(nameSheet).range("A1:B27").ClearContents
            Worksheets(baseSheet).range("AP22").Offset(f, 0).Resize(27, 1).Copy
            Worksheets(nameSheet).range("A1:A27").PasteSpecial xlPasteValues
            Worksheets(baseSheet).range("AQ22").Offset(f, 0).Resize(27, 1).Copy
            Worksheets(nameSheet).range("B1:B27").PasteSpecial xlPasteValues
        Else
            Sheets.Add(After:=Sheets("actSheet")).Name = nameSheet
            Worksheets(baseSheet).range("AP22").Offset(f, 0).Resize(27, 1).Copy
            Worksheets(nameSheet).range("A1:A27").PasteSpecial xlPasteValues
            Worksheets(baseSheet).range("AQ22").Offset(f, 0).Resize(27, 1).Copy
            Worksheets(nameSheet).range("B1:B27").PasteSpecial xlPasteValues
        End If

        '%%%%%%%%%%%%%%%%%%%%%%

        Dim lRow As Long
        Dim lCell As String
        Dim foldername As String
    
        lRow = Cells(Rows.Count, 2).End(xlUp).Row
        lCell = "B" & lRow
        foldername = exportFolder & "\" & nameSheet & ".txt"

        Worksheets(nameSheet).range("A1:" & lCell).SaveAs Filename:=foldername, FileFormat:=xlText, CreateBackup:=False

        Sheets(nameSheet).Activate
        i = i + 30
        f = f + 1
    Loop

    'Call wg_test
End Sub

Upvotes: 0

Views: 229

Answers (1)

Pieter725
Pieter725

Reputation: 33

The problem was solved by activating the preferred worksheet and then saving the active workbook. These lines were added:

Worksheets(nameSheet).Activate
ActiveWorkbook.SaveAs Filename:=foldername, FileFormat:=xlText, CreateBackup:=False

Upvotes: 1

Related Questions