Link to home
Start Free TrialLog in
Avatar of tewe
tewe

asked on

please help me debug !

i write a program which get data from VB UI and save those in excel file!
like this.
i just declare the datTarget as data
but i find the datTarget always show "NOTHING"
I don't know what's happend.please help me in the hurry.
because WE will use this program to begin a project .so please kindly help me!

  On Error Resume Next
    '®aÉúExcel report in one sheet

    Dim objExcelApp As Excel.Application
    Dim objSheet As Excel.Worksheet
    Dim rstTarget As Recordset
    Dim intTable As Integer, intStartField As Integer
    Dim I As Integer, J As Integer, intNum As Integer
    Dim intCurrentRow As Integer

    Dim QuanIdx As Integer
    Dim strEQ_Desc As String, strModel As String
    Dim datTarget As Data
   
    Dim v As Integer
    Dim intInitialRow As Integer
    Dim aFileName As String
    Dim r As Integer
    Dim s As Integer
   
   
    Screen.MousePointer = vbHourglass

    '½¨Á¢Excel µÄApplicationÎï¼þ
    Set objExcelApp = CreateObject("Excel.Application")
    objExcelApp.ActiveWorkbook.Open fileInput.Text
   
    objExcelApp.Workbooks.Add
    objExcelApp.SheetsInNewWorkbook = 1
    'ë[²Ø‘ªÓóÌʽ
    objExcelApp.Visible = True

    '¼ÓÈëÒ»‚€¹¤×÷±í
    objExcelApp.ActiveWorkbook.Worksheets.Add
    Set objSheet = objExcelApp.ActiveWorkbook.ActiveSheet
   
    intInitialRow = 0
    intCurrentRow = intInitialRow   'ÔO¶¨ÆðʼÁÐ
   
    Set rstTarget = datTarget.Recordset.Clone 'Ñ}ÑuÒ»·ÝRecordsetµÄÑ}±¾
    'objSheet.Name = "ÁÎÄÏÔŠÀÏŽŸŒòž"
             
             objSheet.Cells(1, 1) = "MICE_ID"
             objSheet.Cells(1, 2) = "STRAIN"
             objSheet.Cells(1, 3) = "DOB"
             objSheet.Cells(1, 4) = "CROSS"
             objSheet.Cells(1, 5) = "SEX"
             objSheet.Cells(1, 6) = "FATHER_ID"
             objSheet.Cells(1, 7) = "MOTHER_ID"
             objSheet.Cells(1, 8) = "EXP_DATE"
             objSheet.Cells(1, 9) = "GATED"
             objSheet.Cells(1, 10) = "CD4+(%)"
             objSheet.Cells(1, 11) = "®³£"
             objSheet.Cells(1, 12) = "CD8+(%)"
             objSheet.Cells(1, 13) = "®³£"
             objSheet.Cells(1, 14) = "H57+(%)"
             objSheet.Cells(1, 15) = "®³£"
             objSheet.Cells(1, 16) = "CD8+CD44 HI(%)"
             objSheet.Cells(1, 17) = "®³£"
             objSheet.Cells(1, 18) = "CD8+CD44 LO(%)"
             objSheet.Cells(1, 19) = "®³£"
             objSheet.Cells(1, 20) = "CD4+CD44 HI(%)"
             objSheet.Cells(1, 21) = "®³£"
             objSheet.Cells(1, 22) = "CD4+CD44 LO(%)"
             objSheet.Cells(1, 23) = "®³£"
             objSheet.Cells(1, 24) = "DX5+(%)"
             objSheet.Cells(1, 25) = "®³£"
             objSheet.Cells(1, 26) = "H57+DX5+(%)"
             objSheet.Cells(1, 27) = "®³£"
             objSheet.Cells(1, 28) = "TIME OF WRITE DOWN"
             
              If rstTarget.RecordCount > 0 Then
             
                         
                    For J = 0 To rstTarget.Fields.Count - 1
                       objSheet.Cells(intCurrentRow + 1, J + 1).Value = rstTarget.Fields(J).Name
                       r = r + 1
                    Next J
             
         'ÕÒ³ö¿‚ӛ䛔µ
                    rstTarget.MoveLast
                    intNum = rstTarget.RecordCount
                    rstTarget.MoveFirst
         'ÌîÈëÙYÁÏ
                    I = intCurrentRow + 2
                    'v = I + 1
              'End If
             
             
             s = rstTarget.Fields.Count
             v = r + 1
             objSheet.Cells(v, 1) = MICEID.Text
             objSheet.Cells(v, 2) = STRAIN.Text
             objSheet.Cells(v, 3) = DOB.Text
             objSheet.Cells(v, 4) = CROSS.Text
             objSheet.Cells(v, 5) = SEX.Text
             objSheet.Cells(v, 6) = FATHERID.Text
             objSheet.Cells(v, 7) = MOTHERID.Text
             objSheet.Cells(v, 8) = EXPDATE.Text
             objSheet.Cells(v, 9) = GATED.Text
             objSheet.Cells(v, 10) = CD4.Text
                 If CD4.Text > 30 Or CD4.Text < 10 Then
                    objSheet.Cells(v, 11) = "CD4+®³£"
                 End If
             objSheet.Cells(v, 12) = CD8.Text
                  If CD8.Text > 30 Or CD8.Text < 5 Then
                    objSheet.Cells(v, 13) = "CD8+®³£"
                  End If
             objSheet.Cells(v, 14) = H57.Text
                  If H57.Text > 40 Or H57.Text < 20 Then
                    objSheet.Cells(v, 15) = "H57+®³£"
                  End If
             objSheet.Cells(v, 16) = CD8CD44HI.Text
                  If CD8CD44HI.Text > 20 Then
                    objSheet.Cells(v, 17) = "CD8+CD44 HI®³£"
                  End If
             objSheet.Cells(v, 18) = CD8CD44LO.Text
                  If CD8CD44LO.Text < 80 Then
                    objSheet.Cells(v, 19) = "CD8+CD44 LO®³£"
                  End If
             objSheet.Cells(v, 20) = CD4CD44HI.Text
                  If CD4CD44HI.Text > 20 Then
                    objSheet.Cells(v, 21) = "CD4+CD44 HI®³£"
                  End If
             objSheet.Cells(v, 22) = CD4CD44LO.Text
                  If CD4CD44LO.Text < 80 Then
                    objSheet.Cells(v, 23) = "CD4+CD44 LO®³£"
                  End If
             objSheet.Cells(v, 24) = DX5.Text
                  If DX5.Text > 25 Then
                    objSheet.Cells(v, 25) = "DX5+®³£"
                  End If
             objSheet.Cells(v, 26) = H57DX5.Text
                  If H57DX5.Text > 10 Then
                    objSheet.Cells(v, 27) = "®³£"
                  End If
             objSheet.Cells(v, 28) = Now
             
    'Õ{Õû¸ñʽ
        objSheet.Range(objSheet.Cells(intCurrentRow + 1, 1), objSheet.Cells(intCurrentRow + 1, rstTarget.Fields.Count)).Select
        objExcelApp.Selection.EntireColumn.AutoFit
        objExcelApp.Selection.Interior.ColorIndex = 24
        objSheet.Range(objSheet.Cells(intCurrentRow + 1, 1), objSheet.Cells(I - 1, rstTarget.Fields.Count)).Select
            With objExcelApp.Selection
                .Borders(7).Weight = 4
                .Borders(7).ColorIndex = -4105
                .Borders(8).Weight = 4
                .Borders(8).ColorIndex = -4105
                .Borders(9).Weight = 4
                .Borders(9).ColorIndex = -4105
                .Borders(10).Weight = 4
                .Borders(10).ColorIndex = -4105
                .Borders(11).Weight = 2
                .Borders(11).ColorIndex = -4105
                ' ®‹¾€
                If (I - 1) - (intCurrentRow + 1) > 1 Then
                    .Borders(12).Weight = 2
                    .Borders(12).ColorIndex = -4105
                End If
             End With
            With objSheet.PageSetup
                .PrintTitleRows = "$" & intCurrentRow & ":$" & intCurrentRow + 1
                .Orientation = 2
                .Zoom = False
                .FitToPagesWide = 1
                .FitToPagesTall = 10
            End With
    rstTarget.Close
    Set rstTarget = Nothing
    End If
   
     aFileName = Text1.Text
     
                     
                    'ƒ¦´æ¹¤×÷±í¡£
                      'If Dir(aFileName) <> "" Then Kill aFileName
                      objExcelApp.ActiveWorkbook.SaveAs aFileName
     
                   
     
                    ' êPé]»î퓲¾
                     objExcelApp.ActiveWorkbook.Close
   
                    'Óà Quit ·½·¨êPé] Microsoft Excel¡£
   
                     objExcelApp.Quit
   
                    'Ýd³öÎï¼þ¡£
                     Set objExcelApp = Nothing
                     Close #1
                     Screen.MousePointer = 0
                     MsgBox ("ÞD™nÍê³É")
   
    Screen.MousePointer = vbDefault

