Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

open and close excel file from vb6

Posted on 2005-03-10
7
Medium Priority
?
346 Views
Last Modified: 2010-05-02
hi here is my code. i am trying to open an excel file to write something there and once that is done i print it.now the problem appears when i try to print again the same file with new info.what happens it gives me err 1004 and is acting like the previous file had never been closed . pls some help . thk you in advance.
 Dim i As Integer
    Dim j As Integer
    Dim xl0 As New Excel.Application
    Dim fin As Boolean
    Dim filename As Variant
   
   
    filename = "C:\Documents and Settings\zetc\Desktop\nick&alex\A&N\excel file\estimate.xls"
    xl0.Workbooks.Open filename
    With xl0.ActiveSheet
        .Cells(3, 1) = ""
        .Cells(4, 1) = ""
        .Cells(5, 1) = ""
        .Cells(6, 1) = ""
        .Cells(3, 3) = ""
        .Cells(4, 3) = ""
        .Cells(5, 3) = ""
        .Cells(3, 5) = ""
        .Cells(4, 5) = ""
        .Cells(5, 5) = ""
         
         i = 4
         
         
        Do Until i >= 100
           
           
            .Cells(i + 7, 1) = ""
            .Cells(i + 7, 2) = ""
            .Cells(i + 7, 3) = ""
            .Cells(i + 7, 4) = ""
            .Cells(i + 7, 5) = ""
            .Cells(i + 7, 6) = ""
           
            i = i + 1
        Loop
       
         i = 5
         
         
        Do Until i >= 100
            For j = 1 To 6
                'Worksheets("Sheet1").Cells(i + 7, j).Select
                With xl0.ActiveSheet
                    .Cells(i + 7, j).Select
