1 Attachment(s)
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!
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?
:wave:
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!
Re: How to format tables in excel from vb6
try changing
wsxl.Cells(introw, intcol).Value =
to
wsxl.Cells(introw +13, intcol).Value =
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!
Re: How to format tables in excel from vb6
try like
vb Code:
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