Inserting Path to VBA

[sanilmathews]

Hi,

I have this simple Workbook open code with the given path as reference.

Workbooks.Open Filename:="D:\Checklist Folder\Temp\Sample Checklist.xls"

Where the above code will open the "Sample Checklist.xls" from the above mentioned path. However path mentioned above will vary and user have to go to VBA editor to change the path to open the Sample Checklist workbook.

I would not want the user to go into the VBA editor since that would expose the codes to be altered. Will it be possible to mention the path in a cell and the code to refer that cell to identify the mentioned path?

 

[VoG]

Try like this

Workbooks.Open Filename:=Sheets("Sheet1").Range("A1").Value & "Sample Checklist.xls"

 

[steve the fish]

Try:

a = Sheet1.Range("A1").Value
Workbooks.Open a

 

[sanilmathews]

Thanks for your response. It worked just fine. Now I am actually giving the codes that I really had trouble with.

Dim lr As Long
Dim i As Long
Dim twb As Workbook
Application.ScreenUpdating = False
Application.DisplayAlerts = False
Set twb = ActiveWorkbook
lr = Cells(Rows.Count, 1).End(xlUp).Row
rwcnt = lr - 2
On Error GoTo ErrHandler:
Workbooks.Open Filename:=Sheets("Sheet1").Range("A12").Value & "Sample Checklist.xls"
For i = lr To 2 Step -1
    
[B]ActiveWorkbook.SaveAs Filename:=[I]"D:\Checklist Folder\Temp\"[/I] & twb.Sheets("Template").Range("K" & i).Value & ".xls"[/B]
Application.StatusBar = Format(1 - (i / lr), "00% Completed")
Next i
ActiveWorkbook.Close
twb.Activate     
Application.StatusBar = ""

I would like to have the same implemented in the above bolded code.

Thank you once again!

 

[sanilmathews]

ADVERTISEMENT

Could any one please let me know if the above request is possible?

Thanks

 

[VoG]

Why can't you adapt the code you've been biven.

 

[sanilmathews]

ADVERTISEMENT

I tried to do edit like the below and it throwing me error.

[B]ActiveWorkbook.SaveAs Filename:=[I]Sheets("Sheet1").Range("A12").Value[/I] & twb.Sheets("Template").Range("K" & i).Value & ".xls"[/B]

I may be wrong in the above logic.

 

[VoG]

What is in

Sheets("Sheet1").Range("A12")

and

twb.Sheets("Template").Range("K" & i)

 

[sanilmathews]

Sheet1 Cell A12 will have the path that the code uses to save the copies of workbook.

Template Sheet Column K will have the list of names which the copies of workbook gets renamed.

 

[VoG]

Maybe

ActiveWorkbook.SaveAs Filename:=Sheets("Sheet1").Range("A12").Value & "\" & twb.Sheets("Template").Range("K" & i).Value & ".xls"