Almost there..Code to pull data from multiple worksheets (1 Viewer)

Sketchin · Mar 31, 2017

Hello,

I am using the following code to pull data from multiple spreadsheets in a directory tree in to one master sheet called "Results.xlsx"

I would like to modify the code to only pull data from Row 3+ (until a blank row is encountered) of every sheet that contains data except the 1st sheet which has a common name in all sheets.

The code in its current form:

Code:

Private Sub commandButton1_Click()

strPathSrc = "S:\Programs\IND\IND EOI Applications\Internal Review Meetings Summary\2016" ' Source files folder
strMaskSrc = "*.xlsx" ' Source files filter mask
isheetsrc = 2 ' Source sheet index or name
strPathDst = "C:\test\Results\Results.xlsx" ' Destination file
iSheetDst = 1 ' Destination sheet index or name

Set objExcel = CreateObject("Excel.Application")
objExcel.Visible = True
Set objWorkBookDst = objExcel.Workbooks.Open(strPathDst)
Set objSheetDst = objWorkBookDst.Sheets(iSheetDst)
Set objShellApp = CreateObject("Shell.Application")
Set objFolder = objShellApp.Namespace(strPathSrc)
Set objItems = objFolder.Items()
objItems.Filter 64 + 128, strMaskSrc
objExcel.DisplayAlerts = False
For Each objItem In objItems

      Set objWorkBookSrc = objExcel.Workbooks.Open(objItem.Path)
    Set objSheetSrc = objWorkBookSrc.Sheets(isheetsrc)


    GetUsedRange(objSheetSrc).Copy
    Set objUsedRangeDst = GetUsedRange(objSheetDst)
    iRowsCount = objUsedRangeDst.Rows.Count
    objWorkBookDst.Activate
    objSheetDst.Cells(iRowsCount + 1, 1).Select
    objSheetDst.Paste
    objWorkBookDst.Application.CutCopyMode = False
    objWorkBookSrc.Close

Next

End Sub


Function GetUsedRange(objSheet)
    With objSheet
        Set GetUsedRange = .Range(.Cells(1, 1), .Cells(.UsedRange.Row + .UsedRange.Rows.Count - 1, .UsedRange.Column + .UsedRange.Columns.Count - 1))
    End With
End Function

I'm familiar with VBA in Access, but it seems that Excel is it's very own beast.

Thanks for the help

Rx_ · Jun 1, 2017

I have been off for a while, saw this older post.
Look at this:
https://www.access-programmers.co.uk/forums/showthread.php?t=273254&highlight=objxl
It is not exactly what you are asking for. But, it shows the process of how to evaluate a cell by cell and set the formatting, color and such.
You can search my other post by filtering the user name in the Excel area.
Somewhere, there is a post that evaluates the Blank, Null and the hard to find single quote (when a cell is blank for text in a number column, there is a single quote).

Just to give you a head's upj.
The vast majority of my code samples are run from MSAccess.
MSAcces sets a reference to Excel, then runs Excel vba with Remote Automation.
My use of objXL is the object reference to Excel.Applicaiton
This allows a form's button in Access to open up an existing Excel (or two at a time) then perform the process.
The intent was to post these code segments to remind myself of the keywords as I move from one contract to another.

Almost there..Code to pull data from multiple worksheets (1 Viewer)

Sketchin

Registered User.

Rx_

Nothing In Moderation

Similar threads

Users who are viewing this thread