Macro issue

[Farhad]

Hi all,

i am just trying to count how many excel files are in each folders that i
want to process in them i try the code below but i got error " type miss
match" and my other question is that i want to activate the last tab in each
file that i open can any body help me on this?

Dim fs As Application.FileSearch
Set fs = Application.FileSearch
With fs
.LookIn = "L:\Toshiba\" & Trim(Str(j)) & "\" & kk
.Filename = "*.xls"
mmm = .FoundFiles.Count


thanks,

 

[Mike H]

Hi,

I don't understand how your setting up the path but you should be able to
modify this

Sub OpenFiles()
Dim FSO As Object
Dim Folder As Object
Dim File As Object
Set FSO = CreateObject("Scripting.FileSystemObject")
Set Folder = FSO.GetFolder("c:\") ' Change to suit
For Each File In Folder.Files
If File.Type = "Microsoft Excel Worksheet" Then
Count = Count + 1
End If
Next
MsgBox Count
End Sub

Mike

 

[Farhad]

Thanks Mike! i made a little cahnge in your code and it worked but i still
need to know about my secod question which is that i want to know how can i
activate the last tab of each file that i open in my code the tabs names are
different and i don't know the name of tabs

Thanks again for your hehp!

 

[Mike H]

Hi,

The way I prefer is to use code to open the workbooks then call code to do
whatever you want in each workbook you open.

Sub OpenFiles()
Dim FSO As Object
Dim Folder As Object
Dim File As Object
Set FSO = CreateObject("Scripting.FileSystemObject")
Set Folder = FSO.GetFolder("c:\") ' Change to suit
For Each File In Folder.Files
If File.Type = "Microsoft Excel Worksheet" Then
Workbooks.Open Filename:=Folder.Path & "\" & File.Name

'Call your macro
dothings
ActiveWorkbook.Close True
End If
Next
End Sub


Sub dothings()
MsgBox ActiveWorkbook.Name
End Sub

Mike

 

[Farhad]

Thanks again Mike for the second respond but i don't want to know the sheets
name i just want the last tab of each file been activated so i can get the
information that i need in this specific tab of each file. sorry i am very
new in VBA. please let me know if you can help me on this.

Regards,

 

[Mike H]

Hi,

That was just a line of code to demonstrate the workbook was open. To active
the last sheet use this line. You can then go on to do whatever you want.


Sub dothings()
Sheets(Worksheets.Count).Activate
End Sub

Mike