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

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" <(E-Mail Removed)> wrote in message
news:5d1037c2-72ae-4c87-a779-(E-Mail Removed)...
> 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
>
>
>

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" <(E-Mail Removed)> wrote in message
> news:5d1037c2-72ae-4c87-a779-(E-Mail Removed)...
> > 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
> >
> >
> >
>
>
> .
>

Sorry Bob,

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

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
>
>
> .
>

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

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


---
HTH

Bob Phillips

"geniusideas" <(E-Mail Removed)> wrote in message
news:9eb404a0-4673-4395-af19-(E-Mail Removed)...
> Yes, that exactly that I want..Just select folder then all file open
> automatically