Results 1 to 11 of 11
  1. #1
    2 Star Lounger
    Join Date
    Jan 2007
    Location
    Gray, Louisiana, USA
    Posts
    289
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Get Data From Worksheet on Drive (2000/2003)

    Is it possible to compile a list of contents from a particular cell, from multiple workbooks, stored in a particular folder on a hard drive, into a list in an open workbook ?

    If a designated cell has a name or number could Excel automatically generate a list - listing the contents of the designated cell in several different workbooks say in column C?

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

    Re: Get Data From Worksheet on Drive (2000/2003)

    A workbook can have multiple sheets. Should the list contain values from the first worksheet in each workbook, or from a specific worksheet, or from all worksheets in each workbook?

  3. #3
    2 Star Lounger
    Join Date
    Jan 2007
    Location
    Gray, Louisiana, USA
    Posts
    289
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Get Data From Worksheet on Drive (2000/2003)

    Just sheet1

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

    Re: Get Data From Worksheet on Drive (2000/2003)

    You could use the following macro as a starting point. Modify the two constants at the beginning as needed.
    <code>
    Sub ImportFromFolder()
    ' Modify as needed, but keep trailing backslash
    Const strPath = "C:XLTest"
    ' Modify as needed - can be a cell reference such as A1 or the name of a cell.
    Const strCell = "A1"

    Dim strFile As String
    Dim wbkCur As Workbook
    Dim wshCur As Worksheet
    Dim lngCurRow As Long
    Dim wbk As Workbook

    Application.ScreenUpdating = False

    Set wbkCur = ActiveWorkbook
    Set wshCur = wbkCur.Worksheets(1)
    lngCurRow = wshCur.Range("C65536").End(xlUp).Row

    strFile = Dir(strPath & "*.xls")
    Do While Not strFile = ""
    Set wbk = Workbooks.Open(Filename:=strPath & strFile, AddToMRU:=False)
    lngCurRow = lngCurRow + 1
    wshCur.Range("C" & lngCurRow) = wbk.Worksheets(1).Range(strCell)
    wbk.Close SaveChanges:=False
    strFile = Dir
    Loop

    ErrHandler:
    Set wbk = Nothing
    Set wshCur = Nothing
    Set wbkCur = Nothing
    Application.ScreenUpdating = True
    Exit Sub

    ExitHandler:
    MsgBox Err.Description, vbExclamation
    Resume ExitHandler
    End Sub
    </code>
    You can add bells and whistles as desired.

  5. #5
    2 Star Lounger
    Join Date
    Jan 2007
    Location
    Gray, Louisiana, USA
    Posts
    289
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Get Data From Worksheet on Drive (2000/2003)

    Right on target. That generates the list perfectly.

    Thanks

  6. #6
    2 Star Lounger
    Join Date
    Jan 2007
    Location
    Gray, Louisiana, USA
    Posts
    289
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Get Data From Worksheet on Drive (2000/2003)

    Question.
    The files, that the macro gets the cell contents from, has a message box that automatically prompts when it is opened. When I run the macro, I have to click ok for each workbook that is in the folder. Is there an easy way around this (can the macro be modified to get around the mesage box) or do I need to do away with the message box in each of the workbooks? thanks for your time

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

    Re: Get Data From Worksheet on Drive (2000/2003)

    What is the message box about? In general, you can suppress message boxes by adding the following line at the beginning of the macro:

    Application.DisplayAlerts = False

  8. #8
    2 Star Lounger
    Join Date
    Jan 2007
    Location
    Gray, Louisiana, USA
    Posts
    289
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Get Data From Worksheet on Drive (2000/2003)

    The message box is just general information that shows when the last update to the worksheet was done.
    thanks I'm going to try your suggestion.

  9. #9
    2 Star Lounger
    Join Date
    Jan 2007
    Location
    Gray, Louisiana, USA
    Posts
    289
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Get Data From Worksheet on Drive (2000/2003)

    How would I add a line to this macro, at the beginning, so that it fills cell G4 with the text "Injury" ?

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

    Re: Get Data From Worksheet on Drive (2000/2003)

    In the sheet where you create the list? Below the line
    <code>
    Set wshCur = wbkCur.Worksheets(1)
    </code>
    insert the line
    <code>
    wshCur.Range("G4") = "Injury"</code>

  11. #11
    2 Star Lounger
    Join Date
    Jan 2007
    Location
    Gray, Louisiana, USA
    Posts
    289
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Get Data From Worksheet on Drive (2000/2003)

    <img src=/S/music.gif border=0 alt=music width=97 height=29> <img src=/S/clapping.gif border=0 alt=clapping width=19 height=23> <img src=/S/clapping.gif border=0 alt=clapping width=19 height=23> <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

Posting Permissions

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