VBA Cell reference instead of Workbook name reference

[willow1985]

I have the below code that references a workbook called: "OPS 041 Customer Concerns Rev 7"

However this workbook name may change in the future and instead of updating the code every time there is a change it would be easier to update a spreadsheet.

Is there a way to reference a specific cell for the workbook name?

The Macro is running from Workbook: Customer Concern - Warranty Request Log TEST

I would like to replace worbook name: "OPS 041 Customer Concerns Rev 7" with this reference instead: Workbooks("Customer Concern - Warranty Request Log TEST").Sheets("List").Range("M1")
Where M1 Contains the text: OPS 041 Customer Concerns Rev 7

Is there a way to do this? I get an error if I just replace one with the other.

Thank you!

Sub Complete_File()
'
' Complete_File Macro
'

'
Msg = "Your CC File will be finalized," & vbCrLf & "" & vbCrLf & " Do you wish to continue?"

    Ans = MsgBox(Msg, vbYesNo)

    Select Case Ans

        Case vbYes

Sheets("CC Database").Select
Range(Cells(Selection.Row, 16), Cells(Selection.Row, 16)).Select
Selection.Copy
    Sheets("List").Select
    Range("G13").Select
        Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False

Call Transfer_CC

'Open file Named: "OPS 041 Customer Concerns Rev 7.xls" from directory: Workbooks("Customer Concern - Warranty Request Log TEST").Sheets("List").Range("I5")
Dim wkb As Workbook, wkbFrom As Workbook
Dim fromPath As String

fromPath = Workbooks("Customer Concern - Warranty Request Log TEST").Sheets("List").Range("I5")

If Right(fromPath, 1) <> "\" Then fromPath = fromPath & "\"

Set wkb = ThisWorkbook
Set wkbFrom = Workbooks.Open(fromPath & “OPS 041 Customer Concerns Rev 7.xls”)

Windows("Customer Concern - Warranty Request Log TEST.xlsm").Activate
Range("G1:G13").Select
    Selection.Copy
Windows(“OPS 041 Customer Concerns Rev 7.xls”).Activate
Range("O2").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
'Save as Name: Workbooks("Customer Concern - Warranty Request Log TEST").Sheets("List").Range("G1") 'in same directory aka Workbooks("Customer Concern - Warranty Request Log TEST").Sheets("List").Range("I5"):

Dim Path As String
Dim filename As String
Path = Workbooks("Customer Concern - Warranty Request Log TEST").Sheets("List").Range("I5")
filename = Workbooks("Customer Concern - Warranty Request Log TEST").Sheets("List").Range("G1")

ActiveWorkbook.SaveAs filename:=Path & filename & ".xls", FileFormat:=xlNormal

Kill Workbooks("Customer Concern - Warranty Request Log TEST").Sheets("List").Range("I7")

   Case vbNo
        GoTo Quit:
    End Select

Quit:
End Sub

Private Sub Transfer_CC()
Dim FSO
Dim Source_File As String
Dim Source_Folder As String
Dim Destination_Folder As String

Source_File = “OPS 041 Customer Concerns Rev 7.xls”
Source_Folder = "\\DAVINCI-1\USERDATA\carla.fisher\Desktop\Customer Concerns\"
Destination_Folder = Workbooks("Customer Concern - Warranty Request Log TEST").Sheets("List").Range("I5")
Set FSO = CreateObject("Scripting.FileSystemObject")
FSO.CopyFile (Source_Folder & Source_File), Destination_Folder, True

End Sub

 

[davesexcel]

Workbooks("Customer Concern - Warranty Request Log TEST").Sheets("List").Range("M1")

Try this, not sure if I missed any brackets or not, or the correct wb suffex
Workbooks(("Customer Concern - Warranty Request Log TEST.xlsx").Sheets("List").Range("M1").value)

 

[willow1985]

Try this, not sure if I missed any brackets or not, or the correct wb suffex
Workbooks(("Customer Concern - Warranty Request Log TEST.xlsx").Sheets("List").Range("M1").value)

I think there is something missing, I get a compile error with this.

I removed 2 brackets and that fixed the compile error but it still does not work. I get an error on line:

Source_File = Workbooks("Customer Concern - Warranty Request Log TEST.xlsx").Sheets("List").Range("M1").Value

 

[davesexcel]

Workbooks("Customer Concern - Warranty Request Log TEST.xlsx").Sheets("List").Range("M1").value)
Wrong number of brackets?

 

[davesexcel]

possibly use a msgbox to get the value to see if the code works.

msgbox workbooks("Customer Concern - Warranty Request Log TEST.xlsx").Sheets("List").Range("M1").value

 

[willow1985]

Why would I want a msgbox?

Still get an error with msg box. expected ) or separator

 

[davesexcel]

Just to test if you get the correct value.

 

[davesexcel]

okay, I think I was on to it, then somehow I ended up in a different direction
"Workbooks("Customer Concern - Warranty Request Log TEST").Sheets("List").Range("M1")"
you have a workbook named
"Customer Concern - Warranty Request Log TEST" possibly .xlsx or change to .xlsm
and in range M1 sheets("List") you have another workbook name.
workbooks(workbooks("Customer Concern - Warranty Request Log TEST.xlsx").sheets("List").range("M1").value)

 

