locked
VBA in Excel RRS feed

  • Question

  • Hello everyone

    This is my first time with VBA coding and i need some help.

    I work at a facility which produces excel log files every day, i am supposed to create a macro that IMPORTS all the files and can access specific columns and get the average for those values and other related stuff.

    But the main issue is i need to know how to import multiple files into the worksheet (keep in mind that a new one is created every day) and how to access a specific column in all those files at the same time.

    thanks a lot.

    Thursday, November 8, 2012 3:33 PM

All replies

  • You should to be more specific. Details are rally important.

    The best way to get some help is attach link to file (representative sample) with little describe.

    You can use Skydrive, Google or another free server


    Oskar Shon, Office System MVP

    Press if Helpful; Answer when a problem solved

    Thursday, November 8, 2012 5:52 PM
    Answerer
  • This may not work for you but I set this up one time when I had to extract data from 100s of workbooks.  The data was in tabular format and could be treated as a database.  You can connect to Excel workbooks as a database and get the data using SQL statements.  What is good about this is you can access the data and not open the workbook.  It is really fast. The SQL can be complex.  You can use functions like AVG.

    http://support.microsoft.com/kb/257819?wa=wsignin1.0


    • Edited by mogulman52 Thursday, November 8, 2012 7:05 PM
    Thursday, November 8, 2012 7:04 PM
  • You could use code like this, in the summary file. Choose the file(s) with the data to import when the open file dialog appears:

    Sub ImportUserSelectedFiles()
        Dim i As Integer
        Dim wkbkB As Workbook
        Dim FileArray As Variant
        FileArray = Application.GetOpenFilename(MultiSelect:=True)
        If IsArray(FileArray) Then
            For i = LBound(FileArray) To UBound(FileArray)
                Set wkbkB = Workbooks.Open(FileArray(i))
                With wkbkB.Sheets(1).UsedRange
                'Copy column A and paste at the bottom of column A
                    Intersect(.Cells, .Range("A:A")).Copy _
                            ThisWorkbook.Worksheets(1).Cells(Rows.Count, 1).End(xlUp)(2)
                'Copy column C and paste at the bottom of column B
                    Intersect(.Cells, .Range("C:C")).Copy _
                            ThisWorkbook.Worksheets(1).Cells(Rows.Count, 2).End(xlUp)(2)
                End With
                wkbkB.Close False
            Next i
        Else:
            MsgBox "You clicked cancel"
        End If
    End Sub

    Thursday, November 8, 2012 7:52 PM