'it always bugs here
                    Selection.Borders(xlDiagonalDown).LineStyle = xlNone
                    Selection.Borders(xlDiagonalUp).LineStyle = xlNone
                    Selection.Borders(xlEdgeLeft).LineStyle = xlNone
                    Selection.Borders(xlEdgeTop).LineStyle = xlNone
                    Selection.Borders(xlEdgeBottom).LineStyle = xlNone
                    Selection.Borders(xlEdgeRight).LineStyle = xlNone
                    Selection.Borders(xlInsideVertical).LineStyle = xlNone
                    Selection.Borders(xlInsideHorizontal).LineStyle = xlNone
                End With
               
            Next
            i = i + 1
        Loop
        .Cells(1, 1) = "Temps : " & Format(Now, "hh:mm AM/PM")
        .Cells(2, 1) = "Date: " & Format(Now, "m/d/yyyy")
        .Cells(3, 1) = "Nom" & "          " & Form1.Text1.Text
        .Cells(4, 1) = "Prenom" & "          " & Form1.Text2.Text
        '.Cells(3, 2) = Form1.Text1.Text
        '.Cells(4, 2) = Form1.Text2.Text
        .Cells(3, 2) = "Maison #"
        .Cells(3, 3) = Form1.Text7.Text
        .Cells(4, 2) = "Cell #"
        .Cells(4, 3) = Form1.Text8.Text
        .Cells(5, 2) = "Travail #"
        .Cells(5, 3) = Form1.Text9.Text
        .Cells(3, 5) = "Plaque #"
        .Cells(3, 6) = Form1.Text14.Text
        .Cells(4, 5) = "VIN"
        .Cells(4, 6) = Form1.Text15.Text
        .Cells(6, 1) = "Vehicule"
        .Cells(6, 2) = Form1.Text12.Text
        .Cells(6, 3) = Form1.Text10.Text
        .Cells(6, 4) = Form1.Text11.Text
        .Cells(6, 5) = Form1.Text17.Text
        .Cells(6, 6) = Form1.Text16.Text
        .Cells(5, 1) = "Estimate #" & "               " & Form1.Label70.Caption
        '.Cells(5, 2) = Form1.Label70.Caption
        '.Cells(1, 1) = Form1.Text65.Text 'header
       
        '.Cells(8, 1) = "Order #"
        .Cells(9, 1) = "Description"
        .Cells(9, 2) = "Piece #"
        .Cells(9, 3) = "Main d'oeuvre"
        .Cells(9, 4) = "Qty"
        .Cells(9, 5) = "Prix/Temps/%"
        .Cells(9, 6) = "Total"
       
         i = 4
         j = 1
        Do Until Form1.MSFlexGrid2.TextArray(Form1.Fgi2(j, 2)) = ""
            .Cells(i + 7, 1) = Form1.MSFlexGrid2.TextArray(Form1.Fgi2(j, 2))
            .Cells(i + 7, 2) = Form1.MSFlexGrid2.TextArray(Form1.Fgi2(j, 3))
            .Cells(i + 7, 3) = Form1.MSFlexGrid2.TextArray(Form1.Fgi2(j, 4))
            .Cells(i + 7, 4) = Form1.MSFlexGrid2.TextArray(Form1.Fgi2(j, 5))
            .Cells(i + 7, 5) = Form1.MSFlexGrid2.TextArray(Form1.Fgi2(j, 6))
            .Cells(i + 7, 6) = Form1.MSFlexGrid2.TextArray(Form1.Fgi2(j, 7))
            '.Cells(i + 7, 7) = Form1.MSFlexGrid2.TextArray(Form1.Fgi2(j, 7))
           
       
            i = i + 1
            j = j + 1
        Loop
       
       
        For j = 4 To i
           
            Select Case .Cells(j + 7, 3)
           
                Case "P"
                 .Cells(j + 7, 3) = "Part"
                Case "D"
                 .Cells(j + 7, 3) = "Rabais"
                Case "1"
                 .Cells(j + 7, 3) = Form1.Text57.Text * (1 + Form1.Text58.Text)
                 If (Form1.Option2.Value = True) Then
                    .Cells(j + 7, 5) = .Cells(j + 7, 6)
                    .Cells(j + 7, 3) = " "
                 End If
                Case "2"
                 .Cells(j + 7, 3) = Form1.Text59.Text * (1 + Form1.Text60.Text)
                 If (Form1.Option2.Value = True) Then
                    .Cells(j + 7, 5) = .Cells(j + 7, 6)
                    .Cells(j + 7, 3) = " "
                 End If
                 
                Case "3"
                 .Cells(j + 7, 3) = Form1.Text61.Text * (1 + Form1.Text62.Text)
                 If (Form1.Option2.Value = True) Then
                    .Cells(j + 7, 5) = .Cells(j + 7, 6)
                    .Cells(j + 7, 3) = " "
                 End If
               
                Case "F"
                    .Cells(j + 7, 3) = ""
               
            End Select
               
        Next
       ' .Cells(i + 10, 1) = Form1.Text66.Text
        i = i + 1
        .Cells(i + 7, 5) = Form1.Label31.Caption
        .Cells(i + 7, 6) = Form1.Label43.Caption
        i = i + 1
        .Cells(i + 7, 5) = Form1.Label32.Caption
        .Cells(i + 7, 6) = Form1.Label44.Caption
       
        ' Worksheets("Sheet1").Cells(i + 7, 6).Select
        .Cells(i + 7, 6).Select
        Selection.Borders(xlDiagonalDown).LineStyle = xlNone
        Selection.Borders(xlDiagonalUp).LineStyle = xlNone
        Selection.Borders(xlEdgeLeft).LineStyle = xlNone
        Selection.Borders(xlEdgeTop).LineStyle = xlNone
        With Selection.Borders(xlEdgeBottom)
            .LineStyle = xlContinuous
            .Weight = xlThin
            .ColorIndex = xlAutomatic
        End With
        Selection.Borders(xlEdgeRight).LineStyle = xlNone
        Selection.Borders(xlInsideVertical).LineStyle = xlNone
       
        ' Worksheets("Sheet1").Cells(i + 7, 5).Select
        .Cells(i + 7, 5).Select
        Selection.Borders(xlDiagonalDown).LineStyle = xlNone
        Selection.Borders(xlDiagonalUp).LineStyle = xlNone
        Selection.Borders(xlEdgeLeft).LineStyle = xlNone
        Selection.Borders(xlEdgeTop).LineStyle = xlNone
        With Selection.Borders(xlEdgeBottom)
            .LineStyle = xlContinuous
            .Weight = xlThin
            .ColorIndex = xlAutomatic
        End With
        Selection.Borders(xlEdgeRight).LineStyle = xlNone
        Selection.Borders(xlInsideVertical).LineStyle = xlNone
       
         i = i + 1
       
        .Cells(i + 7, 5) = Form1.Label39.Caption
        .Cells(i + 7, 6) = Form1.Label45.Caption
        i = i + 1
        .Cells(i + 7, 5) = Form1.Label40.Caption
        .Cells(i + 7, 6) = Form1.Label46.Caption
        i = i + 1
        .Cells(i + 7, 5) = Form1.Label41.Caption
        .Cells(i + 7, 6) = Form1.Label47.Caption
        ' Worksheets("Sheet1").Cells(i + 7, 6).Select
        .Cells(i + 7, 6).Select
        Selection.Borders(xlDiagonalDown).LineStyle = xlNone
        Selection.Borders(xlDiagonalUp).LineStyle = xlNone
        Selection.Borders(xlEdgeLeft).LineStyle = xlNone
        Selection.Borders(xlEdgeTop).LineStyle = xlNone
        With Selection.Borders(xlEdgeBottom)
            .LineStyle = xlContinuous
            .Weight = xlThin
            .ColorIndex = xlAutomatic
        End With
        Selection.Borders(xlEdgeRight).LineStyle = xlNone
        Selection.Borders(xlInsideVertical).LineStyle = xlNone
        ' Worksheets("Sheet1").Cells(i + 7, 5).Select
        .Cells(i + 7, 5).Select
        Selection.Borders(xlDiagonalDown).LineStyle = xlNone
        Selection.Borders(xlDiagonalUp).LineStyle = xlNone
        Selection.Borders(xlEdgeLeft).LineStyle = xlNone
        Selection.Borders(xlEdgeTop).LineStyle = xlNone
        With Selection.Borders(xlEdgeBottom)
            .LineStyle = xlContinuous
            .Weight = xlThin
            .ColorIndex = xlAutomatic
        End With
        Selection.Borders(xlEdgeRight).LineStyle = xlNone
        Selection.Borders(xlInsideVertical).LineStyle = xlNone
       
       
        i = i + 1
        .Cells(i + 7, 5) = Form1.Label42.Caption
        .Cells(i + 7, 6) = Form1.Label48.Caption
       
       
       
    'End With
    '------------------------------------------------------------
    ActiveSheet.PageSetup.LeftHeaderPicture.filename = _
        "C:\Documents and Settings\zetc\Desktop\nick&alex\A&N\logo.JPG"
    With ActiveSheet.PageSetup.LeftHeaderPicture
        .Height = 30.75
        .Width = 44.25
    End With
    With ActiveSheet.PageSetup
        .PrintTitleRows = ""
        .PrintTitleColumns = ""
    End With
    ActiveSheet.PageSetup.PrintArea = ""
    With .PageSetup
        .LeftHeader = "&G"
        .CenterHeader = Form1.Text65.Text
        .RightHeader = ""
        .LeftFooter = ""
        .CenterFooter = Form1.Text66.Text
        .RightFooter = ""
        .LeftMargin = Application.InchesToPoints(0.75)
        .RightMargin = Application.InchesToPoints(0.75)
        .TopMargin = Application.InchesToPoints(1)
        .BottomMargin = Application.InchesToPoints(1)
        .HeaderMargin = Application.InchesToPoints(0.5)
        .FooterMargin = Application.InchesToPoints(0.5)
        .PrintHeadings = False
        .PrintGridlines = False
        .PrintComments = xlPrintNoComments
        .PrintQuality = 600
        .CenterHorizontally = False
        .CenterVertically = False
        .Orientation = xlLandscape
        .Draft = False
        .PaperSize = xlPaperLetter
        .FirstPageNumber = xlAutomatic
        .Order = xlDownThenOver
        .BlackAndWhite = False
        .Zoom = 100
        .PrintErrors = xlPrintErrorsDisplayed
    End With
  End With
    '------------------------------------------------------------
   
    ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
    xl0.ActiveWorkbook.Save
   

    xl0.Workbooks.Close
     

    xl0.Quit
    Set xl0 = Nothing
