How to format tables in excel from vb6

[enigmagirl122]

Hi i'm making a program where the user can export his data from msflexgrid to ms excel. here's my 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!

[abhijit]

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

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?

[enigmagirl122]

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!

[westconn1]

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

to
wsxl.Cells(introw +13, intcol).Value =

[enigmagirl122]

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!

[westconn1]

try like

For introw = 1 To therows
    wsxl.Cells(introw +14, 1).Value = introw
    For intcol = 1 To thecols +1
        With MSHFlexGrid1
            wsxl.Cells(introw +13, intcol +1).Value = _
               .TextMatrix(introw - 1, intcol - 1) & " "
        End With
    Next
Next

adjust it a bit if it turns out to be in the wrong place