[willow1985]

Customer Concern - Warranty Request Log TEST is an xlsm sheet.

and the code:

workbooks(workbooks("Customer Concern - Warranty Request Log TEST.xlsx").sheets("List").range("M1").value)

Gives a compile error.

I need a cell reference in 3 locations of the code. I will re-post the code with markers 'HERE to show the 3 areas.
I need to replace "OPS 041 Customer Concerns Rev 7.xls" with a cell reference that has "OPS 041 Customer Concerns Rev 7 "in text.

The cell reference is: Workbooks("Customer Concern - Warranty Request Log TEST").Sheets("List").Range("M1")

Sheets("CC Database").Select
Range(Cells(Selection.Row, 16), Cells(Selection.Row, 16)).Select
Selection.Copy
    Sheets("List").Select
    Range("G13").Select
        Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False

Call Transfer_CC

'Open file Named: "OPS 041 Customer Concerns Rev 7.xls" from directory: Workbooks("Customer Concern - Warranty Request Log TEST").Sheets("List").Range("I5")
Dim wkb As Workbook, wkbFrom As Workbook
Dim fromPath As String

fromPath = Workbooks("Customer Concern - Warranty Request Log TEST").Sheets("List").Range("I5")

If Right(fromPath, 1) <> "\" Then fromPath = fromPath & "\"

Set wkb = ThisWorkbook
'HERE
Set wkbFrom = Workbooks.Open(fromPath & "OPS 041 Customer Concerns Rev 7.xls")

Windows("Customer Concern - Warranty Request Log TEST.xlsm").Activate
Range("G1:G13").Select
    Selection.Copy
'HERE
Windows("OPS 041 Customer Concerns Rev 7.xls").Activate
Range("O2").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
'Save as Name: Workbooks("Customer Concern - Warranty Request Log TEST").Sheets("List").Range("G1") 'in same directory aka Workbooks("Customer Concern - Warranty Request Log TEST").Sheets("List").Range("I5"):

Dim Path As String
Dim filename As String
Path = Workbooks("Customer Concern - Warranty Request Log TEST").Sheets("List").Range("I5")
filename = Workbooks("Customer Concern - Warranty Request Log TEST").Sheets("List").Range("G1")

ActiveWorkbook.SaveAs filename:=Path & filename & ".xls", FileFormat:=xlNormal

Kill Workbooks("Customer Concern - Warranty Request Log TEST").Sheets("List").Range("I7")

Call RR_Warranty

   Case vbNo
        GoTo Quit:
    End Select

Quit:
End Sub

Private Sub Transfer_CC()
Dim FSO
Dim Source_File As String
Dim Source_Folder As String
Dim Destination_Folder As String

'HERE
Source_File = "OPS 041 Customer Concerns Rev 7.xls"
Source_Folder = "\\DAVINCI-1\USERDATA\carla.fisher\Desktop\Customer Concerns\"
Destination_Folder = Workbooks("Customer Concern - Warranty Request Log TEST").Sheets("List").Range("I5")
Set FSO = CreateObject("Scripting.FileSystemObject")
FSO.CopyFile (Source_Folder & Source_File), Destination_Folder, True

End Sub
Private Sub RR_Warranty()

Msg = "Do you want to update the RR Warranty Report Database with this information?"

    Ans = MsgBox(Msg, vbYesNo)

    Select Case Ans
        Case vbYes
Windows("Customer Concern - Warranty Request Log TEST.xlsm").Activate
    Sheets("List").Select
    Range("F18:N18").Select
    Selection.Copy
Workbooks.Open "X:\QUALITY\Quality Assurance\QA Eng  specific\Quality Control Statistical Analysis\Warranty Reports\Aerospace\2019\Warranty Report.xlsm"
Windows("Warranty Report.xlsm").Activate
    Sheets("New Data").Select
    Range("A2").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
     
Application.Run "'Warranty Report.xlsm'!RR_Warranties"


Case vbNo
        GoTo Quit:
    End Select

Quit:


End Sub

 

[davesexcel]

fromPath = Workbooks("Customer Concern - Warranty Request Log TEST").Sheets("List").Range("I5")

When referencing another workbook, you need the full name
Workbooks("Customer Concern - Warranty Request Log TEST.xlsm")
This would only work if the workbook is open.

I like to set all the variables first.
For example

Sub Button1_Click()
    Dim wb As Workbook, sh As Worksheet, rng As Range
    Dim s As String

    Set wb = Workbooks("Customer Concern - Warranty Request Log TEST.xlsm")
    'if the workbook is not open, then you need to open it
    'Set wb = Workbooks.Open("C:\Users\Dave\AppData\Local\Temp\Customer Concern - Warranty Request Log TEST.xlsm")

    Set sh = wb.Sheets("List")
    Set rng = sh.Range("M1")

    'if you need the complete folder and file name
    s = "C:\Users\Dave\AppData\Local\Temp\" & rng.Value & ".xlsx"

    MsgBox rng.Value    'get the value in M1
    MsgBox s    'complete file name
    Workbooks.Open (s)    'open the workbook named in M1
End Sub