Pull data from other spreadsheets

Posted on 2013-09-16
Medium Priority
Last Modified: 2013-09-27

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?

Question by:daiwhyte
  • 3
  • 2
LVL 12

Expert Comment

by:Harry Lee
ID: 39496823

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.

Author Comment

ID: 39499027
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?

Author Comment

ID: 39499101
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)

LVL 12

Accepted Solution

Harry Lee earned 2000 total points
ID: 39500082

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


Author Closing Comment

ID: 39527711
Thank you

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Microsoft's Excel has many features that most people will never need nor take advantage of.  Conditional formatting is one feature that you may find a necessity once you start using it.
Windows Explorer let you handle zip folders nearly as any other folder: Copy, move, change, and delete, etc. In VBA you can also handle normal files and folders, but zip folders takes a little more - and that you'll find here.
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa‚Ķ

624 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question