?
Solved

open and close excel file from vb6

Posted on 2005-03-10
7
Medium Priority
?
330 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

I’ve seen a number of people looking for examples of how to access web services from VB6.  I’ve been using a test harness I built in VB6 (using many resources I found online) that I use for small projects to work out how to communicate with web serv…
Article by: Martin
Here are a few simple, working, games that you can use as-is or as the basis for your own games. Tic-Tac-Toe This is one of the simplest of all games.   The game allows for a choice of who goes first and keeps track of the number of wins for…
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…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…
Suggested Courses
Course of the Month9 days, 18 hours left to enroll

762 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