Page 1 of 2 12 LastLast
Results 1 to 15 of 17
  1. #1
    Silver Lounger
    Join Date
    Dec 2000
    Location
    Northampton, Northamptonshire, England
    Posts
    1,951
    Thanks
    2
    Thanked 1 Time in 1 Post

    Import data into Excel from another spreadsheet (Excel 2003)

    Hi

    I have a four identical spreadsheets, one is the master sheet, the others are sent to reps to fill in, I need to import their data into the master.

    I have tried to use data import external data, I can get the data from the first sheet and then I cant import any more. All I can do is edit the query.

    I can't help feeling I am going the wrong way about this. Any advice please.

    Thanks

    Braddy
    If you are a fool at forty, you will always be a fool

  2. #2
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Import data into Excel from another spreadsheet (Excel 2003)

    Hi Braddy

    Seen you on the boards but never had a chance to talk. I had a similar problem last year and I have been desperately looking on my home PC for the solution.

    The way I was able to do it in the end was, like you, create a master workbook which consolidated the other 4 workbooks. I am not sure what your VBA is like but I will provide you with this code for now and if you would like to wait about 15 hours ( when I am back in the office) I can provide you with the full solution.

    Basically the Combine function is looping through a folder ( this can be hard coded or you can keep it as is) finding excel files, opening them, copying the content and then pasting it to the Master, closing and repeating it for the whole folder

    <!post=This post,389440>This post<!/post> describes how you can get summary details or collated info reported from these imported sheets. Contact me via the lounge to ensure I collect it in the morning and I will send the file.


    Public Function BrowseFolder(Optional Title As String = "Select a Folder Containing the files", _
    Optional RootFolder As Variant) As String
    On Error Resume Next
    BrowseFolder = CreateObject("Shell.Application").BrowseForFolder( 0, Title, 0, RootFolder).Items.Item.Path
    End Function

    Sub Combine()
    Dim strPath As String
    Dim strfile As String
    Dim wbkSource As Workbook
    Dim wbkTarget As Workbook
    Dim strname As String
    On Error Resume Next

    strPath = BrowseFolder
    If strPath = "" Then
    Exit Sub
    End If


    Set wbkTarget = Workbooks.Add

    Sheets("Sheet1").Name = "Start"
    Sheets("Start").Select
    Sheets("Start").Move after:=Sheets(2)
    Windows(1).WindowState = xlMinimized


    strfile = Dir(strPath & "*.xls")
    Do While Not (strfile = "")
    Set wbkSource = Workbooks.Open(Filename:=strPath & "" & strfile, AddToMRU:=False)
    wbkSource.Worksheets.Copy after:=wbkTarget.Worksheets(wbkTarget.Worksheets.C ount)

    strname = Left(strfile, Len(strfile) - 4)
    ActiveWorkbook.ActiveSheet.Name = (strname)
    wbkSource.Close SaveChanges:=False
    strfile = Dir

    Cells.Select
    Cells.EntireColumn.AutoFit
    Cells(1, 1).Select
    Loop


    ActiveWorkbook.Sheets.Add after:=Worksheets(Worksheets.Count)
    ActiveWorkbook.ActiveSheet.Name = ("End")



    ActiveWorkbook.SaveAs Filename:="CPA" & Format(Now(), "YYMMDD")


    ExitHandler:
    Set wbkSource = Nothing
    Set wbkTarget = Nothing
    Exit Sub


    End Sub
    Jerry

  3. #3
    Silver Lounger
    Join Date
    Dec 2000
    Location
    Northampton, Northamptonshire, England
    Posts
    1,951
    Thanks
    2
    Thanked 1 Time in 1 Post

    Re: Import data into Excel from another spreadsheet (Excel 2003)

    Hi Jerry

    Nice to meet you, if you can provide the full solution I would be most grateful.

    Many thanks for your response.

    Braddy
    If you are a fool at forty, you will always be a fool

  4. #4
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Import data into Excel from another spreadsheet (Excel 2003)

    It would not be difficult to write VBA code to open the Rep's workbooks and copy the sheets and paste them into the master. I would be glad to work on something if you would provide a little additional information:

    1- How do you want to identify the Rep workbooks? By specific names and paths? All the .xls files in a particular directory? You manually identify the files through an Open dialog box? Some other method?

    2- What worksheets in the Rep's workbooks are to be copied? A specifically named worksheet? The first (or any other) sheet in the workbook?

    3- How do you want to copy the sheets to the master? All to one sheet (what name) one after another down the sheet? To separate worksheets (what names)?

    4- What do you want to do with any data that is already on the sheet being copied to?

    Can you provide a sample Rep's workbook that can be used for testing with any confidential data changed?
    Legare Coleman

  5. #5
    Silver Lounger
    Join Date
    Dec 2000
    Location
    Northampton, Northamptonshire, England
    Posts
    1,951
    Thanks
    2
    Thanked 1 Time in 1 Post

    Re: Import data into Excel from another spreadsheet (Excel 2003)

    Hi Legare

    1. All the xls file in a specific directory.
    2. There will only be one sheet in the reps workbook (prospects).
    3. All to one sheet ( append rows to all previous rows. To the only sheet in the main workbook ( total prospects). to create a total build up.
    4. As above the data already on the sheet must be kept and new data appended each month the workbooks are provided by the reps.

    I have attached the master workbook which is of course blank, but all the reps workbooks will be absolutely identical except of course they will contain the data I wish to collate.
    If you are a fool at forty, you will always be a fool

  6. #6
    Silver Lounger
    Join Date
    Dec 2000
    Location
    Northampton, Northamptonshire, England
    Posts
    1,951
    Thanks
    2
    Thanked 1 Time in 1 Post

    Re: Import data into Excel from another spreadsheet (Excel 2003)

    Hi Jezza

    I used your excellent code for importing data, it is not exactly what I was looking for, as I need the data to be collated all in one sheet ( see my reply to Legare)

    I will find your code very useful for other projects however it seems I have no control over where it saves the file, can you assist with this please.

    Thanks

    Braddy
    If you are a fool at forty, you will always be a fool

  7. #7
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Import data into Excel from another spreadsheet (Excel 2003)

    Braddy

    This is rather scary, I was just looking at the file when this email arrived. I caught up with the thread this morning and it appears that you want it to append past data. My solution is a weekly summary report which basically it drags the files from a predefined folder into the master workbook, sandwiches it between two worksheets, a summary sheet sums up values from the imported sheets and then time stamps the file name and archives it for you. Does this wet your appetite?

    I see Legare is on the case but if you like I can tweak my solution during lunchtime as it is bespoke to my network and see what you think
    Jerry

  8. #8
    Silver Lounger
    Join Date
    Dec 2000
    Location
    Northampton, Northamptonshire, England
    Posts
    1,951
    Thanks
    2
    Thanked 1 Time in 1 Post

    Re: Import data into Excel from another spreadsheet (Excel 2003)

    Hi Jezza

    That would be great if you have the time, I am happy to accept any help at this time.


    Thanks

    Braddy
    If you are a fool at forty, you will always be a fool

  9. #9
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Import data into Excel from another spreadsheet (Excel 2003)

    See if the attached code does what you want. Modify the strPath constant as needed, but make sure to keep the trailing backslash.

  10. #10
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Import data into Excel from another spreadsheet (Excel 2003)

    Hi Braddy

    Here is the revised version. Excuse the crumbs I was eating a sandwich at the time.

    As I said before, I wrote this to consolidate weekly/monthly reports. The Main worksheet has a formula in A1 =SUM(Start:End!A1:A10), this is the method to sum these values on each sheet. To start I would create a new folder on your C drive and in it put 3 worksheets with a value written in each cell A1.

    When you run the macro you will be get a Browse Folder Window, navigate to your new folder and press OK. The consolidator will run and import your 3 xls files and the totals for the contents in all A1 cells shown on the Main sheet. The file will be saved as Braddy050505.xls (yymmdd) for archive purposes.

    Enjoy!
    Jerry

  11. #11
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Import data into Excel from another spreadsheet (Excel 2003)

    You didn't attach a Rep workbook, so I could not test this completely, but try the following code. You also did not specify the path where the Rep workbooks will be stored, so you will need to modify the line that set strPath.

    <pre>Public Sub CombineReps()
    Dim strPath As String, strFName As String
    Dim oMaster As Workbook, oRep As Workbook, oSheet As Worksheet, oCRange As Range
    Set oMaster = ActiveWorkbook
    strPath = "C:Work"
    strFName = Dir(strPath & "*.xls")
    Do While strFName <> ""
    Set oRep = Workbooks.Open(strPath & strFName)
    Set oSheet = Nothing
    On Error Resume Next
    Set oSheet = oRep.Worksheets("prospects")
    On Error GoTo 0
    If Not oSheet Is Nothing Then
    Set oCRange = oSheet.Range("A2", oSheet.Range("A1").Offset( _
    oSheet.Range("A65536").End(xlUp).Row - 1, _
    oSheet.Range("IV1").End(xlToLeft).Column - 1))
    oCRange.Copy
    oMaster.Worksheets("total prospects").Paste _
    Destination:=oMaster.Worksheets("total prospects").Range("A65536").End(xlUp).Offset(1, 0)
    Application.CutCopyMode = False
    End If
    oRep.Close
    strFName = Dir()
    Loop
    End Sub
    </pre>

    Legare Coleman

  12. #12
    Silver Lounger
    Join Date
    Dec 2000
    Location
    Northampton, Northamptonshire, England
    Posts
    1,951
    Thanks
    2
    Thanked 1 Time in 1 Post

    Re: Import data into Excel from another spreadsheet (Excel 2003)

    Hi Legare

    Your code is excellent, as is Hans's code, I am spoilt for choice, and deeply indebted to you both.

    I would also like to thank Jezza who also responded to this request.

    Many Many Thanks

    Braddy
    If you are a fool at forty, you will always be a fool

  13. #13
    4 Star Lounger
    Join Date
    May 2002
    Location
    US
    Posts
    439
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Import data into Excel from another spreadsheet (Excel 2003)

    Legare,

    I too, have an application where this would work. I put the code in, changed the necessary items and ran the code. However, in the files I wish to extract data i have a VBA project. When the files are 'opened', it runs the code. How can I have the VB you posted disable the project so that it will extract the information?

    Thanks,
    Brad

  14. #14
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Import data into Excel from another spreadsheet (Excel 2003)

    If the workbook you are opening has code that you want to disable you can tell vb to disable them and then reenable them after opening:

    <pre>Application.enableenvents = false
    Set oRep = Workbooks.Open(strPath & strFName)
    Application.enableenvents = true</pre>


    Steve

  15. #15
    4 Star Lounger
    Join Date
    May 2002
    Location
    US
    Posts
    439
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Import data into Excel from another spreadsheet (Excel 2003)

    Where exactly does the code go? in the master workbook, or in the one(s) I am copying the data from?

    Thanks,
    Brad

Page 1 of 2 12 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •