Issue in excel report

[divakar.it]

Hi,

I am currently developing a tool in VB 6.0. I am facing a problem when i generate reports in Excel format.

The issue description:
- When I click the 'Produce report button', the vb code will run a query and fetch the record into record set
- and then it will copy into excel
- when copy paste the record set, all the number columns after any date column is changed into Date format.

Eg: The record set of a TimeSheet table have below fields
Emp_Id | Emp_Name | Date | Totalhrs | Uniq_Num

The values produced in the report is

TG0001 | Divakar | 12-Oct-2010 | 1/3/1900 | 1/1/1900

where as it should display

TG0001 | Divakar | 12-Oct-2010 | 3 | 1

I am using Excel 2007 and not sure where the issue is.

I am sure that when copy paste the record set during run time all the columns (here 'Totalhrs' and 'Uniq_Num') changed into date format after copying the date column (here 'Date')

Can anyone please help me on this?

Regards,
Divakar R

[divakar.it]

Sorry!!! Below is my code part where it will copy the record set into excel

xlTmp.Visible = True
    Set xlwbk = xlTmp.Workbooks.Add
    Set xlSht = xlwbk.Worksheets(1)
    Set xlrng = xlSht.Range("A2:I4001")
    xlSht.Cells(1, 1).Value = "Generating data..."
    xlTmp.ScreenUpdating = False

    
    If MyRecSet2.RecordCount > 0 Then
        Cnt = 1
        For Each fld In MyRecSet2.Fields
            xlSht.Cells(1, Cnt).Value = fld.Name
            Cnt = Cnt + 1
        Next fld

    Call xlrng.CopyFromRecordset(MyRecSet2, 4000, 100)

[AndreRet]

Has your issue been solved now with the code above?

[divakar.it]

Hi Andre, Yes i found alternate solution for this..

I copied each column values rather the whole record set. This has resolved the issue however i am curious on the reason for the issue when i copy the whole record set using the above mentioned code.

Would you help me on this?

[AndreRet]

What code have you used in the first instance?

[divakar.it]

The code which i used intially was

xlTmp.Visible = True
Set xlwbk = xlTmp.Workbooks.Add
Set xlSht = xlwbk.Worksheets(1)
Set xlrng = xlSht.Range("A2:I4001")
xlSht.Cells(1, 1).Value = "Generating data..."
xlTmp.ScreenUpdating = False


If MyRecSet2.RecordCount > 0 Then
Cnt = 1
For Each fld In MyRecSet2.Fields
xlSht.Cells(1, Cnt).Value = fld.Name
Cnt = Cnt + 1
Next fld

Call xlrng.CopyFromRecordset(MyRecSet2, 4000, 100)

The code which i fixed using the below:

colCount = MyRecSet2.Fields.Count
    RowIndex = 2
  
    Do While Not MyRecSet2 Is Nothing And Not MyRecSet2.BOF And Not MyRecSet2.EOF
    For Index = 1 To colCount
          xlTmp.Cells(RowIndex, Index).Value = MyRecSet2(Index - 1).Value
       Next Index
    RowIndex = RowIndex + 1
    MyRecSet2.MoveNext
    Loop

[AndreRet]

I think the problem started with excel reading the date field and then copied the next field which is suppose to be an integer. Unfortunately in excel, it copies column to column, trying to reproduce the previous column, hence the incorrect dates in place of the integers.

The use of a loop seems to have eliminated the problem, rather than just copying the fields over.:)

Please close this thread for us if you do not need any more help on the question.:)