Page 1 of 3 123 LastLast
Results 1 to 15 of 31
  1. #1
    Silver Lounger
    Join Date
    Jan 2001
    Location
    Northern, California, USA
    Posts
    1,886
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Multiple Spreadsheets

    Hi!

    I'm going to try my best to explain this problem, I hope you can follow it! [img]/w3timages/icons/smile.gif[/img]

    I have a spreadsheet with a userform which records information and puts it into a worksheet. This spreadsheet is e-mailed to me from 10+ call center staff members, with the file format, iniyyyymmdd.xls (thier initials, and the julian date.) At the end of the month, I have approx 25 files from each team member.

    The data is compiled within each file through a series of array formulas. The results of these formulas are in the exact same range on every file recieved. What I want to do, is generate a sheet which automatically adds the values from these ranges within different files.

    What would be the best way to go about this? Is there a way to read a directory's contents for specific criteria, and pull data from worksheets within specified files for a specified range?

    If this is at all unclear, please let me know!

    Thanks Millions!

    Drk.
    <IMG SRC=http://www.wopr.com/w3tuserpics/Kel_sig.gif>
    Moderator:<font color=448800> Pix Place, Internet Explorer</font color=448800>
    <small>www.kvisions.com

  2. #2
    Silver Lounger
    Join Date
    Jan 2001
    Location
    Northern, California, USA
    Posts
    1,886
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Multiple Spreadsheets

    Anyone at all? [img]/w3timages/icons/dizzy.gif[/img]

    Drk.
    <IMG SRC=http://www.wopr.com/w3tuserpics/Kel_sig.gif>
    Moderator:<font color=448800> Pix Place, Internet Explorer</font color=448800>
    <small>www.kvisions.com

  3. #3
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Kortrijk, Belgium
    Posts
    571
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Multiple Spreadsheets

    Drk,
    I've seen some code that I applied which might be something useful to start with for your particular problem. The code is an example of what is known as recursive programming (as you can see the findfiles procedure is called from within itself). Excuse me if I do not enter into details, I took the code as it was and added some functionality for my personal use. Anyhow, the code lets you loop through a directory (the one you enter in the inputbox) and its subdirectories, searching for files of the type you declare in the filter option (as an example I took the xls-files of september 2000). Then it is up to you to do something with the returned filename. Hope this helps you in the direction you are aiming at.


    Public Sub FindFiles(sPath As String, Files() As String, Optional Filter = "*.*")
    Dim sFileName As String
    Dim Directories() As String
    Dim i As Integer
    ReDim Directories(0)
    If Right(sPath, 1) <> Application.PathSeparator Then
    sPath = sPath & Application.PathSeparator
    End If
    sFileName = Dir(sPath, vbDirectory)
    Do While sFileName <> ""
    If GetAttr(sPath & sFileName) And vbDirectory Then
    If sFileName <> "." And sFileName <> ".." Then
    ReDim Preserve Directories(UBound(Directories) + 1)
    Directories(UBound(Directories)) = sPath & sFileName
    End If
    ElseIf sFileName Like Filter Then
    ReDim Preserve Files(UBound(Files) + 1)
    Files(UBound(Files)) = sPath & sFileName
    End If
    sFileName = Dir
    Loop
    For i = 1 To UBound(Directories)
    FindFiles Directories(i), Files(), Filter
    Next i
    End Sub

    Sub loop_through_dir()
    Dim FileArray() As String
    Dim i As Integer
    Dim sFolder As String
    Do
    sFolder = Trim(InputBox("Enter directory to summarize", "Monthly summary", "c:"))
    If sFolder = "" Then
    If MsgBox("Abort scan?", vbYesNo Or vbQuestion, "No Folder entered") = vbYes Then
    Exit Sub
    End If
    End If
    Loop Until sFolder <> ""
    ReDim FileArray(0)
    FindFiles sFolder, FileArray(), "*0900.xls"
    For i = 1 To UBound(FileArray)
    MsgBox FileArray(i)
    'do something with returned filename (open it, take out the data you need, close it, put the data in a common worksheet, etc.)
    Next i
    End Sub

  4. #4
    Silver Lounger
    Join Date
    Jan 2001
    Location
    Northern, California, USA
    Posts
    1,886
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Multiple Spreadsheets

    Looks Great Hans! i'll give her a try!

    Does anyone know of a way to have a variable in a formula?

    I.E. I know that I can use the following syntax as a formula to retrieve an object from another file:

    ='[workbook.xls]sheetname'!$C$R
    Is there a way to assign a string to the workbook name? Can I create a list of dates and a list of initials, combine the two, and open iniyyyymmdd.xls... any ideas?
    <IMG SRC=http://www.wopr.com/w3tuserpics/Kel_sig.gif>
    Moderator:<font color=448800> Pix Place, Internet Explorer</font color=448800>
    <small>www.kvisions.com

  5. #5
    2 Star Lounger
    Join Date
    Jan 2001
    Location
    Winnipeg, Canada
    Posts
    109
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Multiple Spreadsheets

    I don't know of a way to use a variable in a formula (because Excel doesn't recognize VBA var), but you can do it the other way around.
    You can use a variable in VBA to assign a formula to a cell.
    You hardwire the cell in essence, but the formula can be changed continuously using the variable in VBA, as follows:
    Sub TryThis()
    Dim myVar As String

    myVar = "A1"
    Range("A2") = "=Sheet2!" & myVar
    End Sub

    This is a very simple version ofcourse, but you could change the value of myVar by reading values from a range of cells with a For Each statement.

  6. #6
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Kortrijk, Belgium
    Posts
    571
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Multiple Spreadsheets

    Is this what you mean? You give it a name the moment you save it. If you want to retrieve the name, you can use the commands on the last line of the code below.

    Dim sFileName As String
    sFileName = "C:My Documentstest.xls"
    ActiveWorkbook.SaveAs FileName:=sFileName, FileFormat:= _
    xlNormal, Password:="", WriteResPassword:="", ReadOnlyRecommended:=False _
    , CreateBackup:=False
    MsgBox ThisWorkbook.Path & "" & ThisWorkbook.Name

  7. #7
    Silver Lounger
    Join Date
    Jan 2001
    Location
    Northern, California, USA
    Posts
    1,886
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Multiple Spreadsheets

    Hans,

    I'm trying to incorporate this code into a new worksheet, I think it will serve my purposes well, however can you give me a pointer on adding a string to the filter? I have the following code assigned just before it asks for the directory:

    strInitials = InputBox("Enter Initials:")

    I then want to add this string to the filter, so that only results containing strInitials are returned, and manipulated, any ideas?

    Thanks!

    Drk.
    <IMG SRC=http://www.wopr.com/w3tuserpics/Kel_sig.gif>
    Moderator:<font color=448800> Pix Place, Internet Explorer</font color=448800>
    <small>www.kvisions.com

  8. #8
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Louisville, Kentucky, USA
    Posts
    605
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Multiple Spreadsheets

    I don't have an answer to your problem, but would sure like to know how you put the smiley faces here.

    Thanks
    Roberta Price <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

  9. #9
    Silver Lounger
    Join Date
    Jan 2001
    Location
    Northern, California, USA
    Posts
    1,886
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Multiple Spreadsheets

    If you notice while you're composing, there's a link to "Show 1-Click TagPanel" There are LOTS of smiley's there. [img]/w3timages/icons/smile.gif[/img]

    Drk.
    <IMG SRC=http://www.wopr.com/w3tuserpics/Kel_sig.gif>
    Moderator:<font color=448800> Pix Place, Internet Explorer</font color=448800>
    <small>www.kvisions.com

  10. #10
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Kortrijk, Belgium
    Posts
    571
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Multiple Spreadsheets

    Try something like

    FindFiles sFolder, FileArray(), "*" & strInitials & "*.xls"

    this should add a filter that selects only those files that have strInitials in their names.

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

    Re: Multiple Spreadsheets

    If you put the sheet name in A1, then you could put something like this in B1:

    <pre>="["&A1&"]sheetname!$C$1"
    </pre>

    Then you should be able to use:

    <pre>=Indirect(A1)
    </pre>


    to get the value from the other sheet.

    I didn't test the above, so I may not have the syntax correct.
    Legare Coleman

  12. #12
    Silver Lounger
    Join Date
    Jan 2001
    Location
    Northern, California, USA
    Posts
    1,886
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Multiple Spreadsheets

    Okay.. That's working! [img]/w3timages/icons/bow.gif[/img]

    I had another idea... I can see that the results are being returned as msgboxes, how can I add the results to a list box or text field acting like a text box? This way, I can change the color to red while the process is working on that file, and green when it's complete. Any ideas?

    Thanks again!

    Drk.
    <IMG SRC=http://www.wopr.com/w3tuserpics/Kel_sig.gif>
    Moderator:<font color=448800> Pix Place, Internet Explorer</font color=448800>
    <small>www.kvisions.com

  13. #13
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Kortrijk, Belgium
    Posts
    571
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Multiple Spreadsheets

    the messageboxes in the code only serve as an example. It is clear that you should replace this by your own code. The FileArray() is an array and you should be able to add each individual element of the array to a listbox, using the listbox.additem FileArray(i) command.

  14. #14
    Silver Lounger
    Join Date
    Jan 2001
    Location
    Northern, California, USA
    Posts
    1,886
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Multiple Spreadsheets

    Can I add a color reference to that command? I'm having some trouble adding the colors...

    Thanks Millions! [img]/w3timages/icons/bow.gif[/img]

    Drk.
    <IMG SRC=http://www.wopr.com/w3tuserpics/Kel_sig.gif>
    Moderator:<font color=448800> Pix Place, Internet Explorer</font color=448800>
    <small>www.kvisions.com

  15. #15
    Silver Lounger
    Join Date
    Jan 2001
    Location
    Northern, California, USA
    Posts
    1,886
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Multiple Spreadsheets

    can anyone assist me with this problem? I'm getting several errors, but am unsure of what to do about them, (it seems i've tried nearly everything..) Any ideas?

    ***CODE START***
    Public StrInitials As String
    Public Sub FindFiles(sPath As String, Files() As String, Optional Filter = "*.*")
    Dim sFileName As String
    Dim Directories() As String
    Dim i As Integer
    ReDim Directories(0)
    If Right(sPath, 1) <> Application.PathSeparator Then
    sPath = sPath & Application.PathSeparator
    End If
    sFileName = Dir(sPath, vbDirectory)
    Do While sFileName <> ""
    If GetAttr(sPath & sFileName) And vbDirectory Then
    If sFileName <> "." And sFileName <> ".." Then
    ReDim Preserve Directories(UBound(Directories) + 1)
    Directories(UBound(Directories)) = sPath & sFileName
    End If
    ElseIf sFileName Like Filter Then
    ReDim Preserve Files(UBound(Files) + 1)
    Files(UBound(Files)) = sPath & sFileName
    End If
    sFileName = Dir
    Loop
    For i = 1 To UBound(Directories)
    FindFiles Directories(i), Files(), Filter
    Next i
    End Sub

    Public Sub btngo_Click()
    Dim FileArray() As String
    Dim i As Integer
    Dim sFolder As String
    Do
    sFolder = Trim(InputBox("Enter directory to summarize", "Monthly summary", "c:"))
    StrInitials = InputBox("Enter Initials:")
    If sFolder = "" Then
    If MsgBox("Abort scan?", vbYesNo Or vbQuestion, "No Folder entered") = vbYes Then
    Exit Sub
    End If
    End If
    Loop Until sFolder <> ""
    ReDim FileArray(0)
    FindFiles sFolder, FileArray(), "*" & StrInitials & "*.xls"
    For i = 1 To UBound(FileArray)
    ListBox.AddItem Now() & ". . . . . ." & FileArray(i)
    'ACTIVATE CODE UPON ANSWERED QUESTION ON MULTIPLE SHEET NAMING
    Workbooks.Open FileName:=FileArray(i)
    Sheets("Statistical Data").Activate
    Range("A2:N2").Copy
    Windows("AdminDB").Activate
    Worksheets.Add
    With ActiveSheet
    .Name = FileArray(fName)
    Range("B2").Select
    Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
    False, Transpose:=False
    Range("B4").Select
    Windows(FileArray(i)).Activate
    Application.CutCopyMode = False
    Range("A5:N5").Copy
    'Application.CutCopyMode = False
    'Selection.Copy
    Windows("AdminDB").Show
    Range("B6").Select
    Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
    False, Transpose:=False
    'do something with returned filename (open it, take out the data you need, close it, put the data in a common worksheet, etc.)
    End With
    Next i
    End Sub
    ****CODE END****

    Any help would be GREATLY appreciated!

    Thanks!

    Drk.
    <IMG SRC=http://www.wopr.com/w3tuserpics/Kel_sig.gif>
    Moderator:<font color=448800> Pix Place, Internet Explorer</font color=448800>
    <small>www.kvisions.com

Page 1 of 3 123 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
  •