Strange behavior with Macro - Import from Access
 

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

Strange behavior with Macro - Import from Access
 

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

"Marko" wrote:

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

Strange behavior with Macro - Import from Access
 

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

Strange behavior with Macro - Import from Access
 

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

Strange behavior with Macro - Import from Access
 

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

Strange behavior with Macro - Import from Access
 

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

"Patrick Molloy" wrote:

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

Strange behavior with Macro - Import from Access
 

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" wrote in message
...
I have tried the alternative but nothing happens. No data appears in A1
and
also
no error message.

"Patrick Molloy" wrote:

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

Strange behavior with Macro - Import from Access
 

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 :-(

"Patrick Molloy" wrote:

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" wrote in message
...
I have tried the alternative but nothing happens. No data appears in A1
and
also
no error message.

"Patrick Molloy" wrote:

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

Strange behavior with Macro - Import from Access
 

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!

"Marko" wrote:

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