Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Strange macro behavior - result is correct every other time | Excel Programming | |||
Strange behavior with old macro now throws error with "Application | Excel Programming | |||
Excel 2007 (Beta) - strange behavior of macro | Excel Programming | |||
Strange Personal Macro Workbook Behavior | Excel Discussion (Misc queries) | |||
Strange macro behavior! | Excel Programming |