Save as using a path from another Workbook

Akihitoc

New Member
Joined
Apr 11, 2016
Messages
16
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

Active Member
Joined
Jun 4, 2015
Messages
441
Code:
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

Active Member
Joined
Jun 4, 2015
Messages
441
Sorry, code above is wrong! Try:
Code:
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

New Member
Joined
Apr 11, 2016
Messages
16
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

Active Member
Joined
Jun 4, 2015
Messages
441
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 ?
VBA Code:
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

Active Member
Joined
Jun 4, 2015
Messages
441
Ok, I've made it a little bit more intelligent...

VBA Code:
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
 

Forum statistics

Threads
1,078,441
Messages
5,340,298
Members
399,364
Latest member
farhan11941234

Some videos you may like

This Week's Hot Topics

  • Problem with Radio Button's format control
    I am creating an employee evaluation template (a sample is below) Column A is the category Column B, C D, E and F will be ratings (unacceptable...
  • Last Display on userform to a Listbox
    [CODE=vba] lstdisplay.ColumnCount = 15 lstdisplay.RowSource = "A1:O600000" [/CODE] So when i do this it Displays everything on the sheet i am...
  • Rename and move files to a new location
    Dear all, I have an excel file with the following information. The actual file name is at column A but i want to rename it using the following...
  • Help with True/False Formula
    Hello! Am stumped how to fix this formula, in which my result returns 'True', but it should return False. =IF(AG2=True...
  • Clear extra characters from a provided range of cells
    Dear All, I have following code which gives me desired output to remove extra characters from a provided range. But it takes too much time when...
  • Help with Current and highest streaks
    Hi there, I've just joined the forum and this is my first post. I've already spent quite a bit of time searching the net and this forum for a...
Top