Page 1 of 3 123 LastLast
Results 1 to 15 of 34
  1. #1
    4 Star Lounger
    Join Date
    May 2003
    Location
    Austin
    Posts
    401
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Automate routine (Excel 97-SR2)

    Every month, I compose a workbook that has a series of links from other workbooks, on one worksheet.

    Seeing that some months have 4 weeks and others have 5, there are only two possible layouts.
    440Rx9C is one, while the 5 week I believe is 660Rx9C ( I can guess what the numbers mean).
    The files are always in the same directory(but change subdirectories occassionally).
    The only problem, is these 'master sheets' are hidden from view.


    How would I automate this procedure via VBA or MACRO? <img src=/S/blackhole.gif border=0 alt=blackhole width=15 height=15>

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

    Re: Automate routine (Excel 97-SR2)

    First, what is your definition of a week? Technically, the lognest month has 4 3/7 weeks. What is your definition of a month with 5 weeks?

    Second, what process are you trying to automate? Creating the links to the other workbooks?

    If "these 'master sheets' are hidden from view" how are you using them now?
    Legare Coleman

  3. #3
    4 Star Lounger
    Join Date
    May 2003
    Location
    Austin
    Posts
    401
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Automate routine (Excel 97-SR2)

    Each employee has a workbook.
    Each workbook has 1 sheet for each day.
    There are 5 workdays per week.
    This means that there may be 20 or 25 active sheets per workbook.
    (I threw in a few separator sheets to make the layout less confusing for the user)
    There is an extra sheet in each workbook, which is hidden, that holds links from each page.
    This we'll call the IndiMaster.

    There is another workbook that we'll call GrpMaster.
    on one sheet, I paste links from each IndiMaster and occassionally update them during the month.

    My process involves...Opening each workbook-unhiding the sheet-selecting the range (440Rx9c or 660Rx9c)-copying
    then moving to the GrpMaster, paste-link in the first available area.
    Each sheet's array under the previous one.
    It gets real tedious when I have to open each book, unhide the page, copy,pastelink, close each book, not save the changes...blah blah blah

    I can't make a macro because the sheets always have a different name because of the month.
    Even though the directory is always the same, the file name changes.
    e.g.
    All IndiMasters would be called [empname]Sep01.xls while the GrpMaster would be called EOM_GroupSep01.xls.
    Hope that helps clarify and not confuse.
    Appreciate the assistance.
    <img src=/S/bow.gif border=0 alt=bow width=15 height=15>

  4. #4
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Automate routine (Excel 97-SR2)

    I'd guess his company/customer is using 13 week quarters comprising 4,4, & 5 week periods.
    -John ... I float in liquid gardens
    UTC -7ąDS

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

    Re: Automate routine (Excel 97-SR2)

    Ok, here is some code that may get you started. This code will search through a directory (hard coded as C:Work in this code) and find all of the files that have the current month and year as the last five characters of the file name. It then opens each workbook and copies "Sheet1" and pastes it onto "Sheet1" of the current workbook (obviously not what you want to do). See of this is on the right track. If so, then we can move on to expanding it to complete what you need to do.

    <pre>Public Sub GetWBData()
    Dim strPath As String, strFileName As String, strMMMYY As String
    Dim strWK As String
    Dim oUserWB As Workbook
    strPath = "C:Work"
    strMMMYY = Format(Now(), "MMMYY")
    strFileName = Dir(strPath & "*.xls", vbNormal)
    While strFileName <> ""
    strWK = Left(strFileName, InStr(strFileName, ".") - 1)
    strWK = Right(strWK, 5)
    If strWK = strMMMYY Then
    Set oUserWB = Workbooks.Open(Filename:=strPath & strFileName)
    oUserWB.Worksheets("Sheet1").Cells.Copy
    ThisWorkbook.Worksheets("Sheet1").Paste _
    Destination:=Worksheets("Sheet1").Range("A1")
    Application.DisplayAlerts = False
    oUserWB.Close
    Application.DisplayAlerts = True
    End If
    strFileName = Dir()
    Wend
    End Sub
    </pre>

    Legare Coleman

  6. #6
    4 Star Lounger
    Join Date
    May 2003
    Location
    Austin
    Posts
    401
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Automate routine (Excel 97-SR2)

    Excellent...Sorry. Somehow, I missed this post.
    This will go a long way. <img src=/S/bravo.gif border=0 alt=bravo width=16 height=30>
    Question:
    Does the size of the range matter?

    <font color=red>Oh oh..I'm getting an error [type mismatch]</font color=red>
    When I do the mouse over the line with an error, it states that strMMMYY = 12:00a.m.

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

    Re: Automate routine (Excel 97-SR2)

    Does the size of what range matter? What line are you getting the error on?
    Legare Coleman

  8. #8
    4 Star Lounger
    Join Date
    May 2003
    Location
    Austin
    Posts
    401
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Automate routine (Excel 97-SR2)

    <font color=blue>1</font color=blue>strMMMYY = Format-(<font color=red>(2)</font color=red>Now(), "MMMYY")
    (Mouse over(1)strMMYYY) indicates the value as 12:00 a.m.)
    (Mouse over(2)Now) indicates the value as 10/22/01-3:12:09p.m.)


    some sheets are 440rX9c others are 660rX9c
    I guess range size doesn't matter.

    Question
    If all files are located in subdirectories (1 for each employee)
    How would I call these individual pathnames for each strFileName?
    Examples:
    D:MyDirTimestuffDavid
    D:MyDirTimestuffDenise
    D:MyDirTimestuffDustbin

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

    Re: Automate routine (Excel 97-SR2)

    That line should read:

    <pre> strMMMYY = Format(Now(), "MMMYY")
    </pre>


    There should not be a minus sign beetween the Format and the left paren.


    Do you want to go through all the subdirectories of D:MyDirTimestuff, or do you want to call the procedure with different subdirectory names? If you want to automatically cycle through all of the subdirectories, that is a bit more complicated. If you want to call the procedure with a directory name as a parameter, then this should work:

    <pre>Public Sub GetWBData(strPath As String)
    Dim strFileName As String, strMMMYY As String
    Dim strWK As String
    Dim oUserWB As Workbook
    strMMMYY = Format(Now(), "MMMYY")
    strFileName = Dir(strPath & "*.xls", vbNormal)
    While strFileName <> ""
    strWK = Left(strFileName, InStr(strFileName, ".") - 1)
    strWK = Right(strWK, 5)
    If strWK = strMMMYY Then
    Set oUserWB = Workbooks.Open(Filename:=strPath & strFileName)
    oUserWB.Worksheets("Sheet1").Cells.Copy
    ThisWorkbook.Worksheets("Sheet1").Paste _
    Destination:=Worksheets("Sheet1").Range("A1")
    Application.DisplayAlerts = False
    oUserWB.Close
    Application.DisplayAlerts = True
    End If
    strFileName = Dir()
    Wend
    End Sub
    </pre>

    Legare Coleman

  10. #10
    4 Star Lounger
    Join Date
    May 2003
    Location
    Austin
    Posts
    401
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Automate routine (Excel 97-SR2)

    <font color=blue>*</font color=blue>-It's still tripping on that format line.
    Public Sub GetWBData()
    Dim strPath As String, strFileName As String, strMMMYY As Date
    Dim strWrk As String
    Dim oUserWB As Workbook
    strPath = "C:"
    <font color=blue>strMMMYY = Format(Now(), "MMMYY")</font color=blue>
    strFileName = Dir(strPath & "*.xls", vbNormal)
    While strFileName <> ""
    strWrk = Left(strFileName, InStr(strFileName, ".") - 1)
    strWrk = Right(strWk, 5)
    If strWk = strMMMYY Then
    Set oUserWB = Workbooks.Open(FileName:=strPath & strFileName)
    oUserWB.Worksheets("Sheet1").Cells.Copy
    ThisWorkbook.Worksheets("Sheet1").Paste _
    Destination:=Worksheets("MstSht").Range("Fiveweekm st")
    Application.DisplayAlerts = False
    oUserWB.Close
    Application.DisplayAlerts = True
    End If
    strFileName = Dir()
    Wend
    End Sub

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

    Re: Automate routine (Excel 97-SR2)

    The second line in the routine should read:

    <pre>Dim strPath As String, strFileName As String, strMMMYY As String
    </pre>


    You changed the definition of strMMMYY to Date, it should be String.
    Legare Coleman

  12. #12
    4 Star Lounger
    Join Date
    May 2003
    Location
    Austin
    Posts
    401
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Automate routine (Excel 97-SR2)

    Yeah, I got that about 2 minutes before you notified me. I swear.
    Thanks again.

    Strange...now, I get a Macro message box <img src=/S/help.gif border=0 alt=help width=23 height=15>

    As a means of cycling through all of the subdirectories..
    What if all of my applicable directories consisted of a 1 word string?

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

    Re: Automate routine (Excel 97-SR2)

    What do you mean that you get a macro message box? What does the message say?

    The CycleDir proceduer below will go through all of the subdirectories of D:MyDirTimestuff and call GetWBData for each one:

    <pre>Public Sub GetWBData(strPath As String)
    Dim strFileName As String, strMMMYY As Date
    Dim strWrk As String
    Dim oUserWB As Workbook
    strMMMYY = Format(Now(), "MMMYY")
    strFileName = Dir(strPath & "*.xls", vbNormal)
    While strFileName <> ""
    strWrk = Left(strFileName, InStr(strFileName, ".") - 1)
    strWrk = Right(strWK, 5)
    If strWK = strMMMYY Then
    Set oUserWB = Workbooks.Open(Filename:=strPath & strFileName)
    oUserWB.Worksheets("Sheet1").Cells.Copy
    ThisWorkbook.Worksheets("Sheet1").Paste _
    Destination:=Worksheets("MstSht").Range("Fiveweekm st")
    Application.DisplayAlerts = False
    oUserWB.Close
    Application.DisplayAlerts = True
    End If
    strFileName = Dir()
    Wend
    End Sub

    Public Sub CycleDir()
    Dim strPath As String, strDir As String
    Dim iAttr As Integer
    strPath = "D:MyDirTimestuff"
    strDir = Dir(strPath & "*.*", vbDirectory)
    While strDir <> ""
    iAttr = GetAttr(strPath & strDir)
    If iAttr = vbDirectory Then
    Call GetWBData(strDir)
    End If
    strDir = Dir
    Wend
    End Sub
    </pre>

    Legare Coleman

  14. #14
    4 Star Lounger
    Join Date
    May 2003
    Location
    Austin
    Posts
    401
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Automate routine (Excel 97-SR2)

    Macro message box attached

    I can now enter the CycleDir as a macro

    Then I get an invalid procedure call on the line...
    <font color=red>strDir = Dir</font color=red>

    I've triple checked the code and pathnames...

    The Help box says the procedure may not be supported.
    I'm on Excel97 sr-2 on a WinNT4 platform
    Is this a reference/library setting that can be swapped out?
    Attached Files Attached Files

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

    Re: Automate routine (Excel 97-SR2)

    That looks like what you should get if you select Macro from the tools menu, but I don't know of any reason you should get it if you didn't do that. Why don't you attach a workbook with the macros and let us take a look.
    Legare Coleman

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
  •