By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
459,725 Members | 1,209 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 459,725 IT Pros & Developers. It's quick & easy.

errors in query numerical data transfer to excel

P: 8
halo..

i have this problem..
i have a query that have been derived from many tables and query.. and i want to transfer some of the value in the query to a specific cells in the microsoft excell..

the example of the query is as stated below

City ! Year ! Month ! Sales ! Revenue !
-------------------------------------------------------------------------
Bandung 2005 05 3851 987
Jakarta 2005 05 7894 245
etc.

but the value that being stated at the predefined excel reports some of the sales and the revenue value is not precise as the source query (example the 3851 data become 3811 in the excel report).. some of them were right.. why could this be happened...

how can i get this right? i will be waiting for kind helps.
Sep 22 '06 #1
Share this Question
Share on Google+
4 Replies


PEB
Expert 100+
P: 1,418
PEB
Hi,
In fact the queries in Access are dynamic and getted from the source information, until the data in Excel is static...

And now imagine: When U have copied your data, the value was 11 but in your service s'one have introduced a sale and the result changed...

In a minute your query is refreshed and the value is 51!

This is possibility!

Also it can arrive that when you copy a calculation field from Access to Excel, there is a problem...

A calculation field is this in which there is a function inside...

:)
Sep 22 '06 #2

P: 8
Actually i am now working on the static prototipe data base which doesnt update..

the example query is

City Year Month Sales Revenue
A 2006 03 7789 987
B 2006 03 9098 1021
C 2006 03 7564 977

the result in excel after transfer..

City Year Month Sales Revenue
A 2006 03 7321 987
B 2006 03 8754 1020
C 2006 03 8001 977

As we can see here there are some data which are changing..
could you show me the VBA code how to simply select and import a value of a query to be putted on a specific cell in the excel..


Thanks
Sep 22 '06 #3

PEB
Expert 100+
P: 1,418
PEB
Hi,

Strange! You do programatically the transfer between Access and Excel?


this is an exemple from Access VB help it should help you:

CreateObject Function


Creates and returns a reference to an ActiveX object.

Syntax

CreateObject(class,[servername])

The CreateObject function syntax has these parts:

Part Description
class Required; Variant (String). The application name and class of the object to create.
servername Optional; Variant (String). The name of the network server where the object will be created. If servername is an empty string (""), the local machine is used.



The class argument uses the syntax appname.objecttype and has these parts:

Part Description
appname Required; Variant (String). The name of the application providing the object.
objecttype Required; Variant (String). The type or class of object to create.



Remarks

Every application that supports Automation provides at least one type of object. For example, a word processing application may provide an Application object, a Document object, and a Toolbar object.

To create an ActiveX object, assign the object returned by CreateObject to an object variable:

' Declare an object variable to hold the object
' reference. Dim as Object causes late binding.
Dim ExcelSheet As Object
Set ExcelSheet = CreateObject("Excel.Sheet")

This code starts the application creating the object, in this case, a Microsoft Excel spreadsheet. Once an object is created, you reference it in code using the object variable you defined. In the following example, you access properties and methods of the new object using the object variable, ExcelSheet, and other Microsoft Excel objects, including the Application object and the Cells collection.

' Make Excel visible through the Application object.
ExcelSheet.Application.Visible = True
' Place some text in the first cell of the sheet.
ExcelSheet.Application.Cells(1, 1).Value = "This is column A, row 1"
' Save the sheet to C:\test.xls directory.
ExcelSheet.SaveAs "C:\TEST.XLS"
' Close Excel with the Quit method on the Application object.
ExcelSheet.Application.Quit
' Release the object variable.
Set ExcelSheet = Nothing

Declaring an object variable with the As Object clause creates a variable that can contain a reference to any type of object. However, access to the object through that variable is late bound; that is, the binding occurs when your program is run. To create an object variable that results in early binding, that is, binding when the program is compiled, declare the object variable with a specific class ID. For example, you can declare and create the following Microsoft Excel references:

Dim xlApp As Excel.Application
Dim xlBook As Excel.Workbook
Dim xlSheet As Excel.WorkSheet
Set xlApp = CreateObject("Excel.Application")
Set xlBook = xlApp.Workbooks.Add
Set xlSheet = xlBook.Worksheets(1)

The reference through an early-bound variable can give better performance, but can only contain a reference to the class specified in the declaration.

You can pass an object returned by the CreateObject function to a function expecting an object as an argument. For example, the following code creates and passes a reference to a Excel.Application object:

Call MySub (CreateObject("Excel.Application"))

You can create an object on a remote networked computer by passing the name of the computer to the servername argument of CreateObject. That name is the same as the Machine Name portion of a share name: for a share named "\\MyServer\Public," servername is "MyServer."

Note Refer to COM documentation (see Microsoft Developer Network) for additional information on making an application visible on a remote networked computer. You may have to add a registry key for your application.

The following code returns the version number of an instance of Excel running on a remote computer named MyServer:

Dim xlApp As Object
Set xlApp = CreateObject("Excel.Application", "MyServer")
Debug.Print xlApp.Version

If the remote server doesn’t exist or is unavailable, a run-time error occurs.

Note Use CreateObject when there is no current instance of the object. If an instance of the object is already running, a new instance is started, and an object of the specified type is created. To use the current instance, or to start the application and have it load a file, use the GetObject function.

If an object has registered itself as a single-instance object, only one instance of the object is created, no matter how many times CreateObject is executed.
Sep 22 '06 #4

P: 1
you can convert to string value

select convert(varchar(10), sales) as Sales
Feb 22 '08 #5

Post your reply

Sign in to post your reply or Sign up for a free account.