0

I have 19 files with different data sets in each file. Each file has one worksheet with 2 columns, i am trying to write a VB Macro assigned to a button [Get Output Data] - name of my button in one worksheet.

This is the code i have written to get data from one of the 19 files.

Sub FetchQAData()

Dim filePath As String
Dim SourceWb As Workbook
Dim TargetWb As Workbook

Set TargetWb = ActiveWorkbook

filePath = TargetWb.Sheets("Control").Range("D8").Value
Set SourceWb = Workbooks.Open(filePath)

       SourceWb.Sheets("results").Range("A1:B9").Copy Destination:=TargetWb.Sheets("QA model output").Range("B5:C13")

SourceWb.Close

MsgBox "RESULTS IMPORTED"

End Sub

The files are all stored on a central server and when i run the Macro get a Run-time error 1004 and the application doesn't recognize the file path.

Can anyone help here please?

Community
  • 1
  • 1
user6431512
  • 1
  • 1
  • Did you try this: http://stackoverflow.com/a/11573970/1153513 – Ralph Jun 06 '16 at 17:11
  • Yes I did Ralph, it doesn't work. – user6431512 Jun 06 '16 at 17:23
  • What is the result of `Dir(filePath)`? What is value for `TargetWb.Sheets("Control").Range("D8").Value`? – Ralph Jun 06 '16 at 17:25
  • I have created a directory of all files in a worksheet in the TargetWB (this is where i want the data to be copied into). This directory is in a worksheet "control". "D8" is the cell where i have entered the path of the 1st of 19 files which points to the reference SourceWb. The runtime error is in the "Set SourceWb = Workbooks.Open(filePath)" command, where it is not recognizing the filepath of the source file. – user6431512 Jun 06 '16 at 17:57
  • I understood that when you posted the question. But then it seems that the value in `filePath` or in `TargetWb.Sheets("Control").Range("D8").Value` is invalid. Hence, my question what this value is. Maybe the value in `TargetWb.Sheets("Control").Range("D8").Value` is not valid? – Ralph Jun 06 '16 at 18:02

2 Answers2

0

Your filePath in cell "D8" must be the full path to the workbook to include the extension: i.e. "C:\WorkbookName.xlsx" without the "" marks.

GMalc
  • 2,608
  • 1
  • 9
  • 16
0

How about this AddIn? Try this and see if it does what you want.

http://www.rondebruin.nl/win/addins/rdbmerge.htm

enter image description here

ASH
  • 20,759
  • 19
  • 87
  • 200