Copy data from a closed workbook (ADO)

I am using code provided at Ron's web site and with his help (Thanks Ron!)
and help form this forum I got the code to work except I am not getting the
data I expect.

On the Saturday sheet, cell b2 contains a date and cell b3 has city/state/zip

Sub GetData_Example2()
Dim SaveDriveDir As String, MyPath As String
Dim FName As Variant

SaveDriveDir = CurDir
MyPath = Application.DefaultFilePath 'or use "C:\Data"
ChDrive MyPath
ChDir MyPath
FName = Application.GetOpenFilename(filefilter:="Excel Files, *.xls")

If FName = False Then
'do nothing
Else

****When I run either****

GetData FName, "Saturday", "b2:b3", Sheets("Source").Range("b2"), False
Or
GetData FName, "Saturday", "b2:b3", Sheets("Source").Range("b2:b3"), False


I get the city/state/zip in b2 (no date)

When I run

GetData FName, "Saturday", "b1:b3", Sheets("Source").Range("b2:b3"), False

I get the date in b2 (no city/state/zip)

I need to be able to select the source file since the filename will not be
known to hard code it. Any help is greatly appreciated!

Christy

Hi

Look at the last argument (False /True)
Header yes or No

--
Regards Ron de Bruin
http://www.rondebruin.nl



"Christy" <[email protected]> wrote in message news:[email protected]...
>I am using code provided at Ron's web site and with his help (Thanks Ron!)
> and help form this forum I got the code to work except I am not getting the
> data I expect.
>
> On the Saturday sheet, cell b2 contains a date and cell b3 has city/state/zip
>
> Sub GetData_Example2()
> Dim SaveDriveDir As String, MyPath As String
> Dim FName As Variant
>
> SaveDriveDir = CurDir
> MyPath = Application.DefaultFilePath 'or use "C:\Data"
> ChDrive MyPath
> ChDir MyPath
> FName = Application.GetOpenFilename(filefilter:="Excel Files, *.xls")
>
> If FName = False Then
> 'do nothing
> Else
>
> ****When I run either****
>
> GetData FName, "Saturday", "b2:b3", Sheets("Source").Range("b2"), False
> Or
> GetData FName, "Saturday", "b2:b3", Sheets("Source").Range("b2:b3"), False
>
>
> I get the city/state/zip in b2 (no date)
>
> When I run
>
> GetData FName, "Saturday", "b1:b3", Sheets("Source").Range("b2:b3"), False
>
> I get the date in b2 (no city/state/zip)
>
> I need to be able to select the source file since the filename will not be
> known to hard code it. Any help is greatly appreciated!
>
> Christy
>

Thanks Ron. I figured it had something to do with headers.

I was able to get the code to work by copying the data one cell at a time. I
had to include an extra cell above the one I wanted in the source range. I
guess the true/false indicates if you want to copy a header row which must be
included in the range either way?

Anyway, it works great now. It does run a little slow and I will be
checking out your "Create a summary sheet from different workbooks" code next.

Thanks again
Christy

"Ron de Bruin" wrote:

> Hi
>
> Look at the last argument (False /True)
> Header yes or No
>
> --
> Regards Ron de Bruin
> http://www.rondebruin.nl
>
>
>
> "Christy" <[email protected]> wrote in message news:[email protected]...
> >I am using code provided at Ron's web site and with his help (Thanks Ron!)
> > and help form this forum I got the code to work except I am not getting the
> > data I expect.
> >
> > On the Saturday sheet, cell b2 contains a date and cell b3 has city/state/zip
> >
> > Sub GetData_Example2()
> > Dim SaveDriveDir As String, MyPath As String
> > Dim FName As Variant
> >
> > SaveDriveDir = CurDir
> > MyPath = Application.DefaultFilePath 'or use "C:\Data"
> > ChDrive MyPath
> > ChDir MyPath
> > FName = Application.GetOpenFilename(filefilter:="Excel Files, *.xls")
> >
> > If FName = False Then
> > 'do nothing
> > Else
> >
> > ****When I run either****
> >
> > GetData FName, "Saturday", "b2:b3", Sheets("Source").Range("b2"), False
> > Or
> > GetData FName, "Saturday", "b2:b3", Sheets("Source").Range("b2:b3"), False
> >
> >
> > I get the city/state/zip in b2 (no date)
> >
> > When I run
> >
> > GetData FName, "Saturday", "b1:b3", Sheets("Source").Range("b2:b3"), False
> >
> > I get the date in b2 (no city/state/zip)
> >
> > I need to be able to select the source file since the filename will not be
> > known to hard code it. Any help is greatly appreciated!
> >
> > Christy
> >
>
>
>