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" wrote in message
...
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" wrote:

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" wrote in message
...
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