Opening a user selected workbook

Hi,

I have a large workbook that I use to analyse data that is put into several
sheets.
Rather than save the large workbook (which is several MB) I would like to
run a macro to copy and paste the input information into a new Data only
workbook. I will end up with many “data� workbooks but at least these are
less than a hundredth of the size. These Data only files would also permit
other people to update the data even though they do not have access to the
large workbook.
Then when that data is required it can be copied back into the large workbook.

I have the macros to do all the copy and pasting but do not know how to
prompt the user for naming the file in the first place or recalling the
desired file when the data is to be “imported�.

My code at the moment refers - several times throughout its execution - to a
workbook of a particular name; in practice it needs to refer to the
“selected� workbook.

Any ideas? Should I approach this in a different way?

Kind regards
David Hall

Look in Excel VBA help at the GetOpenFileName and GetSaveAsFileName methods
of the application object.

--
Regards,
Tom Ogilvy

"David Hall" <David Hall@discussions.microsoft.com> wrote in message
news:CB09F7E4-0E6A-4219-B184-368F7DC04137@microsoft.com...
> Hi,
>
> I have a large workbook that I use to analyse data that is put into
several
> sheets.
> Rather than save the large workbook (which is several MB) I would like to
> run a macro to copy and paste the input information into a new Data only
> workbook. I will end up with many "data" workbooks but at least these are
> less than a hundredth of the size. These Data only files would also permit
> other people to update the data even though they do not have access to the
> large workbook.
> Then when that data is required it can be copied back into the large
workbook.
>
> I have the macros to do all the copy and pasting but do not know how to
> prompt the user for naming the file in the first place or recalling the
> desired file when the data is to be "imported".
>
> My code at the moment refers - several times throughout its execution - to
a
> workbook of a particular name; in practice it needs to refer to the
> "selected" workbook.
>
> Any ideas? Should I approach this in a different way?
>
> Kind regards
> David Hall

David,

You could use

fName = Inputbox("Supply file name")
if fName <> "" Then
Activeworkbook.SaveAs filename:=fname
end if

but it would be better IMO to use the built-in functionality. Take a look at
GetSaveAsFileName in help. Read it carefully as it is not immediately
straight-forward.

--
HTH

-------

Bob Phillips
"David Hall" <David Hall@discussions.microsoft.com> wrote in message
news:CB09F7E4-0E6A-4219-B184-368F7DC04137@microsoft.com...
> Hi,
>
> I have a large workbook that I use to analyse data that is put into
several
> sheets.
> Rather than save the large workbook (which is several MB) I would like to
> run a macro to copy and paste the input information into a new Data only
> workbook. I will end up with many "data" workbooks but at least these are
> less than a hundredth of the size. These Data only files would also permit
> other people to update the data even though they do not have access to the
> large workbook.
> Then when that data is required it can be copied back into the large
workbook.
>
> I have the macros to do all the copy and pasting but do not know how to
> prompt the user for naming the file in the first place or recalling the
> desired file when the data is to be "imported".
>
> My code at the moment refers - several times throughout its execution - to
a
> workbook of a particular name; in practice it needs to refer to the
> "selected" workbook.
>
> Any ideas? Should I approach this in a different way?
>
> Kind regards
> David Hall