Save as using a path from another Workbook

[Akihitoc]

Hi Good day!

I have Workbook A save on a determined path

I opened Workbook B and I want to save it on the same Folder as Workbook A
The name that I want to use for Workbook B is located on same Workbook B on cell M1
I would like to save the file as 97-2003 Workbook

I have tried different ways but nothing works
Thanks!

 

[Paul Ked]

Sub Ked()
    Dim wbA As Workbook, wbPath As String, OpenPath As String
    wbPath = ThisWorkbook.Path
    With Application.FileDialog(msoFileDialogFilePicker)
        .AllowMultiSelect = False
        .Filters.Add "Excel Files", "*.xlsx; *.xlsm; *.xls; *.xlsb", 1
        .Show
        OpenPath = .SelectedItems.Item(1)
    End With
    If InStr(OpenPath, ".xls") = 0 Then
        MsgBox "That is not a valid Excel file.", , "Wrong file..."
        Exit Sub
    End If
    Workbooks.Open OpenPath
    ActiveWorkbook.SaveAs Filename:=wbPath & "\" & Range("M1") & ".xls", FileFormat:=xlExcel8
End Sub

 

[Paul Ked]

Sorry, code above is wrong! Try:

Sub Ked()
    Dim wbB As String, wbPath As String, OpenPath As String
    wbPath = ThisWorkbook.Path
    wbB = Range("M1")
    With Application.FileDialog(msoFileDialogFilePicker)
        .AllowMultiSelect = False
        .Filters.Add "Excel Files", "*.xlsx; *.xlsm; *.xls; *.xlsb", 1
        .Show
        OpenPath = .SelectedItems.Item(1)
    End With
    If InStr(OpenPath, ".xls") = 0 Then
        MsgBox "That is not a valid Excel file.", , "Wrong file..."
        Exit Sub
    End If
    Workbooks.Open OpenPath
    ActiveWorkbook.SaveAs Filename:=wbPath & "\" & wbB & ".xls", FileFormat:=xlExcel8
End Sub

 

[Akihitoc]

Thanks a lot Paul!
It worked very well, just a question... in some point of the code it request me to select the location of Workbook-A in order to save the Workbook-B
Is there a way to do this automatically

I don't know, maybe save the path of Workbook-A as string and then use it to save Workbook-B in the same path ?
Thanks again!

 

[Paul Ked]

No, it asks you to open the workbook you want to save with the file name in "M1", then it saves the workbook you just opened to the same path as the file you ran the sub from.

I don't know, maybe save the path of Workbook-A as string and then use it to save Workbook-B in the same path ?

wbPath = ThisWorkbook.Path

is the string you just mentioned.

How are you opening the workbook B now? How do we know which open workbook is the one with the path to save B to?

 

[Paul Ked]

Ok, I've made it a little bit more intelligent...

Sub SaveB()
    Application.ScreenUpdating = False
    Dim i&, ync As String, wbNm As Workbook
    If Workbooks.Count < 2 Then MsgBox "No other workbook open!", , "Error...": Exit Sub
    If Workbooks.Count > 2 Then
        For i = 1 To Workbooks.Count
            If Workbooks(i).Name <> ThisWorkbook.Name Then
                ync = MsgBox("Save " & Workbooks(i).Name & " as the value in M1?", vbYesNoCancel, "Please choose...")
                If ync = vbCancel Then Exit Sub
                If ync = vbYes Then Set wbNm = Workbooks(i): GoTo Sv
            End If
        Next
    Else
        If ThisWorkbook.Name = Workbooks(1).Name Then
            Set wbNm = Workbooks(2)
        Else
            Set wbNm = Workbooks(1)
        End If
    End If
Sv:
    On Error GoTo Xit
    wbNm.SaveAs ThisWorkbook.Path & "\" & Range("M1") & ".xls", FileFormat:=xlExcel8
    Exit Sub
Xit:
    MsgBox "No file saved!", , "Errored on save..."
End Sub

 

[Akihitoc]

It works!
Thanks a lot Paul!!

 

[Paul Ked]

You're welcome