Need an Excel macro to pick up information from different workbook

I am working in Excel and need to write a macro (which is in VB) in let's say worrkbook1 that would popup a dialog box to ask for a workbook filename and would then refer to a predefined sheet and cell reference in that workbook and paste the formula in woorkbook1

For e.g. I want the following formula to appear in woorkbook1:

='[American School (owner).xls]Income Expense Assumptions'!$A$1

but I need the ability to modify the filename with the input provided in the popup dialog box

Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Hi Ali,

This code brings up the SaveAs dialog box to get the file name and path - it won't actually save anything. Cell A1 from sheet Income Expense Assumptions is then dumped into A1 of ThisWorkbook
The code works whether the target book is open or closed

It assumed you have Excel 2000 to use the StrReverse function



Sub GetString()
    Dim FName As String, NewString As String
    Dim FilePos As Integer

    FName = Application.GetSaveAsFilename( _
                                          fileFilter:="Pick a file (*.xls), *.xls")

    'If user doesn't select a file then exit
    If FName = "" Then Exit Sub
    FilePos = Len(FName) - InStr(StrReverse(FName), "\") + 1
    NewString = "='" & Left(FName, FilePos) & "[" & Right(FName, Len(FName) - FilePos) & "]Income Expense Assumptions'!$A$1"

    'Enter Formula.
    ThisWorkbook.Range("A1") = NewString
End Sub

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial

please change
 If FName = "" Then Exit Sub
 If FName = False Then Exit Sub
if the user cancels the file name retrieval process


It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic Classic

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.