Import dynamic Excel worksheet ranges

[silverspr]

Hello, I'm trying to set up an import routine to run through a workbook with multiple worksheets and ranges for import into one table. The import will always start with Column/row A5. I'm at the moment just trying to cycle through each worksheet to ensure I'm capturing the last populated cell, but the routine I'm using below keeps giving the same last cell across all worksheets (when I know the last cell is different). Once the routine runs the workbook doesn't want to open, it flashes briefly on the screen, like its hidden or locked, is it something to do with how I'm calling excel, closing it...
Your assistance is appreciated: 1) why the last cell doesn't update with the active worksheet, 2) why workbook doesn't want to display after routine runs.

Sub ImportExcel()
Dim Range As Excel.Range
Dim excelapp As New Excel.Application
Dim excelbook As New Excel.Workbook
Dim excelsheet As New Excel.Worksheet
Dim intNoOfSheets As Integer, intCounter As Integer
Dim strFilePath As String
Dim LastColumn As Long
Dim LastRow As Long



strFilePath = "g:\Desktop\Summary Scoring for Lab Equip List for fiscals 2014-16.xls"
'strFilePath = "g:\Desktop\Service-Academic Split.xlsx"
 
Set excelbook = excelapp.Workbooks.Open(strFilePath)
 
intNoOfSheets = excelbook.Worksheets.Count
 
For intCounter = 1 To intNoOfSheets


  excelbook.Worksheets(intCounter).Activate
    LastRow = Cells.Find(What:="*", After:=[A1], _
                         SearchOrder:=xlByRows, _
                         SearchDirection:=xlPrevious).Row
    LastColumn = Cells.Find(What:="*", After:=[A1], _
                            SearchOrder:=xlByColumns, _
                            SearchDirection:=xlPrevious).Column
    MsgBox excelbook.Worksheets(intCounter).Name & "!A5:" & Cells(LastRow, LastColumn).Address
  Next


excelbook.Close
excelapp.Quit
Set excelapp = Nothing
 
End Sub

[June7]

I ran your code. It works so far as the MsgBox displays the correct cell references for each sheet.

This code leaves an instance of Excel still running even though the app appears closed. This can be seen in Windows task manager. I have run into this issue with numerous poster questions and it is soooo aggravating. Manually terminate the process in task manager then the file will open. But if I try to run the procedure again it errors. I debug/terminate code, end process, then run again and it works, with the same issue of residual process in task manager.

Review:
http://forums.aspfree.com/microsoft-...el-413629.html
http://forums.aspfree.com/microsoft-...et-413493.html

You are missing line that sets the excelapp object. Makes me wonder why the Set excelapp = Nothing doesn't error:
Set excelapp = CreateObject("Excel.Application")
Unfortunately, adding that line doesn't alter code behavior.

However, commenting the Find code and then just doing a Debug.Print of each sheet name does work. The workbook and app close and the process terminates. There is something about the Find operation that causes the Excel process to remain open.

Maybe this will help you fix that issue http://www.vbforums.com/showthread.p...rsion-of-Excel)
Sorry, I'm not able to.