Strange behavior with Macro - Import from Access

[Marko]

I have a problem with a macro that should import one row of an access query
result
into a specific cell in my excel sheet.
The strange thing is that if there is only one value of one row to import,
then
the value below the designated cell gets moved one column to the right.
If I have to rows in my query result and I have to free cells for the import
and below those cells one value, this value will not be moved to the right.

It is a little bit difficult to describe the problem but I hope it is clear
what I mean.

Bye
Marko

 

[Marko]

Sorry, it is "two free cells" not "to"..

 

[Patrick Molloy]

Its unclear what you mean by a query "row" with one value. Do you mean
there's one record returned and that has just a single field?
it would help if you showed the offending code.

 

[Marko]

Let's say the query result is
Column1 Column2 Column 3
"Costs" "5000" "0"

Now I want to get the value from column2 (row 1) into a specific Excel cell.
The strange behavior is in Excel...

For example: A1 is empty and there is a "2" in A2. So when you run the
following macro you get the "5000" in A1 and the "2" moves to B2 and A2 is
empty.

Sub Testimport()
'
' Testimport Macro
' Macro recorded 14.07.2009 by Marko
'

'
Range("A1").Select
With ActiveSheet.QueryTables.Add(Connection:= _
"ODBC;DSN=MS Access
Database;DBQ=D:\test.mdb;DefaultDir=D:;DriverId=25;FIL=MS
Access;MaxBufferSize=2048;PageTimeout=5;" _
, Destination:=Range("A1"))
.CommandText = Array( _
"SELECT SonstigeKosten.f2" & Chr(13) & "" & Chr(10) & "FROM
`D:\test`.SonstigeKosten SonstigeKosten")
.Name = "Query from MS Access Database"
.FieldNames = False
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.PreserveColumnInfo = True
.Refresh BackgroundQuery:=False
End With
End Sub

 

[Patrick Molloy]

I suspect that Excel is doing two things
(1) insterting a row or rows according to the number of records (or rows if
you will) returned. In this case, there's one record, so one row gets
inserted.
(2) there's only one value, the 5000, to that gets dropped into the first
column, A, and that means in row one, hence A1


this code provides an alternative if you want to try it:

Option Explicit
' under Tools/References
' set a refernce to the Microsoft ActiveX Data Objects 2.7 Library
Sub LoadDataFromAccess()
Dim MyFile As String
Dim con As New ADODB.Connection
Dim rst As New ADODB.Recordset
Dim SQL As String
Dim i As Long

MyFile = "D:\test.mdb"
SQL = "SELECT SonstigeKosten.f2" & Chr(13) & "" & Chr(10) & _
"FROM `D:\test`.SonstigeKosten SonstigeKosten"

con.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & MyFile

rst.Open SQL, con, adOpenStatic

Range("A1").CopyFromRecordset rst

rst.Close
con.Close

Set rst = Nothing
Set con = Nothing

End Sub

 

[Marko]

I have tried the alternative but nothing happens. No data appears in A1 and
also
no error message.

 

[Patrick Molloy]

if you step through it, does the recordset get populated? is the SQL text
correct - ie does it run in an Access query?
the code is from an existing application - i changed only the sql and
database full name

 

[Marko]

I have tested:
SELECT SonstigeKosten.f2
FROM [D:\test].SonstigeKosten AS SonstigeKosten;

and that seems to work. I have tried a couple of things but without any
success.
A1 remains blank :-(

 

[Marko]

Looks like I have found the solution.
I have recorded a macro to import data from an query in Access into Excel.
At the point "Import Data" where I've been asked "Where do you want to put
the data?" I have clicked on "Properties" and there switched the radio button
from "insert cells for new data, delete unused cells" to "Overwrite existing
cells with new data, clear unused cells".
That's all!

@Patrick: Thank you very much for your help!