+ Reply to Thread
Results 1 to 5 of 5

how to batch processing excel worksheet modifications?

  1. #1
    neptune
    Guest

    how to batch processing excel worksheet modifications?

    Hi,

    I hope that anyone can help me. I have 300 excel files to process, I
    don't what to have to open all these files one by one. What I have to
    do is the following: I have to modify the excel files in some simple
    way, add a column and insert the filename of the corresponding file in
    this column for every line items of my excel file.

    example :

    dir \name_my_excels\ has 2 files, fileA(5 lines) and fileB(10 lines)

    To do:

    open fileA, add a column, insert the filename in the columnF for the 5
    active lines and close the file,

    open fileB, add a column, insert the filename in the columnF for the 10
    active lines and close the file,

    and so on, this for 300 times.

    Is there a way that I can do this on one files and batch process my
    directory to have it done on all my excel files?

    A quick respond an a software, macro or anything like this would same
    me time and energy.

    Thanks a lot,

    Marc
    ps. Can anyone cross post this message for me, I'm new to groups


  2. #2
    Forum Contributor
    Join Date
    11-16-2004
    Posts
    282
    Here's a macro you can use (replace code in RED below w/your own references):

    Sub processExcelFiles()
    ' Declare variables
    Dim a(999999), i
    Dim Msg, style, Title
    Dim myDrive, myLocation, LoadDir As String
    Dim counter, loopLimit
    ' Initialize variables...
    ' *** IMPORTANT *** Replace the drive name "C" below w/ your reference
    myDrive = "C"
    ' *** IMPORTANT *** Replace the file path below w/ your reference
    myLocation = "yourDirectory1\yourDirectory2"


    ' Turn off screen updating (program runs faster)...
    Application.ScreenUpdating = False

    ' Notify user of progress...
    oldStatusBar = Application.DisplayStatusBar
    Application.DisplayStatusBar = True
    Application.StatusBar = "Searching for files; please wait..."
    newHour = Hour(Now())
    newMinute = Minute(Now())
    newSecond = Second(Now()) + 2
    waitTime = TimeSerial(newHour, newMinute, newSecond)
    Application.Wait waitTime

    'Set the filepath to the correct directory...
    ChDrive myDrive
    ChDir myDrive & ":\" & myLocation

    ' Initialize the array counter...
    i = 0
    ' Load array with names of Excel files in correct directory...
    a(i) = Dir("*.xls")
    ' If no Excel files are present, alert user & exit program...
    If a(i) = "" Then
    GoTo FilesMissing ' the error handler...
    Exit Sub
    Else
    ' Loop through the Excel files to count files as loop limit...
    Do
    i = i + 1
    a(i) = Dir()
    Loop Until a(i) = ""
    ' Count the number of names in the array...
    fileCount = CStr(i)
    ' Notify user of number of files to be protected...
    Application.StatusBar = "Number of files to process: " & fileCount & " - please wait..."
    newHour = Hour(Now())
    newMinute = Minute(Now())
    newSecond = Second(Now()) + 2
    waitTime = TimeSerial(newHour, newMinute, newSecond)
    Application.Wait waitTime
    ' Start looping through the Excel file(s) to open & process each one...
    For MyFilCount = 0 To (fileCount - 1)
    LoadDir = CurDir & "\"
    Workbooks.Open LoadDir & (a(MyFilCount)), UpdateLinks:=0, _
    ReadOnly:=False, IgnoreReadOnlyRecommended:=True
    ' Provide file processing status to user ...
    Application.StatusBar = _
    "Processing file " & MyFilCount + 1 & " of " & fileCount & ": " & a(MyFilCount) & "; please wait..."
    newHour = Hour(Now())
    newMinute = Minute(Now())
    newSecond = Second(Now()) + 1
    waitTime = TimeSerial(newHour, newMinute, newSecond)
    Application.Wait waitTime
    ' Get number of rows for repeat loop upper limit...
    loopLimit = ActiveSheet.UsedRange.Rows.Count
    counter = 0
    ActiveSheet.Range("F1").Select
    ' Add filename to each row in column F...
    Do Until counter = loopLimit
    counter = counter + 1
    ActiveCell.Value = ActiveWorkbook.Name
    ActiveCell.Offset(1, 0).Select
    Loop
    ActiveWorkbook.Save
    ActiveWorkbook.Close SaveChanges:=True
    Next MyFilCount
    ' Reset screen updating and status bar...
    Application.ScreenUpdating = True
    Application.StatusBar = False
    Application.DisplayStatusBar = oldStatusBar
    ' Define user dialog parameters
    Msg = "File processing is now complete."
    style = vbOKOnly + vbInformation + vbDefaultButton1
    Title = "File Processing Status"
    ' Display user dialog
    Response = MsgBox(Msg, style, Title)
    End If
    Exit Sub
    'Error handler if no file(s) exist in directory...
    FilesMissing:
    ' Define user dialog parameters
    Msg = "There are no files located in the " & myDrive & ":\" & myLocation & " directory." & Chr(13) & _
    "The program stopped and no updates were made."
    style = vbOKOnly + vbCritical + vbDefaultButton1
    Title = "Missing File(s)"
    ' Display user dialog
    Response = MsgBox(Msg, style, Title)
    ' Reset screen updating and status bar...
    Application.ScreenUpdating = True
    Application.StatusBar = False
    Application.DisplayStatusBar = oldStatusBar
    End Sub

    Hope this helps,
    theDude

  3. #3
    Earl Kiosterud
    Guest

    Re: how to batch processing excel worksheet modifications?

    Neptune,

    A macro could do it. It would have to be written, and you'd have to be able
    to put it into a workbook. You'd want to put a copy of the files in a
    separate folder (certainly don't start processing your only copy of the
    files).

    --
    Earl Kiosterud
    mvpearl omitthisword at verizon period net
    -------------------------------------------

    "neptune" <neptune245@yahoo.ca> wrote in message
    news:1105631843.598307.320990@c13g2000cwb.googlegroups.com...
    > Hi,
    >
    > I hope that anyone can help me. I have 300 excel files to process, I
    > don't what to have to open all these files one by one. What I have to
    > do is the following: I have to modify the excel files in some simple
    > way, add a column and insert the filename of the corresponding file in
    > this column for every line items of my excel file.
    >
    > example :
    >
    > dir \name_my_excels\ has 2 files, fileA(5 lines) and fileB(10 lines)
    >
    > To do:
    >
    > open fileA, add a column, insert the filename in the columnF for the 5
    > active lines and close the file,
    >
    > open fileB, add a column, insert the filename in the columnF for the 10
    > active lines and close the file,
    >
    > and so on, this for 300 times.
    >
    > Is there a way that I can do this on one files and batch process my
    > directory to have it done on all my excel files?
    >
    > A quick respond an a software, macro or anything like this would same
    > me time and energy.
    >
    > Thanks a lot,
    >
    > Marc
    > ps. Can anyone cross post this message for me, I'm new to groups
    >




  4. #4
    marko
    Guest

    Re: how to batch processing excel worksheet modifications?

    I believe a macro can do this, but you've got to be a lot clearer on
    what you want it to do. If you're going to write the macro, I would
    have a flowchart of what needs to be done here. What is the
    'corresponding filename' you're referring to. That's what really threw
    me.


  5. #5
    Ken Macksey
    Guest

    Re: how to batch processing excel worksheet modifications?

    Hi

    Be sure to make back up copies all wokbooks first.
    Always work with copies of the workbooks
    and test your code until you are satisfied
    that everything works the way you want it to.

    This code will go thru all of the workbooks in a folder.

    Write the specific code you need to be performed and insert it where shown.
    Then test,test,test until you are sure!!!





    Application.ScreenUpdating = False
    Application.DisplayAlerts = False

    With Application.FileSearch
    .NewSearch
    .LookIn = "C:\temp 1" ' your drive / directory here
    .SearchSubFolders = True
    .FileName = ".xls" ' all files ending in xls
    .FileType = msoFileTypeExcelWorkbooks
    If .Execute() > 0 Then
    ' how many files are there in the selected folder?
    MsgBox "There were " & .FoundFiles.Count & " file(s) found."

    For i = 1 To .FoundFiles.Count
    Workbooks.Open .FoundFiles(i), 0
    '
    'The code to select a column
    '
    'and insert a column
    '
    'and insert the file name
    '
    'in the columns and
    '
    'whatever else you need to do goes here.
    '
    ActiveWorkbook.Save
    ActiveWorkbook.Close
    Next i
    Else
    MsgBox "There were no files found."
    End If
    End With


    Application.ScreenUpdating = True
    Application.DisplayAlerts = True

    MsgBox "All Done!"




+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1