Prev: Creating folder names from column data in excel
Next: Problem with external data and #reference error
From: geniusideas on 21 Dec 2009 06:48
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



From: Bob Phillips on 21 Dec 2009 07:00
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" <geniusideas(a)gmail.com> wrote in message
news:5d1037c2-72ae-4c87-a779-439a1991c205(a)u25g2000prh.googlegroups.com...
> 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
>
>
>


From: Ryan H on 21 Dec 2009 08:39
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" <geniusideas(a)gmail.com> wrote in message
> news:5d1037c2-72ae-4c87-a779-439a1991c205(a)u25g2000prh.googlegroups.com...
> > 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
> >
> >
> >
>
>
> .
>
From: geniusideas on 21 Dec 2009 09:18
Sorry Bob,

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


From: Ryan H on 21 Dec 2009 11:28
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
>
>
> .
>
 |  Next  |  Last
Pages: 1 2
Prev: Creating folder names from column data in excel
Next: Problem with external data and #reference error