Pulling Access data into Excel

H

Harry

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 sheet 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 sheet across a row, and then find a way to copy the
contents into the proper sheet 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
 
T

Tom Ogilvy

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

Harry

Tom, is there any easy way with VB to copy those values from the dummy sheet row
(once I've imported them), to the target sheet in a column? I look at the
script you wrote for me to move stuff from k16..K22 ---> k48..k54, in the same
sheet, and I'm wondering how I might modify the script by running a VB script
associated with the dummy sheet, copying into the proper target sheet. 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 sheet other than the current sheet in this
script, and I'm sure there are other ramifications since I'm going from a row in
sheet "dummy", row 30, columns "a to j" and copying to "sheet 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
 
T

Tom Ogilvy

Sub CopyData()
worksheets("Dummy").Range("A30").Resize(1,17)
worksheets("sheet1").Range("K25").PasteSpecial _
paste:=xlPasteAll, Transpose:=True
End sub

columns A to J are 10 columns, A to Q would be 17 columns.

This is peer to peer.
 
H

Harry

Having some trouble with it. I've modified it accordingly as follows:

Sub CopyData()
worksheets("Codes & Constants").Range("A30").Resize(1,17)
Worksheets("Data").Range("K25").PasteSpecial _
Paste:=xlPasteAll, Transpose:=True
End Sub

And I get an error "Compile error - Expected ="

after typing the source line with Codes & Constants. The line is red in the
editor. Whay did I do wrong?

Also, what did you mean by peer to peer, and what's your reference to A to Q, I
don't see that in the code.

Harry
 
T

Tom Ogilvy

Typo - I left out the copy method:

Sub CopyData()
worksheets("Codes & Constants")
.Range("A30").Resize(1,17).copy
Worksheets("Data").Range("K25").PasteSpecial _
Paste:=xlPasteAll, Transpose:=True
End Sub

--
Regards,
Tom Ogilvy


Harry said:
Having some trouble with it. I've modified it accordingly as follows:

Sub CopyData()
worksheets("Codes & Constants").Range("A30").Resize(1,17)
Worksheets("Data").Range("K25").PasteSpecial _
Paste:=xlPasteAll, Transpose:=True
End Sub

And I get an error "Compile error - Expected ="

after typing the source line with Codes & Constants. The line is red in the
editor. Whay did I do wrong?

Also, what did you mean by peer to peer, and what's your reference to A to Q, I
don't see that in the code.

Harry


Sub CopyData()
worksheets("Dummy").Range("A30").Resize(1,17)
worksheets("sheet1").Range("K25").PasteSpecial _
paste:=xlPasteAll, Transpose:=True
End sub

columns A to J are 10 columns, A to Q would be 17 columns.

This is peer to peer.

--
Regards,
Tom Ogilvy



Harry said:
Tom, is there any easy way with VB to copy those values from the dummy sheet row
(once I've imported them), to the target sheet in a column? I look at the
script you wrote for me to move stuff from k16..K22 ---> k48..k54, in
the
same
sheet, and I'm wondering how I might modify the script by running a VB script
associated with the dummy sheet, copying into the proper target sheet. 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 sheet other than the current sheet in this
script, and I'm sure there are other ramifications since I'm going from
a
row in
sheet "dummy", row 30, columns "a to j" and copying to "sheet 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
 
H

Harry

YOu are a gem. I played around a little, looked at some other macros, and now I
managed to link the script to a button to control when it happens, and it works
perfectly. The PasteAll was changing cell widths and stuff, along with range,
so I changed it to:

'HF Created: This takes the Access data I've imported, and copies it to the
correct cells

Sub copy_data()
Range("R25:AH25").Copy
Range("K25").PasteSpecial _
Paste:=xlPasteValues, Transpose:=True
End Sub

and it seems to work like a charm.

Thanks

Harry



Typo - I left out the copy method:

Sub CopyData()
worksheets("Codes & Constants")
.Range("A30").Resize(1,17).copy
Worksheets("Data").Range("K25").PasteSpecial _
Paste:=xlPasteAll, Transpose:=True
End Sub

--
Regards,
Tom Ogilvy


Harry said:
Having some trouble with it. I've modified it accordingly as follows:

Sub CopyData()
worksheets("Codes & Constants").Range("A30").Resize(1,17)
Worksheets("Data").Range("K25").PasteSpecial _
Paste:=xlPasteAll, Transpose:=True
End Sub

And I get an error "Compile error - Expected ="

after typing the source line with Codes & Constants. The line is red in the
editor. Whay did I do wrong?

Also, what did you mean by peer to peer, and what's your reference to A to Q, I
don't see that in the code.

Harry
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top