Cell Reference as file Path

[Raaj Chauhan]

Hi All,

Thanks to all. I need a small help. I have a code which is working fine. Whereas each and every time I need to change the reference (path) of the file in VBA Code. Can you please help me, instead of giving the path in VBA code can i keep it in Sheet1 (A4) cell.

Thanks in Advance

Sub UpdateDrugs()

Dim fpath As String
Dim owb As Workbook
Dim Master As Worksheet
Dim Slave As Worksheet 'the following declares both master and slave as worksheets

' I want to keep this reference in Sheet1 A4 cell
fpath = "D:\ART Work\Kamareddy_MLL Tool_08032018.xlsm" ' Path where your MLL tool file is

Set owb = Application.Workbooks.Open(fpath) 'opens the file path

Set Master = ThisWorkbook.Worksheets("Drug_Master") 'Declares this workbook and sheet as "master"
Set Slave = owb.Worksheets("Data") 'Declares the workbook and sheet you're copying to as "slave"

For j = 1 To 50000 'The master sheet range
    For i = 1 To 50000 'The slave sheet range
        If Trim(Master.Cells(j, 2).Value2) = vbNullString Then Exit For 'if the ART_No is blank it will exit and move on to the next row
        If Master.Cells(j, 2).Value = Slave.Cells(i, 23).Value Then '2 (Column B) in Drugs and 23 (Colum W) in MLL tool
            Slave.Cells(i, 26).Value = Master.Cells(j, 4).Value 'to update Due Date
            Slave.Cells(i, 27).Value = Master.Cells(j, 5).Value ' to update Next Due Date
            Slave.Cells(i, 32).Value = Master.Cells(j, 9).Value 'to update Regimen
            
        End If
    Next
    
Next

MsgBox ("Data Transfer Successful")

With owb
    .Save
    .Close
End With
End Sub

 

[mumps]

Replace:

fpath = "D:\ART Work\Kamareddy_MLL Tool_08032018.xlsm"

with

fpath = Range("A4").Value

where A4 contains:

D:\ART Work\Kamareddy_MLL Tool_08032018.xlsm

without the quotes.

 

[Raaj Chauhan]

Replace:

fpath = "D:\ART Work\Kamareddy_MLL Tool_08032018.xlsm"

with

fpath = Range("A4").Value

where A4 contains:

D:\ART Work\Kamareddy_MLL Tool_08032018.xlsm

without the quotes.

Thanks mumps for quideing me. It's working fine. As per your suggestion I have added the below code
fpath = ThisWorkbook.Worksheets("Home").Range("A4")

Thanks once again

 

[mumps]

You are very welcome.