Avatar of Rimvis
Rimvis
Flag of Lithuania image

I don't see, where you are assigning some value to datTarget.
So it is Nothing.
Avatar of tewe
tewe

ASKER

could you please tell me ,if i want to make it to work.
how could i do!
please!
Avatar of tewe

ASKER

could you please tell me ,if i want to make it to work.
how could i do!
please!
First off all, I don't know, why you are using Data and Recordset objects at all. Maybe I don't understand something?
Your goal is to take values from input fields and put it into Excel file, right? If that's all, I could try to fix existing code for you, if you want to.
Avatar of tewe

ASKER

could you please tell me ,if i want to make it to work.
how could i do!
please!
If you trying to write data from Data control recordset, here's what you should do:
1) Remove definition "Dim datTarget As Data" from your procedure. Here you are creating some copy of Data control, that is not connected to control in your form in any way.
Maybe you HAVE control named "datTarget" on your form? Than skip next step.
2)Correct line "Set rstTarget = datTarget.Recordset.Clone"
You must clone recordset from Data control in your form.
3)Correct lines like "objSheet.Cells(v, 1) = MICEID.Text"
Values should be taken from Recordset fields, not input textboxes. So if you have field in recordset named "MICEID", line should look like
"objSheet.Cells(v, 1) = rstTarget!MICEID" or "objSheet.Cells(v, 1) = rstTarget.Fields("MICEID")"

