Retrieving data from other workbooks

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

Question

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

Answer

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

HTH

Advertisement

©2021 eLuminary LLC. All rights reserved.