How to browse folder and open xls file automatically in vba?

[geniusideas]

Hi,
My Task now is to create excel macro that can:
1) User browse folder
2) Click folder--open then
3) Macro will automatically open all Excel file one by one
4) After open --- collect certain data
5) Close file

Anyone pls help for the VBA code.
Thanks

 

[Bob Phillips]

Sub Main()
Dim fd As FileDialog
Dim SelectedItem As Variant

Set fd = Application.FileDialog(msoFileDialogFilePicker)
With fd

.AllowMultiSelect = True
If .Show = -1 Then

For Each SelectedItem In .SelectedItems

Workbooks.Open (SelectedItem)
'do something with activeworkbook
Next SelectedItem
End If
End With

Set fd = Nothing

End Sub

 

[Ryan H]

Hey Bob, just out of curiosity. I have been programming with VBA for 2-3
years now and I see some people setting there object variables to Nothing at
the end of the code. What is the purpose of this? Doesn't Excel do that for
you once the procedure is done in most cases?

 

[geniusideas]

Sorry Bob,

I can browse the folder but unable to open file inside
automatically..Still need to select.Pls help again.Thanks

 

[Ryan H]

Bobs code works fine. You select the workbooks you want to open in the
folder you browse to and his code opens them.

Are you wanting to just select a folder and have the code automatically open
all Excel files in that folder? Try to be more specific on your needs.

 

[geniusideas]

Yes, that exactly that I want..Just select folder then all file open
automatically

 

[Bob Phillips]

My code does just that, as it is commented, you need to do something with
the opened workbook