PDA

View Full Version : code to tell excel which folders to look into.



Sir Babydum GBE
09-09-2007, 05:46 AM
Hi,

Firstly, I'm going on annual leave when I finish today, so I hope you don't mind but I'm going to post this question in VBAX and MrExcel, as I'm hoping to get this piece finished before i go.

The purpose of the following code is to open up excel documents within the same folders as the coded document is sitting, then to copy information from those documents to itself.

I would like to amend this now but am not sure how.

My intention is to have a sheet entitled "Sources", then in column A, have a list of folder paths. So instead of just examining documents in the same folder - I want the macro to look at my list of sources, and then open all Excel workbooks that are in the folders I have indicated in that list.

How should the code be modified?

strPath = ThisWorkbook.Path
If strPath = "" Then
MsgBox "This workbook must be saved in directory first!"
Exit Sub
End If

Application.ScreenUpdating = False
strPath = strPath & Application.PathSeparator
strFile = Dir(strPath & "*.xls")
Set wksDest = ActiveSheet
lngTargRow = 3
Do Until strFile = ""
If Not strFile = ThisWorkbook.Name Then
Set wbk = Workbooks.Open(strPath & strFile, UpdateLinks:=0)
Set wksSource = wbk.Sheets(3)

If LCase$(wksSource.Cells(1, 1).Value) = "userworkbook" Then
Set rngLastCell = LastCellInSheet(wksSource)
If rngLastCell.Row > 2 Then
With wksSource
varData = .Range(.Cells(3, "A"), rngLastCell)
End With
lngRowCount = UBound(varData, 1)
lngColumnCount = UBound(varData, 2)
With wksDest
.Range(.Cells(lngTargRow, 1), .Cells(lngTargRow + lngRowCount - 1, _
lngColumnCount)).Value = varData
End With
lngTargRow = lngTargRow + lngRowCount
End If
End If
wbk.Close False
End If
strFile = Dir
Loop
fldr = Split(ThisWorkbook.FullName, "\")
fldr = fldr(UBound(fldr) - 1)
Thanks a lot

BD

mdmackillop
09-09-2007, 07:07 AM
Call your sub, passing the folder name. Note the first line in my code, which will show some undeclared varaiables. tut, tut!
Option Explicit

Sub SBD()
Dim cel As Range
For Each cel In Range(Cells(1, 1), Cells(Rows.Count, 1).End(xlUp))
Call SBD1(cel)
Next
End Sub


Sub SBD1(strpath As String)
'strpath = ThisWorkbook.Path
If strpath = "" Then
MsgBox "This workbook must be saved in directory first!"
Exit Sub
End If
'etc.

Sir Babydum GBE
09-17-2007, 09:15 AM
Thanks MD

It errored on the line Call SBD1(cel) highlighting "cel" (I changed SBD1 to my real macro name)

Bob Phillips
09-17-2007, 09:41 AM
Could it be becuase cel is a Range and the procedure signature calls for a string?

Bob Phillips
09-17-2007, 09:42 AM
What's this I see as well? Inno Setup?