Solved

please help me debug !

Posted on 2002-07-17
27
205 Views
Last Modified: 2010-05-02
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

0
Comment
Question by:tewe
  • 15
  • 12
27 Comments
 
LVL 19

Expert Comment

by:Rimvis
ID: 7161427
I don't see, where you are assigning some value to datTarget.
So it is Nothing.
0
 

Author Comment

by:tewe
ID: 7161473
could you please tell me ,if i want to make it to work.
how could i do!
please!
0
 

Author Comment

by:tewe
ID: 7161487
could you please tell me ,if i want to make it to work.
how could i do!
please!
0
 
LVL 19

Expert Comment

by:Rimvis
ID: 7161494
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.
0
 

Author Comment

by:tewe
ID: 7161524
could you please tell me ,if i want to make it to work.
how could i do!
please!
0
 
LVL 19

Expert Comment

by:Rimvis
ID: 7161531
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.


0
 

Author Comment

by:tewe
ID: 7161536
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!
0
 
LVL 19

Expert Comment

by:Rimvis
ID: 7161539
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.
0
 

Author Comment

by:tewe
ID: 7161548
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!
0
 

Author Comment

by:tewe
ID: 7161566
i don't know how to catch the excel file to compare.
do you understand what i mean?
0
 
LVL 19

Expert Comment

by:Rimvis
ID: 7161575
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?
0
 

Author Comment

by:tewe
ID: 7161585
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!
0
 
LVL 19

Expert Comment

by:Rimvis
ID: 7161709
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
0
Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 

Author Comment

by:tewe
ID: 7161915
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!
0
 
LVL 19

Expert Comment

by:Rimvis
ID: 7161925
You mean "good day"? :o) It's 14:23 here in Lithuania, hehe :o)
0
 

Author Comment

by:tewe
ID: 7164086
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?

0
 

Author Comment

by:tewe
ID: 7164088
i could send my setup program to you!
if you want
0
 
LVL 19

Expert Comment

by:Rimvis
ID: 7164107
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.
0
 

Author Comment

by:tewe
ID: 7164125
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!
0
 
LVL 19

Expert Comment

by:Rimvis
ID: 7164138
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
0
 

Author Comment

by:tewe
ID: 7164165
ok! i send my souce to you!
please compile and run setup program!
it is a small program!
0
 
LVL 19

Accepted Solution

by:
Rimvis earned 400 total points
ID: 7164171
:o/ I have received only these files:
Project1.vbw (50b), Project1.vbp (1k), Project1.PDM (6k)

Where are forms and other files?

BTW, does your COMPILED program hangs (not after setup, but not in debug mode)?

0
 

Author Comment

by:tewe
ID: 7164181
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
0
 

Author Comment

by:tewe
ID: 7164187
the program hang after setup ,not compile
0
 

Author Comment

by:tewe
ID: 7164213
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?
0
 
LVL 19

Expert Comment

by:Rimvis
ID: 7164214
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/
0
 
LVL 19

Expert Comment

by:Rimvis
ID: 7164225
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)
0

Featured Post

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

There are many ways to remove duplicate entries in an SQL or Access database. Most make you temporarily insert an ID field, make a temp table and copy data back and forth, and/or are slow. Here is an easy way in VB6 using ADO to remove duplicate row…
I was working on a PowerPoint add-in the other day and a client asked me "can you implement a feature which processes a chart when it's pasted into a slide from another deck?". It got me wondering how to hook into built-in ribbon events in Office.
Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…

743 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now