Using a cell number instead of a filepath in declaration

**Sc0tt1e** · 10-01-2014, 03:19 AM

I had an idea a while back which I have been unable to resolve and wondered if anyone knew a solution.

I create funky things that get passed around the office and different people store them in different locations, instead of having to walk around huge offices and changing the code for everyone that uses it I thought I could insert a user form that you can use Application.GetOpenFilename so they can save the document and then enter the file location themselves, which is saved on a hidden sheet in a cell which the code can refer to instead.

E.g.Instead of declaring a folder location in the code (i.e. Set Filepath = "C:\My Computer\My Location\My Folder") is it possible to enter the filepath into an excel cell (for the example A5) and call A5 within the code rather than the location and still declare it as Filepath.

**romperstomper** · 10-01-2014, 03:25 AM

Yes, you just refer to the range instead of a literal value:

Dim FilePath as String
Filepath = Sheets("Some sheet").range("A5").value

**Sc0tt1e** · 10-01-2014, 03:53 AM

I tried that and it wouldn't work.

What if the filepath for the spreadsheet has also already been defined?

i.e.

Dim FPath as Workbook
Dim StUp as Worksheet
Dim Filepath as String

Set FPath = Workbooks.Open("C:\Users\Scott\Documents\CMC\Database.xlsm")
Set StUp = FPath.Worksheets("BAU") 

Set Filepath = FPath.Range("A5").value

**romperstomper** · 10-01-2014, 04:47 AM

You don't use Set with Strings and you have to specify a worksheet for the range. Note my code versus yours.

**Sc0tt1e** · 10-01-2014, 05:19 AM

So would something like this work?

BA1 would contain "C:\Users\Scott\Documents\CMC\Database.xlsm"

Dim FPath as String
Dim HR1 as Worksheet

FPath = Sheets("Sheet1").Range("BA1").Value
Set HR1 = FPath.Worksheets("HR")

With HR1.Range("C" & Rows.Count).End(xlUp).Offset(1)
    .Value = HR.Initials.Value & "001"

**romperstomper** · 10-01-2014, 05:41 AM

Not quite - this will fail because FPath is a String:

Set HR1 = FPath.Worksheets("HR")

You need:

Set HR1 = Workbooks.Open(FPath).Worksheets("HR")

**Sc0tt1e** · 10-01-2014, 05:47 AM

Ahhhhhh, you star.

Cheers Romper, you have just made my life a hell of a lot easier.

**romperstomper** · 10-01-2014, 05:59 AM

Glad to help.

Using a cell number instead of a filepath in declaration

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

Using a cell number instead of a filepath in declaration

Re: Using a cell number instead of a filepath in declaration

Re: Using a cell number instead of a filepath in declaration

Re: Using a cell number instead of a filepath in declaration

Re: Using a cell number instead of a filepath in declaration

Re: Using a cell number instead of a filepath in declaration

Re: Using a cell number instead of a filepath in declaration

Re: Using a cell number instead of a filepath in declaration

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] Trying to get the filepath on "Open", but get filepath of a temporary file instead

[SOLVED] Copying files from a selected filepath to a filepath mentioned in a worksheet's cell

[SOLVED] Copying filepath onto a cell

Open Filepath and then display number of sheetnames using VBA

Filepath in cell problem.

Bookmarks

Bookmarks

Posting Permissions