Results 1 to 7 of 7
  1. #1
    Join Date
    Feb 2012
    Posts
    188

    Unanswered: Insert Column Into Workbooks

    I posted this same question here (but haven't been able to get a resolution):

    Insert Column Into Workbooks - Tech Support Guy Forums

    I want to take a workbook, let's call it "Master" for example sake, I want to take column "O" from this workbook, and insert this column as hidden into ALL workbooks here (for examples sake let's call them destination):
    C:\Files\Excel\Workbooks\

    So basically I would need to open each workbook in that directory one by one, and when the workbook is opened copy/paste column "O" from master into the destination workbook, save the destination workbook, close the destination workbook then open the next destination workbook and repeat

    I know how to open all workbooks in a directory, but the issue that I am running into is 1) differentiating between "Master" and "Destination" and then also making the column be hidden.

    Can someone provide a script or VBA for Excel that can conquer this feat?

  2. #2
    Join Date
    Apr 2004
    Location
    Derbyshire, UK
    Posts
    789
    Provided Answers: 1
    Hi

    This code simulates you problem
    Code:
    Sub CopyColumn()
        Dim File(1) As String
        Dim i As Integer
        
        File(0) = "C:\temp\Test1.xls"
        File(1) = "C:\temp\test2.xls"
        
        Columns("O:O").Select
        Selection.Copy
        
        Application.ScreenUpdating = False
        For i = 0 To UBound(File)
            Workbooks.Open File(i)
            Columns("E:E").Select
            ActiveSheet.Paste
            ActiveWorkbook.Close SaveChanges:=True
            
        Next i
        Application.ScreenUpdating = True
    End Sub
    Note the copy statement is outside the loop hence it is 'remembered' for all the subsiquent paste operation which are in the active (opened) workbook.

    HTH


    MTB

  3. #3
    Join Date
    Feb 2012
    Posts
    188
    So if I am following your code....I would have to list out the name of each workbook in order to have the workbook opened and the column pasted in?

    If that is the case, is there a VBA procedure that would write the name of all of the workbooks in a directory to a text file, so I could just copy/paste, or am I just going to have to go through and manually input each workbook from the directory into the code you posted above?

  4. #4
    Join Date
    Apr 2004
    Location
    Derbyshire, UK
    Posts
    789
    Provided Answers: 1
    Hi again

    The code below is a modified version of a routine I wrote last week.
    Code:
    Sub Main_TranferData()
    On Error GoTo ErrorHandler
        Dim File As String
        Dim FD As FileDialog
        Dim FileItem As Variant
        Dim DefaultPath As String
        
        Columns("O:O").Select
        Selection.Copy
        
        DefaultPath = "C:\temp" 'Your stating path goes hear
    
        If Dir(DefaultPath, vbDirectory) = "" Then DefaultPath = "C:\"
            
        Set FD = Application.FileDialog(msoFileDialogFilePicker)
        
        With FD
            .Filters.Add "Excel Files (*.xls)", "*.xls*"
            .AllowMultiSelect = True
            .Title = "Column 'O' Copy:  Select Files to Copy To"
            .InitialFileName = DefaultPath
            If .Show = -1 Then
                Application.ScreenUpdating = False
                For Each FileItem In .SelectedItems
                    Application.StatusBar = "Copying Column to file '" & Mid(FileItem, InStrRev(FileItem, "\") + 1) & "'.  Please waite"
                    Workbooks.Open FileItem
                    Range("J1").Select
                    ActiveSheet.Paste
                    ActiveWorkbook.Close savechanges:=True
                Next FileItem
            End If
        End With
    
    ErrorHandler:
        Application.StatusBar = False
        Application.ScreenUpdating = True
        If Err.Number > 0 Then
            MsgBox Err.Description, vbExclamation, "Error"
        End If
    End Sub
    The dialogue box is multi-select, so you can pick as many files in the selected folder as you like.
    They will then be opened in turn and the column pasted (in this case) into column 'J'.

    HTH a little more


    MTB

  5. #5
    Join Date
    Feb 2012
    Posts
    188
    Thank you for the prompt response....

    When I try to execute this code in Excel 2000 I get the following compile error:
    User defined Type Not Defined
    Code:
    Dim FD As FileDialog

    When I try to execute this code in Excel 2007 it will open up the workbook to copy to, but once it has made it to the Error Handler I get a message of:

    Selected Method of Range Class Failed

  6. #6
    Join Date
    Apr 2004
    Location
    Derbyshire, UK
    Posts
    789
    Provided Answers: 1
    Hi

    You did not say whether the column was actualy copied. I assume not!

    I've modhified the code slgihtly below
    Code:
    Sub Main_TranferData()
    On Error GoTo ErrorHandler
        Dim File As String
        Dim FD As FileDialog
        Dim FileItem As Variant
        Dim DefaultPath As String
        
        Columns("O:O").Select
        Selection.Copy
        
        DefaultPath = "C:\temp" 'Your stating path goes hear
    
        If Dir(DefaultPath, vbDirectory) = "" Then DefaultPath = "C:\"
            
        Set FD = Application.FileDialog(msoFileDialogFilePicker)
        
        With FD
            .Filters.Add "Excel Files (*.xls)", "*.xls*"
            .AllowMultiSelect = True
            .Title = "Column 'O' Copy:  Select Files to Copy To"
            .InitialFileName = DefaultPath
            If .Show = -1 Then
                Application.ScreenUpdating = False
                For Each FileItem In .SelectedItems
                    Application.StatusBar = "Copying Column to file '" & Mid(FileItem, InStrRev(FileItem, "\") + 1) & "'.  Please waite"
                    Workbooks.Open FileItem
                    Range("J1").Select
                    ActiveSheet.Paste
                    ActiveWorkbook.Close savechanges:=True
                Next FileItem
            End If
        End With
    ExitSub:
        Application.StatusBar = False
        Application.ScreenUpdating = True
        
        Exit Sub
    ErrorHandler:
        If Err.Number > 0 Then
            MsgBox Err.Description, vbExclamation, "Error"
        End If
        GoTo ExitSub
    End Sub
    Just tidied up the error handler.

    If the file to copy is opened then I can olny assume that the file is protected or something, as I connot see why the Range("J1").Select could fail otherwise!!??

    I have run this on both 2003 and 2007 without problem.


    MTB

  7. #7
    Join Date
    Feb 2012
    Posts
    188
    Code works in 2007

    Few small tweaks...would it be possible to mirror the formatting that is in the "source" workbook to the workbooks the column is being pasted into? For example, the column that is being copied shows percentage and 2 decimal places, once it is pasted it is showing more than 2 decimal places.

    Also, in some cases it is throwing off the row height. Rows 5 - 90 the row height should be 15.75. I could be wrong, (still fairly new to VBA) this should be easy tweaks...if you have the time, and don't mind sparing the assistance?

Posting Permissions

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