Retrieving data from other workbooks

Last Edited By Krjb Donovan
Last Updated: Mar 05, 2014 09:53 PM GMT


Hello hope you are well. I have a workbook which has a macro button on the front page. When a user clicks on this button, I want another workbook (which the user will select) to open and then information( i will define parameters) to be automatically imported into a particular cell in my original workbook. I have used "Application.Dialogs(xlDialogOpen).Show" to open the File-> Open window, But how do i get the information from that new workbook to the new workbook seeing that I cannot predefine what the name of the workbook is?

eg click button Window - "what file?" User selects Information from worksheet("Sent Claims"), Range ("A:D") copied into original workbook, Worksheet("Gift Aid") Range ("A:D").

This is what I was using to do this (which I think you helped me with last year) - but the sub-directory is subject to change (user defined). I also want to use this procedure to extract other information but I can adjust code to suit.

Private Sub Gift_Aid_Open_Click() Dim bk As Workbook, bk1 As Workbook Dim r As Range, r1 As Range Dim fName As String Application.Dialogs(xlDialogOpen).Show

fName = "D:\Pandylane\Treasury\Gift Aid records.xlsm" Set bk1 = Workbooks("Income - Expenditure 2009.xlsm") Set bk = Workbooks.Open(Filename:=fName) Set r = bk.Sheets("Sent Claims").Range("A:D").EntireColumn Set r1 = bk1.Sheets("Gift Aid Sent Claims").Range("A:D").EntireColumn r.Copy r1 bk.Close End Sub

I would appreciate any advice you can give me. Many Thanks James Allen


Instead of just Application.Dialogs(xlDialogOpen).Show I recommend MyFile = Application.GetOpenFileName Then the entire path to the file will be stored in MyFile (unless user clicks cancel in which case MyFile will be False).

Now you can refer to Activeworkbook



©2021 eLuminary LLC. All rights reserved.