Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Page 1 of 2 12 LastLast
Results 1 to 10 of 15

Thread: Whoever answers this will be a god!!!!!!!!

  1. #1
    Board Regular
    Join Date
    Apr 2002
    Posts
    76
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    I have this folder you see containing about 60 different workbooks. I have this macro that goes into each one and copies certain data and pastes it into a summary workbook. The thing is, I have only just finished doing it for TWO people!! And that's taken me ages to record!!! Is there any way of telling the macro what to do, and commanding it to do it to all the workbooks in the folder as it's the same operation I need over and over again. The code is here:

    Workbooks.Open FileName:= _
    "bluehbennettCustomer Services DebtJoe Bloggs.xls"
    Range("A2").Select
    ActiveWindow.SmallScroll Down:=228
    Range("A2:J250").Select
    Selection.Copy
    Windows("Statistics Collation.xls").Activate
    Range("A4").Select
    ActiveSheet.Paste
    Range("A4").Select
    Windows("Joe Bloggs.xls").Activate
    Application.CutCopyMode = False
    ActiveWindow.LargeScroll Down:=-8
    Range("A1").Select
    Sheets("Sheet1").Select
    ActiveWindow.Close

    I will be sooooooooo grateful if someone can help as I am completely stumped (as usual!). Thank you, thank you, thank you in advance

    Janie
    xxxxxxx

    [ This Message was edited by: buntykins on 2002-04-25 07:48 ]

  2. #2
    Board Regular
    Join Date
    Mar 2002
    Location
    Milwaukee, WI
    Posts
    105
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Do you always paste it to A4?

  3. #3
    Board Regular
    Join Date
    Apr 2002
    Posts
    76
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    No, I actually paste it to the next available row, but I know how to record that "Go to A65536 and then press CTRL Up" thing so I thought I could insert that somewhere. I know I sound really stupid it's just that this is something I have been working on for months now, and it's nearly completed, so I'm getting all flustered and excited about it!

    if it's possible I'd like it to paste the first one to A4, then the next one to A254, then the next one to A504, then to A754 and so on, going up in 250's. I don't suppose anyone knows how to do that as well do they?

    Janie
    xx

    [ This Message was edited by: buntykins on 2002-04-25 08:05 ]

  4. #4
    Board Regular
    Join Date
    Mar 2002
    Location
    Milwaukee, WI
    Posts
    105
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    My manuAl method would be to manually open the workbook, run this code, then open the next workbook, etc. Should only take a few minutes for 60 workbooks. I made the assumption that you are copying the data to the next empty row.

    ThisSheet = ActiveWindow.name
    Range("A2:J250").Copy
    Windows("Statistics Collation.xls").Activate
    Range("A65536").End(x1Up).Offset(1,0).Select
    ActiveSheet.Paste
    Windows(ThisSheet).Activate
    ActiveWindow.Close

  5. #5
    New Member
    Join Date
    Apr 2002
    Posts
    3
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    You need to make use of the 'Microsoft Scripting Runtime' reference, if you have not already. (Tools->References...)

    You need to use the following:

    Dim objFSO As New FileSystemObject
    Dim fol As Folder
    Dim fls As Files
    Dim fil As File

    Set fol = objFSO.GetFolder()

    Set fls = fol.Files

    For Each fil In fls

    If (fil.Type = "Microsoft Excel Worksheet") Then


    Range("A4").Select
    ActiveSheet.Paste
    Range("A4").Select
    >

    End If

    Next

    Set fil = Nothing
    Set fls = Nothing
    Set fol = Nothing
    Set objFSO = Nothing

    Note: You need to specify the folder somehow. You could enter the path in a cell perhaps.

    -----
    Jason

  6. #6
    Board Regular
    Join Date
    Mar 2002
    Location
    =ActiveCell.Address
    Posts
    478
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default


    Just because I can't resist a post with a subject line like this one(!) try the below. It moves each file to a processed subdirectory after it's finished with the file which is something I've found helpful...

    Sub getdata()

    Dim MyPath As String
    Dim MyFile As String
    Dim MyIncrement As Long

    'Set start row for pasting.
    MyIncrement = 4

    'Change the drive to whatever drive you're on
    ChDrive "C"


    'Define Path - change to whatever..
    MyPath = "C:temp"

    'Change directory to your directory
    ChDir (MyPath)

    ' See if a subdirectoy called "Processed" exists
    MyCheckDir = Dir(MyPath & "Processed", vbDirectory)

    'If it doesn't exist then create it
    If MyCheckDir = "" Then
    MkDir MyPath & "Processed"
    End If

    ' Now go looking for Excel files to process
    ChDir MyPath & ""
    MyFile = Dir("*.xls", vbNormal)

    Do While MyFile <> ""

    Workbooks.Open Filename:=MyFile

    'Do your stuff here (have shortened the code a bit - there were some unnecessary lines)...

    Range("A2:J250").Copy
    Windows("Statistics Collation.xls").Activate
    Range("A" & MyIncrement).Select
    ActiveSheet.Paste
    Application.CutCopyMode = False

    'Switch back to the open Excel file and close it, without saving
    Windows(MyFile).Activate
    ActiveWorkbook.Close SaveChanges:=0

    'Copy the file we've processed to the Processed Directory
    FileCopy MyPath & "" & MyFile, MyPath & "Processed" & MyFile
    'Delete original
    Kill MyFile

    ' Call Dir again without arguments to return the next *.XLS file in
    ' the same directory.
    MyFile = Dir

    'Increment the value for pasting
    MyIncrement = MyIncrement + 250

    Loop
    End Sub


    Rgds
    AJ

  7. #7
    Board Regular
    Join Date
    Apr 2002
    Posts
    76
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    You'll have to forgive me, both of you but I've never seen anything like either of these things before, so I'm not exactly sure how to implement them. Both of them sound intriguing but how do I get the going?

    Jrnyman - How do I specify in your code which folder to look in to open all the workbooks?

    Jasonking - When you say specify the folder, could I put this in the code somewhere, and if so, where?

    Sorry to be so blonde! Thanks heaps to both of you!

    Janie
    xxxxx

  8. #8
    Board Regular
    Join Date
    Apr 2002
    Posts
    76
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    AJ, this sounds perfect, but when you say about the processed subdirectory, where would this be? What would it be called?

    Janie

  9. #9
    Board Regular
    Join Date
    Mar 2002
    Location
    =ActiveCell.Address
    Posts
    478
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hiya,

    In the example it would be C:tempprocessed. Basically, it creates a processed directory off of whatever path you put in in the define path bit near the top of the macro so you can change that bit however you want.

    Rgds
    AJ


    (P.S. remember, anywhere you see two backslashes on this board always swap them for just one - the board seems to automagically add a backslash before a lot of characters in postings, one of which characters is a backslash! Hence the doubling up.)

  10. #10
    New Member
    Join Date
    Apr 2002
    Posts
    3
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Apologies, my posted message messed up when sending.

    Btw, Your code will go within the 'If' statement. (That's one bit that messed up).

    You can specify the folder's path in the code by entering:

    Set fol = objFSO.GetFolder("c:workbooks")

    Or you could enter the path in a cell of the current workbook (say cell 'A1'). Then the code would be:

    Set fol = objFSO.GetFolder(Range("A1")

    -----
    Jason

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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