• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 167
  • Last Modified:

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

Thanks,
Ali
0
aazain
Asked:
aazain
  • 2
1 Solution
 
Dave BrettCommented:
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

Regards

Dave

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
0
 
Dave BrettCommented:
typo..............

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

Cheers

Dave
0
 
JackOfPHCommented:
listen
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now