Macro Referencing a Changing Filename

[LauraSophie]

Hi,

I have a macro that opens a specific file, opens it, takes some data and then closes it again. The premise is there are so many files that need opening that it will do them one by one and then close them afterwards. The rest of the macro works completely fine but I want to be able to have the filename changeable without editing the macro.


Part of the macro is:

Workbooks.Open Filename:= _
"H:\FOLDER\SUBFOLDER\Filename.xlsx" _

, UpdateLinks:=3
Windows("Book1").Activate
Columns("D:D").Select
Selection.Copy

Do you know of a way to have the filename referenced in a cell within the worksheet as opposed to being called out specifically in the macro? I have a CELL function included in the worksheet that gives the exact filename but is there any way of getting the macro to look for this instead of having to specify it? That way if the end user updates the filename (probably with edit links) in the cell on the worksheet they don't need to go in an amend the macro?

Thanks!

 

[NdNoviceHlp]

If the entire file path is in Sheet1 cell A1 then...

Workbooks.Open Filename:= CSTR(Sheets("Sheet1").Range("A" & 1).Value

Also, the use of selection and activate are usually not required and slow code execution. For example, if your copying and pasting D1:D10 from sheet1 to sheet2....

Sheets("Sheet1").Range("D1:D10).Copy Destination:=Sheets("Sheet2").Range("D" & 1)
Application.CutCopyMode = False

HTH. Dave

 

[LauraSophie]

Thank you! This is great!

It does only work when the subject cell has the file name typed as opposed to being calculated by a formula. I don't suppose you know a way around this? At the moment I am formatting the file name using: =SUBSTITUTE(SUBSTITUTE(LEFT(E4,SEARCH("]",E4,1)),"[","",1),"]","",1). Where E4 contains an =CELL(filename) formula.

Also, I'm not sure what you mean: "Also, the use of selection and activate are usually not required and slow code execution". Apologies - I'm not very advanced at VBA. It's just something I've been trying to learn by doing a bit more recently.

Thanks!

 

[NdNoviceHlp]

It shouldn't matter whether U type the file path or generate it with a formula. I'm guessing the formula doesn't produce the exact file name. Check to make sure there are no " " (blank spaces) and that the "/" (file separator) and file extensions (ie. ".xlsm") are correctly outputted by the formula. As for the selection and activation, I was trying to help U learn and provided U with an example of copying and pasting without the use of activation or selection. Perhaps part of your formula generated contains integers and not strings which are required for file paths. That's why the previous post re. CSTR which forces the data to be interpreted as a string. I see that I missed a bracket above, So if U correct the sheet name and cell column and row in the following code for your formula output if should work.

Workbooks.Open Filename:= CSTR(Sheets("Sheet1").Range("A" & 1).Value)

U can also do this to check the formula output...

Msgbox CSTR(Sheets("Sheet1").Range("A" & 1).Value) & " characters: " and Len(CSTR(Sheets("Sheet1").Range("A" & 1).Value))

This will tell U what the outputted file path is and how many characters so U can check for invisible " " blank characters.
Dave

 

[LauraSophie]

Thank you! I figured out what the problem was and it was a rookie mistake!

The formula had the file path being outputted correctly but becuase the macro opens the file, the formula is #N/A until the file has been opened! Sorted - thank you!

 

[NdNoviceHlp]

You are Welcome. Thanks for posting your outcome. Dave