load money type columns into Excel

Collapse
This topic is closed.
X
X
Collapse
 
  • Page of 1
  • Time
  • Show
Clear All
new posts
Previous template Next
  • =?Utf-8?B?dHRocm9uZQ==?=
    #1

    load money type columns into Excel

    Hi,
    I am querying sql server and loading the resulting data into Excel.
    For character and integer data types, it's working well, but when I pull
    decimal data and attempt to load it into Excel money format it fails. Below
    is a sample of the code I'm running...

    Dim strRangeString As String
    Dim aRange As Excel.Range
    Dim decArray As Array
    dim i as Integer

    decArray = Array.CreateIns tance(GetType(D ecimal), 600, 6)

    'data was loaded into the decarray using a sql procedure call and data reader

    i = 599 ' max number of rows returned from the prodecure call

    strRangeString = "i2:n" & i + 1
    aRange = XL.Sheets("Aver age Loss Data").Range(st rRangeString)
    aRange.Select()

    'this is the line that fails
    aRange.Value = decArray

    'the error returned only states "The Parameter is Incorrect."

    This code works for integer and character(strin g) data types. It is only
    decimal that fails. The Excel file that I am attempting to load is
    preformatted as a Currency type for the columns being loaded. I've also
    tried changing the Excel pre-format to numeric with 2 decimal places, but I
    got the same error.




    Tags: None
    • =?Utf-8?B?dHRocm9uZQ==?=
      #2
      RE: load money type columns into Excel

      Sorry, I forgot to add that I'm using SQL Server 2005 for the database and
      the incoming data is formatted as money. I tried to format the sql sat as
      decimal(18,2) too, but with the same results.

      Also, I'm using VB.net - VS version 7.1.3088
      MS .Net Framework 1.1 version 1.1.4322

      "tthrone" wrote:
      Hi,
      I am querying sql server and loading the resulting data into Excel.
      For character and integer data types, it's working well, but when I pull
      decimal data and attempt to load it into Excel money format it fails. Below
      is a sample of the code I'm running...
      >
      Dim strRangeString As String
      Dim aRange As Excel.Range
      Dim decArray As Array
      dim i as Integer
      >
      decArray = Array.CreateIns tance(GetType(D ecimal), 600, 6)
      >
      'data was loaded into the decarray using a sql procedure call and data reader
      >
      i = 599 ' max number of rows returned from the prodecure call
      >
      strRangeString = "i2:n" & i + 1
      aRange = XL.Sheets("Aver age Loss Data").Range(st rRangeString)
      aRange.Select()
      >
      'this is the line that fails
      aRange.Value = decArray
      >
      'the error returned only states "The Parameter is Incorrect."
      >
      This code works for integer and character(strin g) data types. It is only
      decimal that fails. The Excel file that I am attempting to load is
      preformatted as a Currency type for the columns being loaded. I've also
      tried changing the Excel pre-format to numeric with 2 decimal places, but I
      got the same error.
      >
      >
      >
      >

        Comment

        Previous template Next
        Working...