That should be enough.


Avatar of tewe

ASKER

thanks!
my goal is i hope when i input data to excel.
I could move the point to the last row,
and add data from last row+1.
thanks very much!
One more thing :o)
Do not use browser Refresh function to reload this page. You are posting your last comment that way. There's "Relaod Question" link in upper right corner.
Avatar of tewe

ASKER

in this program ,I THINK I must create a clone to compare with the the old excel file,one is movefirst another is movelast.if movefirst = movelast.
i will know how much rows in excel file.
then i could +1 to v,and apeend data to excel file.
if you could understand,please help me .
because ,we will use this program to run a project.
and tomorrow os deadline!
so please.
thanks for your kndly help!
Avatar of tewe

ASKER

i don't know how to catch the excel file to compare.
do you understand what i mean?
Correct me if I'm wrong:

1) You have Data control on your form, which is linked to Excel file (lets call it A.xls).

2) When you are entering data to textboxes in your form, you want to save file A.xls contents and new entered data into new file B.xls. Or do you want to append data to same file A.xls?

Is that right?
Avatar of tewe

ASKER

no ,i don't use any data control
my form just include txtbox and commandbutton!

i hope both of them!
when i entry data,sometimes i will create a a.xls file,
but sometimes i will append data to the same file!
Have a look at this.
This sample appends data to existing Excel file, or creates new if it doesn't exists.
I hope you can reuse this in your project.

