Results 1 to 6 of 6

Thread: How to format tables in excel from vb6

  1. #1

    Thread Starter
    Addicted Member
    Join Date
    Feb 2009
    Posts
    211

    How to format tables in excel from vb6

    Hi i'm making a program where the user can export his data from msflexgrid to ms excel. here's my code:


    Code:
    Private Sub Export_Click()
    Dim intresponse As Integer
    
    intresponse = msgbox("Do you want to save your record, before exporting it?", vbYesNo)
    
    If intresponse = vbYes Then
        Call Sayb
    Else:
    
    Dim therows As Integer
    Dim thecols As Integer
    Dim Gridstyle As Integer
    Dim worksheetname As String
    
    Gridstyle = 1
    
    Dim objxl As New Excel.Application
    Dim wbxl As New Excel.Workbook
    Dim wsxl As New Excel.Worksheet
    Dim introw As Integer 'counter
    Dim intcol As Integer  'counter
    
    therows = MSHFlexGrid1.Rows
    thecols = MSHFlexGrid1.Cols
    
    If Not IsObject(objxl) Then
        msgbox "You need Microsoft Excel to use this function", _
           vbExclamation, "Print to Excel"
        Exit Sub
    End If
    
    On Error Resume Next
    
    ' open Excel
    objxl.Visible = True
    Set wbxl = objxl.Workbooks.Add
    Set wsxl = objxl.ActiveSheet
    
    ' name the worksheet
    With wsxl
        If Not worksheetname = "" Then
            .Name = worksheetname
        End If
    End With
        
    ' fill worksheet
    For introw = 1 To therows
        For intcol = 1 To thecols
            With MSHFlexGrid1
                wsxl.Cells(introw, intcol).Value = _
                   .TextMatrix(introw - 1, intcol - 1) & " "
            End With
        Next
    Next
    
    ' format the look
    For intcol = 1 To thecols
        wsxl.Columns(intcol).AutoFit
        'wsXL.Columns(intCol).AutoFormat (1)
        wsxl.Range("a1", Right(wsxl.Columns(thecols).AddressLocal, _
             1) & therows).AutoFormat Gridstyle
    Next
    'headers
    
    wsxl.PageSetup.CenterHeader = "GS1 Phils. Product List"
    wsxl.PageSetup.LeftHeader = "Enter Company Name,Fax Number, Tel. Number, Contact Person and Date Submitted"
    wsxl.PageSetup.LeftFooter = "&D"
    wsxl.PageSetup.RightFooter = "Page &P of &N"
    
    'format
    wsxl.Columns("D").Delete
    wsxl.Range("b2", "b50000").NumberFormat = 0
    wsxl.PageSetup.Zoom = False
    wsxl.PageSetup.FitToPagesTall = 1
    wsxl.PageSetup.FitToPagesWide = 1
    wsxl.PageSetup.Orientation = xlLandscape
    'wsxl.PageSetup.PaperSize = xlPaperA4
    wsxl.PageSetup.PaperSize = xlPaperLegal
    
    End If
    End Sub
    attached is an example of what i need. the text with red background is the format that i need to add.
    Thank you so much!
    Attached Images Attached Images  

  2. #2
    PowerPoster abhijit's Avatar
    Join Date
    Jun 1999
    Location
    Chit Chat Forum.
    Posts
    3,226

    Thumbs down Re: How to format tables in excel from vb6

    You will need to use the following code after selecting the range in your excel sheet.

    Code:
    Range("A1:C6").Select 'Select Range.
        With Selection.Interior
            .ColorIndex = 6 'Yellow Color
            .Pattern = xlSolid 'Solid Pattern.
        End With
    I have done this using VBA. Does this work for you?

    Everything that has a computer in will fail. Everything in your life, from a watch to a car to, you know, a radio, to an iPhone, it will fail if it has a computer in it. They should kill the people who made those things.- 'Woz'
    save a blobFileStreamDataTable To Text Filemy blog

  3. #3

    Thread Starter
    Addicted Member
    Join Date
    Feb 2009
    Posts
    211

    Re: How to format tables in excel from vb6

    I'm sorry i think i didn't explain my concern clearly. if i click Export, the records from msflexgrid is transfer to excel, it will start populating in B1 and so on until the condition is met. My problem is i want to start populating records in B14 and so on, i don't know how to do that.I'm going to insert User's Information in rows A1 to A14 that's why i want to adjust my rows(pls refer to red background). Plus i want to add another col. (the one highlight with red namely "No."). for numbering purposes only so that the user will know how many records they have.

    Thanks!

  4. #4
    PowerPoster
    Join Date
    Dec 2004
    Posts
    25,618

    Re: How to format tables in excel from vb6

    try changing
    wsxl.Cells(introw, intcol).Value =

    to
    wsxl.Cells(introw +13, intcol).Value =
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

  5. #5

    Thread Starter
    Addicted Member
    Join Date
    Feb 2009
    Posts
    211

    Re: How to format tables in excel from vb6

    Thanks it work. but i have another concern, i want to add another column, name Number. its an autonumber. so that the user has an idea how may records he has. the example is included on my attachment the one with red background beside barcode number column.

    thanks!

  6. #6
    PowerPoster
    Join Date
    Dec 2004
    Posts
    25,618

    Re: How to format tables in excel from vb6

    try like
    vb Code:
    1. For introw = 1 To therows
    2.     wsxl.Cells(introw +14, 1).Value = introw
    3.     For intcol = 1 To thecols +1
    4.         With MSHFlexGrid1
    5.             wsxl.Cells(introw +13, intcol +1).Value = _
    6.                .TextMatrix(introw - 1, intcol - 1) & " "
    7.         End With
    8.     Next
    9. Next
    adjust it a bit if it turns out to be in the wrong place
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width