Using macros to change filename and path in a cell

[RonOliver]

Hi. I'm a teacher and I'm fairly new to VBA, so I'm still struggling a bit.

I wanted to send my mates an excel spreadsheet than was able to take information from other spreadsheets I do not know the name of. I tried using "='[So and so.xlsx]Final & Extra'!K3" so, when I hit enter, a dialog box opens asking me to locate the file. I wanted to do exactly that, only using a VBA module to open the explorer upon clicking a button and automatically changing the filename (or path) when locating the file. I managed to create a clickable button that opened a dialog box, but I don't know how to make it change the text in cells.

 

[Micron]

Sounds like you need the msoFileDialogFilePicker, which will return the path string to the chosen file. Then write the string to a cell on a sheet?

 

[RonOliver]

Sounds like you need the msoFileDialogFilePicker, which will return the path string to the chosen file. Then write the string to a cell on a sheet?

That's correct. I'm just having a hard time trying to get the file path and name from msofiledialogfilepicker and inserting it as part of a cell formula. I'm aware this is probably too easy, but I just started out with VBA last week, and it's getting a bit complicated for me to figure out.

 

[Micron]

By "I don't know how to make it change the text in cells." I didn't know you meant in a cell formula. Let's start with posting the code that you've tried?
Please use code tags (vba button on posting toolbar) to maintain code indentation and make code easier to read.

 

[RonOliver]

Sorry, my bad. It's not easy to explain things you don't really fully understand.

I was trying this code:

Sub Triar_arxiu()
    Dim Dialeg As FileDialog
    Dim ArxiuSeleccionat As Variant
    MsgBox "Per favor, indiqueu on està l'arxiu de notes que voleu obrir."
    Set Dialeg = Application.FileDialog(msoFileDialogFilePicker)
    Dialeg.Title = "Trieu l'arxiu"
    If Err.Number <> 0 Then
    Else
        MsgBox "L'arxiu de notes ja està obert"
    End If
End Sub

After selecting the file, I want a specific cell from the file that I've selected to load. It's supposed to be this cell:

"='[So and so.xlsx]Final & Extra'!K3"

. Since my coding knowledge is very limited, I don't know how to continue writing the true value of "if". I know the coding might be atrocious, so excuse me if I made any unnecessary or stupid mistakes.

 

[RonOliver]

Maybe a small clarification. "So and so.xlsx" is the file I don't know the name of and I'd like for the file picker to replace automatically when I select the file.

 

[Micron]

Maybe you can reiterate all of the specific requirements in one post? I can't tell if you want to open a file and do something to it, do a Save As or what. For a teacher, you're a bit vague? Here's 2 examples in your posts

I'd like for the file picker to replace automatically

You want the file picker to replace itself? Replace a file? Rename a file? Open then edit the file (editing being something that file dialogs don't do)?

I want a specific cell from the file that I've selected to load.

Load into what? Cells don't load; files maybe, but the usual term is 'open'. Data from a file can load into something if you have that something ready. Your posts also suggest that every possible file you could choose from a folder will have that sheet name with a cell at that address which contains data that you want to edit (or whatever the task is). If it's always to be one file, then I don't see the need for the dialog at all.

I'm very much like a computer, I guess - black and white and not receptive to situations that require a lot of interpretation. In your head you have all the details and more or less the desired outcome, whereas I'm sitting here in my living room with no real idea of what you're saying. Perhaps those musings/questions will help you to understand the problem at my end.

 

[RonOliver]

Sorry. I don't teach anything computer-related, so the terms are very fuzzy for me indeed.

Let me explain myself again — I'm making an excel spreadsheet for my mates. We'll call it File A. In that sheet, there is a cell that takes data from another excel spreadsheet cell in a different .xlsx file. We'll call this one File B. My problem is, I won't be the one using the spreadsheet and they are likely to re-name both files. Because of this, once they re-name File B, my cell in File A is going to give me "#REF!" until I change File B's name and path in the cell I'm working on. Since my mates are even less computer-savvy than I am, and I'm not confident they'll know how to change File B's name and/or path in File A's cell, I am trying to create a button they can click on, which is meant to open a file picker so they can locate the re-named file and automatically change File B's name and path in File A's cell. The desired result would be for the cell in File A to display the data from the other cell in File B after the right file has been located. I assumed this could be done writing a macro, but I have no real idea of how to do that.

I'm sorry I've been a bit frustrating to work with. Hope things are clearer now.