Option Explicit
Private Const consFileName As String = "C:\Temp\A.xls"

Private Sub cmdGo_Click()
    Dim objExcelApp As Excel.Application
    Dim objSheet As Excel.Worksheet
    Dim iRowsTotal As Integer
    Dim i As Integer
   
    'call Excel applications
    Set objExcelApp = CreateObject("Excel.Application")
   
    'check if file exists
    If Dir$(consFileName) = "" Then
        'create new workbook
        objExcelApp.Workbooks.Add
        objExcelApp.SheetsInNewWorkbook = 1
        objExcelApp.Visible = True
        objExcelApp.ActiveWorkbook.Worksheets.Add
    Else
        'open existing
        objExcelApp.Workbooks.Open consFileName
    End If
   
    'activate sheet
    Set objSheet = objExcelApp.ActiveWorkbook.ActiveSheet
   
    'find last empty row
    'I'm asuming empty row means end of data
    i = 1
    Do While objSheet.Cells(i, 1) <> ""
        i = i + 1
    Loop
    iRowsTotal = i
   
    'write data after existing records
    objSheet.Cells(iRowsTotal, 1) = txtSomeData.Text
   
    'save workbook
    objExcelApp.ActiveWorkbook.Save consFileName
     
    'close and exit
    objExcelApp.ActiveWorkbook.Close
    objExcelApp.Quit
    Set objExcelApp = Nothing
   
End Sub
Avatar of tewe

ASKER

thanks!i will try to use your solution in my project tomorrow.
because i have leave my office.
thanks for your help!
having a good night!
You mean "good day"? :o) It's 14:23 here in Lithuania, hehe :o)
Avatar of tewe

ASKER

good morning!
according to your suggestion, i have new logic thinking.
and solve my problem!

but i have another problem,
when i build the setup program,in this process everything is ok!
but after setup process,i run my program .
it always hang.
i don't know what's going on?
could you please tell me?

Avatar of tewe

ASKER

i could send my setup program to you!
if you want
How does your program "hang"? Just freezes? Or there's some error messages? Does target computer have Excel installed?

What tool did you use to create setup? That one from Visual Studio (Package and deployment <something>)? I think "setup.lst" file would be enough for start.
Avatar of tewe

ASKER

yes freezes!
and i have installed the excel program!
i use the vb setup program!

my situation which is when i run my program!
it could show the first row only.
but it couldn't show others.

i have check my program,it is ok in the vb compile mode.
but when i do completely the setup.it appear those siyuation!

do you know why?
thanks you very much again!
Hm, so you are trying to run setup on the same machine you are  developing?
How bizzare, how bizzare :o/
OK, send me your setup files, I'll look at them. How big are they? I hope not too much :o)
Mail: kohagor@hotmail.com
Avatar of tewe

ASKER

ok! i send my souce to you!
please compile and run setup program!
it is a small program!
ASKER CERTIFIED SOLUTION
Avatar of Rimvis
Rimvis
Flag of Lithuania image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of tewe

ASKER

i already send the form to you!
not include the setup file.
because i think the setup which i run is wrong.
so please run setup again!
thanks
Avatar of tewe

ASKER

the program hang after setup ,not compile
Avatar of tewe

ASKER

Dear rimvis:
    i just solve the problem,i think the problem is the setup program error.when i reinstall the program is ok!
thanks for your help this time.
i seriously hope ,next time when i have another problem you could help me once again!
having a nice weekend!
    where are you come from?
I can't open your Form1.frm for some reason. When I double-click on it, VB tryes to open it as module, istead of form :o/

Anyway, I've looked in your form using Notepad (ow, that hurts :o) ). And I don't see where are you preserving old data in Excel file. I think your code just overwrites it.

So maybe you program does not hang at all? It just does what it is told :o/
I'm glad I could help :o)

Anyway, as I said, your code does not save previous data in Excel file. Are you sure that is what you want?

BTW, I have opened your form with VB. I was some comment in chinese (I think), thar caused a problem.

I'm from Lithuania, I allready told you :o)