Activate a file using "Input Box"

Panoos64 · Sep 17, 2018

Hi all, I would like to write a VBA code so that to open and activate a file, which I will choose using an input box:

e.g. Salaries 1-2018.xlsx

The path of this is: ‘C:\Users\User\Payroll\Salaries 1-2018.xlsx”

In input box I have to enter the file name, e.g. Salaries 1-2018 or Salaries 2-2018 e.t.c. and the code should open and activate.

Thanking you in advance

My Aswer Is This · Sep 17, 2018

Try this:
Enter just the file name.

Code:

Sub Open_Me_New()
'Modified  9/17/2018  7:09:23 AM  EDT
On Error GoTo M
Dim ans As String
Dim anss As String
ans = InputBox("Enter file name to open")
anss = "C:\Users\User\Payroll\Salaries"
Workbooks.Open anss & "\" & ans & ".xlsx"
Exit Sub
M:
MsgBox "File may not exist or we have some other problem"
End Sub

Panoos64 · Sep 17, 2018

Well done My Asw! it works perfect. I would like to thank you once again for your continuance support. Hv a great, lovely day!

My Aswer Is This · Sep 17, 2018

Glad I was able to help you.
Come back here to Mr. Excel next time you need additional assistance.

Panoos64 said:
Well done My Asw! it works perfect. I would like to thank you once again for your continuance support. Hv a great, lovely day!

Panoos64 · Sep 18, 2018

Apologies that i bother you again my Asw, just kindly require to provide me support so that when i open a file by the above vba i would like to link the below codes. Shortly i wanted to join your VBA code with below. Kindly advice me if i have to prepare a new post. I am asking you because is relate to the above code.

Thanking you in advance

Code:

Dim ws As Worksheet
Dim rngLocation As Range
Dim rngNewCol As Range
Dim lRow As Long
Set ws = ActiveSheet
Set rngLocation = ws.Rows(1).Find("SALARY")
If rngLocation Is Nothing Then Exit Sub
rngLocation.Offset(, 1).EntireColumn.Insert
Set rngNewCol = rngLocation.Offset(, 1)
lRow = ws.Cells.Find("*", ws.Range("A1"), SearchDirection:=xlPrevious).Row - 1
rngNewCol.Value = "Bank list"
With rngNewCol.Offset(1, 0).Resize(lRow)
    .NumberFormat = "General"
    .Formula = "=VLOOKUP(RC[-10],'[PAYMENT LIST.xlsx]Sheet1'!R4C1:R5000C32,25,0)"


Set ws = ActiveSheet
Set rngLocation = ws.Rows(1).Find("Bank list")
If rngLocation Is Nothing Then Exit Sub
rngLocation.Offset(, 1).EntireColumn.Insert
Set rngNewCol = rngLocation.Offset(, 1)
lRow = ws.Cells.Find("*", ws.Range("A1"), SearchDirection:=xlPrevious).Row - 1
rngNewCol.Value = "Variance"
With rngNewCol.Offset(1, 0).Resize(lRow)
    .NumberFormat = "General"
    .Formula = "=SUM(RC[-2]-RC[-1])"

End Sub

Activate a file using "Input Box"

Panoos64

Well-known Member

Excel Facts

My Aswer Is This

Well-known Member

Panoos64

Well-known Member

My Aswer Is This

Well-known Member

Panoos64

Well-known Member

Similar threads

Forum statistics

Share this page

Activate a file using "Input Box"

Panoos64

Well-known Member

Excel Facts

My Aswer Is This

Well-known Member

Panoos64

Well-known Member

My Aswer Is This

Well-known Member

Panoos64

Well-known Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock