Pull data from other spreadsheets


Ive got a template which I currently fill in using data which is stored on two other spreadsheets (copy and paste).

I want to be able to populate the template automatically from the two spreadsheets.

When I receive the two spreadsheets, I save them in a folder on my C drive then open them up and manually copy the data from them to the Template. The data is always in the same location the received spreasheets.

What I would like to do is load my template, run a macro which prompts me for the location of the two spreasheets, after I enter it, I want the template auto populated using the macro.

How can I acheive this guys?

Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Harry LeeCommented:

Can you please upload sample spreadsheets so that I can write up the VBA macro for you?

It's hard to write up the file if I cannot see how the data has to be transferred to the template file.
daiwhyteAuthor Commented:
I thought it would just be a reference something along the lines of

=SUM('C:\Documents and Settings\Docs\[BEDS.xlsx]Home'!F11)

This would pull just whatever data is in cell F11 on the home tab of the file?
daiwhyteAuthor Commented:
Ok, this works.

=('C:\Documents and Settings\1182388\My Documents\Test and Deliver\Pop\[x86_Detailed_SiteName_Date.xlsx]Home'!F11)

Now how can I change it so instead of looking for the extact file name, it can look for files which start x86?

The reason I ask is that I have many spreadsheet templates which are sent to me. When I want to populate my template, I drop two excel files into a folder called POP and then open my template. Currently - I have to rename the spreadsheets to match whatever file name Im calling (in this case x86_Detailed_SiteName_Date.xlsx)

Harry LeeCommented:

Try this macro. The macro will ask you for a folder to start. It then loop through all files start "X386_". You just have to edit it so that put in the copy and paste action in the commented area.

Function GetFolder(InitDir As String) As String
Dim fldr As FileDialog
Dim sItem As String
sItem = InitDir
Set fldr = Application.FileDialog(msoFileDialogFolderPicker)
With fldr
.Title = "Select a Folder"
.AllowMultiSelect = False
If Right(sItem, 1) <> "\" Then
sItem = sItem & "\"
End If
.InitialFileName = sItem
If .Show <> -1 Then
sItem = InitDir
sItem = .SelectedItems(1)
End If
End With
GetFolder = sItem
Set fldr = Nothing
End Function
Sub PullDataFromX386Wbks()
    Dim strPath As String, strFile As String, wbk As Workbook, ws As Workbook
    ' Path must end in trailing backslash
    Set ws as ActiveSheet
    strPath = GetFolder("C:\") & "\"
    strFile = Dir(strPath & "*.xls")
    Do While strFile <> ""
        If Left(strFile, 4) = "x86_" Then
            Set wbk = Workbooks.Open(Filename:=strPath & strFile)

' Put your action in here
' For any cell referencing to the source data book, use wbk to reference the workbook,
' and sheets to reference to the sheet.
'            wbk.Sheets("Home").Range("F11").Copy
'            ws.Paste
' the action ends here.            
            wbk.Close SaveChanges:=False
        End If
        strFile = Dir
End Sub

Open in new window


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
daiwhyteAuthor Commented:
Thank you
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.