Need an Excel macro to pick up information from different workbook

Posted on 2004-11-27
Last Modified: 2010-05-02
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

Question by:aazain
    LVL 50

    Accepted Solution

    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
    LVL 50

    Expert Comment

    by:Dave Brett

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


    LVL 15

    Expert Comment


    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Do You Know the 4 Main Threat Actor Types?

    Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

    If you have ever used Microsoft Word then you know that it has a good spell checker and it may have occurred to you that the ability to check spelling might be a nice piece of functionality to add to certain applications of yours. Well the code that…
    Since upgrading to Office 2013 or higher installing the Smart Indenter addin will fail. This article will explain how to install it so it will work regardless of the Office version installed.
    Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…
    Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…

    760 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    9 Experts available now in Live!

    Get 1:1 Help Now