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

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

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

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


---
HTH

Bob Phillips

"geniusideas" wrote in message
...
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

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

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?
--
Cheers,
Ryan


"Bob Phillips" wrote:

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


---
HTH

Bob Phillips

"geniusideas" wrote in message
...
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





.

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

Sorry Bob,

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

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

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.


--
Cheers,
Ryan


"geniusideas" wrote:

Sorry Bob,

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


.

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

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

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

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


---
HTH

Bob Phillips

"geniusideas" wrote in message
...
Yes, that exactly that I want..Just select folder then all file open
automatically