?
Solved

Modify Excel Files Using a Script

Posted on 2010-01-11
5
Medium Priority
?
531 Views
Last Modified: 2012-05-08
I have over several hundred files in Excel which all, for the most part, have the same format (all columns in the same place, same header rows, etc., etc.,).

What I'd like to do is use a script/macro/something to:

1. Open each file (either manually or through some recursive method which runs through the files in the target directory)
2. Add a column to end of the range of each file (this should error out if the position of the column to be added is not equal to a constant which can be defined in the script (Column must equal Column 10)
3. That column would be titled NEWCOLUMN and then the rows (up to the last row) would be populated with the File Name of the target file.

I've attached a sample file (with fake data of course) with a BEFORE and AFTER tab for an idea of what the file starts like and what it should end like.

The files have varying rows, anywhere from 100+ to 10,000's+...

Thanks!!!


Test-Book.xls
0
Comment
Question by:fbmartinez
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
5 Comments
 
LVL 6

Expert Comment

by:CowboyJeeper
ID: 26283466
How about something like this?
Const COL_NUMBER As Integer = 10
Const COL_VALUE As String = "Your new heading"

Sub addcol()
    Sheets("sheet1").Select
    If Cells(1, COL_NUMBER) = Empty Then
        Cells(1, COL_NUMBER) = COL_VALUE
    End If
End Sub

Open in new window

0
 
LVL 6

Expert Comment

by:CowboyJeeper
ID: 26283485
I forgot to mention in there that I selected a sheet first to make sure it was on the correct sheet to add the column to.  Since I don't know how many sheets your hundreds of files have and they most certainly wont all be on the correct sheet if they have multiples.  You may however have to change the sheet name.
0
 
LVL 1

Author Comment

by:fbmartinez
ID: 26283657
You missed the essential part of the question/request here. The files need to be opened by the script or the script should run automatically as I open each file.

The column heading should be added, and the name of the file should be derived from the file - not manually entered.
0
 
LVL 6

Accepted Solution

by:
CowboyJeeper earned 1500 total points
ID: 26284818
This should be a little more to what you need then I think.
Const COL_NUMBER As Integer = 10
Const COL_VALUE As String = "Your new heading"
 
Function addcol()
    Sheets("sheet1").Select
    If Cells(1, COL_NUMBER) = Empty Then
        Cells(1, COL_NUMBER) = COL_VALUE
    End If
End Function

Function ParsePath(strPath As String) As String
    ParsePath = Left$(strPath, InStrRev(strPath, "\"))
End Function

Function OpenF(Title As String) As String
    Dim wb As Workbook
    fname = Application.GetOpenFilename("Excel-files,*.xls", , Title)
    'no file selected
    If fname = False Then Exit Function
    For Each wb In Application.Workbooks
        If wb.Path & "\" & wb.Name = fname Then
            'MsgBox "File " & wb.Name & " is already open"
            OpenF = wb.Name
            Exit Function
        End If
    Next
    'Workbooks.Open (fname)
    OpenF = fname
End Function

Function FileList(fldr As String, Optional fltr As String = "*.*") As Variant
    Dim sTemp As String, sHldr As String
    If Right$(fldr, 1) <> "\" Then fldr = fldr & "\"
    sTemp = Dir(fldr & fltr)
    If sTemp = "" Then
        FileList = False
        Exit Function
    End If
    Do
        sHldr = Dir
        If sHldr = "" Then Exit Do
        sTemp = sTemp & "|" & sHldr
     Loop
    FileList = Split(sTemp, "|")
End Function

Sub testit()
    Dim list As Variant
    Dim folderPath As String
    
    folderPath = OpenF("Select a file in the directory you wish to look at")
    folderPath = ParsePath(folderPath)
    list = FileList(folderPath, "*.xls")
    For Each doc In list
        MsgBox folderPath & s
        Workbooks.Open Filename:=folderPath & s
        retVal = addcol()
        ActiveWorkbook.Save
        ActiveWindow.Close
    Next doc
End Sub

Open in new window

0
 
LVL 1

Author Closing Comment

by:fbmartinez
ID: 31675485
This helped me get where I needed to get, but it wasn't precisely what my original question was looking to solve. Thank you!!!
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

With User Account Control (UAC) enabled in Windows 7, one needs to open an elevated Command Prompt in order to run scripts under administrative privileges. Although the elevated Command Prompt accomplishes the task, the question How to run as script…
This article describes how to use a set of graphical playing cards to create a Draw Poker game in Excel or VB6.
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.

777 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question