2 Replies Latest reply on Oct 24, 2013 10:33 AM by Jacob Picolet

    Import Data from multiple excel files

    Jacob Picolet

      I would like to pull in data from multiple excel files, but cannot seem to get the global "Cells" to switch to a new file.  I can pull in information if I only reference one excel file and I seem to be able to quit the first excel file and empty the placeholders, but cannot figure out how to "empty" the "Cells" portion.  I also tried using different names for the different excel variables, but had the same issue.  Anybody else have this issue?  The idea is to be able to pull in User Initials from one excel file and Other Infomation from a different excel file. 

       

      Thanks for the help,

      JP

        • Re: Import Data from multiple excel files
          Simon Turner

          "Cells" isn't global. It is a property of a Worksheet.

          You should define a Worksheet variable (e.g. xlSheet) and set it to the ActiveSheet in the Workbook.

          Something like set xlSheet = xlApp.ActiveWorkbook.ActiveSheet

          Or Set xlBook = xlApp.Workbooks.Open(someXLfilename)

          Set xlSheet = xlBook.Sheets(1)

          Then you can use xlSheet.Cells

            • Re: Import Data from multiple excel files
              Jacob Picolet

              I suppose I used the wrong terminology to explain "Cells".  I was meaning to refer to the need to include the Microsoft Excel Reference for "Cells" to work and how the API refers to it as a Member of the Excel.Global.

               

              I figured out the issue, being that I was using a With xlWB.Worksheets("Sheet Example") statement and for whatever reason using a With statement doesn't work on the second excel file.  So like Simon said above, as long as you define and use xlws.cells it will work.