Re: Pulling Access data into Excel

From: Harry (harry_at_finefam.ca)
Date: 02/16/05 

Date: Wed, 16 Feb 2005 00:11:08 GMT

Tom, is there any easy way with VB to copy those values from the dummy *** row
(once I've imported them), to the target *** in a column? I look at the
script you wrote for me to move stuff from k16..K22 ---> k48..k54, in the same
***, and I'm wondering how I might modify the script by running a VB script
associated with the dummy ***, copying into the proper target ***. The old
code, in case you don't have it there was as follows after some modiciation:

'HF CHANGED : This takes some of the unit info, and copies it to respondent info

'Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Columns.Count > 1 Then Exit Sub
If Target.Column <> 11 Then Exit Sub
If Target(1).Row >= 16 And Target(1).Row <= 22 Then
  Application.EnableEvents = False
   Me.Range("K48:K52").Value = _
   Me.Range("K16:K22").Value
    Me.Range("K48").Value = Me.Range("K16").Value _
    & " " & Me.Range("K17").Value & " " & Me.Range("K18").Value & " " &
Me.Range("K19").Value
    Me.Range("K49").Value = Me.Range("K20").Value
    Me.Range("K50").Value = Me.Range("K21").Value
    Me.Range("K51").Value = "ON"
    Me.Range("K52").Value = Me.Range("K22").Value
  Application.EnableEvents = True
End If
End Sub

I don't know how to reference a *** other than the current *** in this
script, and I'm sure there are other ramifications since I'm going from a row in
*** "dummy", row 30, columns "a to j" and copying to "*** 1", column k
fields "25 to 41". At least in this case, the fields across match the fields
down exactly, so no concatenating of fields required. There are 17 fields in
all.

By the way, I don't know if you do this for a living and do PayPal billing, but
if a fee is appropriate, I'm more than willing.

Harry

On Tue, 15 Feb 2005 18:04:31 -0500, "Tom Ogilvy" <twogilvy@msn.com> wrote:

>You should only have to do it once. Then just refresh.
>
>It doesn't do what you want because the people who wrote the code didn't
>provide that functionality.
>
>--
>Regards,
>Tom Ogilvy
>
>"Harry" <harry@finefam.ca> wrote in message
>news:42126888.21278109@nntp.broadband.rogers.com...
>> Hello
>>
>> Two quick questions please
>>
>> I'm doing a database query from Excel 2003 in Access. I go:
>>
>> Data/Import External Data/New Database Query/
>>
>> and then I progess through the wizard, selecting my Access file, selecting
>the
>> matching criteria and then selecting the cell in the Excel *** to import
>to.
>>
>> It grabs the right records from Access, but it imports the data from the
>Access
>> record and displays it across a row, and I'd like to go down a column,
>cause
>> that's where the proper target fields are for the fields from Access.
>>
>> Why does it seem I can only import data across a row using MSQUERY? I'm
>having
>> to put those into a dummy *** across a row, and then find a way to copy
>the
>> contents into the proper *** in the proper cells.
>>
>> Second question, is there an easier way. It is awkward to walk through
>the
>> keystokes to do the MSQUERY, selecting the options etc. each time.
>>
>> Thanks
>>
>> Harry
>>
>
>