'mExcelApp.ActiveWorkbook.Close True / False, mFileName

 End Function
0
Comment
Question by:calin131
  • 4
  • 3
7 Comments
 
LVL 32

Expert Comment

by:Erick37
ID: 13509053
I think you need to qualify the Selection object back to your Application (xl0) object

>>'it always bugs here <<
xl0.Selection.Borders(xlDiagonalDown).LineStyle = xlNone
^^add xl0. to qualify the Selection object.

Excel automation fails second time code runs
http://support.microsoft.com/default.aspx?scid=kb;en-us;178510

0
 
LVL 2

Author Comment

by:calin131
ID: 13509204
now it says object required at the line where u said that i should put x10.
0
 
LVL 32

Accepted Solution

by:
Erick37 earned 375 total points
ID: 13509327
Dim xl0 As New Excel.Application '<< this is your declaration for the Excel object

it is xl0 and not x10 (It looks the same)
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
LVL 2

Author Comment

by:calin131
ID: 13509512
i did that but the thing is that when i print it still prints whatever it was printed let's say at 12:53even though it is 14:00 now
0
 
LVL 32

Expert Comment

by:Erick37
ID: 13509605
I'm sorry, I did not understand your last comment.
0
 
LVL 2

Author Comment

by:calin131
ID: 13509727
it is ok i fixed it. the main thing was that every where i was making a reference to a excel thing like activesheet or workbook ... i had to put the "xl0." because if i didnt then it will just stay in the memory of my computer as open. now that i fixded that and the xl0 instead of the x10 all is good and working thank you very much
0
 
LVL 2

Author Comment

by:calin131
ID: 13521657
thanks bro you opened my eyes on this one
you deserve the credit
just one more thing : if i unswer questions how do i get the points for that is it the person who posted the question that will agree to give me the points or is it an expert who will decide that
thanks in advance
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction While answering a recent question (http://www.experts-exchange.com/Q_27402310.html) in the VB classic zone, I wrote some VB code in the (Office) VBA environment, rather than fire up my older PC.  I didn't post completely correct code o…
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
Suggested Courses
Course of the Month15 days, 6 hours left to enroll

577 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