Looping though *.xls files except for the main consolidation file

T

Thomas

Hi,

This is my first post to any Group. Any help would be appreciated.

I have some excel VBA code that sets the current path of the main
workbook (BaseBook) and the loops through all .xls files in that path,
opens the file (FNames) and copies all non hidden worksheets to the
main workbook (BaseBook).

This works fine except the code (obviously) tries to re-open the main
workbook (BasebBook) at some point in the loop.

I need some help to test if the file about to be opened (FNames) is
the main workbook (BaseBook) and if so, skip to the next FNames file.

I know in simple terms how to do this but lack the VBA expreriance to
write this bit. I have never done a VBA course, but have picked up
many different things over the years.

I have gotten around this by saving Basebook with a file extension
like "workbnookname.aaa" I need to find a dynamic way of coding this
test, rather than fixing the file name as a fixed name.

Code is posted below: Problem in Part2:

Sub Testing()

Dim Basebook As Workbook
Dim Mybook As Workbook
Dim FNames As String
Dim MyPath As String
Dim SaveDriveDir As String
Dim Answer
Dim mySht As Variant

SaveDriveDir = CurDir 'Current file drive

Part1:

MyPath = ActiveWorkbook.Path ' File locations

ChDrive MyPath
ChDir MyPath

FNames = Dir("*.xls")
If Len(FNames) = 0 Then
MsgBox "No files in the Directory"
ChDrive SaveDriveDir
ChDir SaveDriveDir
Exit Sub
End If

Application.AskToUpdateLinks = False
Application.ScreenUpdating = False

Part2:

Set Basebook = ThisWorkbook ' Main consolidation macro

Do While FNames <> "" ' Loop through *.xls files in
' current directory


' My poor attempt at testing the file name...
' just will not work.

' If Basebook.Name = FNames Then
' GoTo Part4
' Else: GoTo Part3
' End If


Part3:
Set Mybook = Workbooks.Open(FNames)
On Error Resume Next


'Worksheet Loop - Loops through visible sheets only
'
For Each mySht In ActiveWorkbook.Sheets

If mySht.Visible = True Then


mySht.Activate

mySht.Copy
after:=Basebook.Sheets(Basebook.Sheets.Count)

End If

Next mySht

On Error GoTo 0

Mybook.Close False
FNames = Dir()

Part4:

Loop

Part5:

ChDrive SaveDriveDir
ChDir SaveDriveDir
Application.ScreenUpdating = True



Part6: 'Used to move the sheet order around to facilitate
consolidation formula

'Move Sheet to Front

Sheets("Consol").Move Before:=Sheets(1)
Sheets("First").Move Before:=Sheets(2)

'Move Sheet to End
Sheets("Last").Move after:=Sheets(Sheets.Count)

Finish:

ActiveWorkbook.Worksheets(1).Select
Calculate

End Sub




Any help would be appreciated.

Thanks again.
Thomas
 
M

mudraker

You almost have it

with you attempt code change

If Basebook.Name = FNames Then


to

If Basebook.Name = FNames.Name The
 
T

Thomas

Thanks... does not work as expected. I receive an error

Compile Error - Invalid qualifier.

Also, not sure if my error trapping is set properly.

I do appreciate your help.

Thomas





*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top