Excel in VB6
-
:rolleyes: :-D hi der, If possible could someone please solve this problem of mine. I am a beginner and want to get my results in Excel format but the output which i need has to be formatted and i am not able to get that kinda format. Have already developed the application with backend in Access and need to get the data frm Access and display it in Excel. Have already incorp. Crystal reports and have put the export tab on it. but what i need is an Excel format. If anybody can do it please mail me i can send u the Excel file in the format which i want.:-O:zzz::laugh::cool::doh::rose::)
-
:rolleyes: :-D hi der, If possible could someone please solve this problem of mine. I am a beginner and want to get my results in Excel format but the output which i need has to be formatted and i am not able to get that kinda format. Have already developed the application with backend in Access and need to get the data frm Access and display it in Excel. Have already incorp. Crystal reports and have put the export tab on it. but what i need is an Excel format. If anybody can do it please mail me i can send u the Excel file in the format which i want.:-O:zzz::laugh::cool::doh::rose::)
The whole answer, in a nutshell, is to control Excel directly through VB. First, add a reference to the Excel Library through Project...References. Then, you can do things like the following:
Private Sub cmdStart_Click()
Dim xl As New Excel.Application
Dim myWorkBook As Excel.Workbook
Dim myWorkSheet As Excel.Worksheet
xl.Workbooks.Open "c:\000\Book1.xls"
Set myWorkBook = xl.ActiveWorkbook
Set myWorkSheet = myWorkBook.ActiveSheet
With myWorkSheet.Range("Headers")
With .Borders.Item(xlEdgeBottom)
.Weight = XlBorderWeight.xlThin
.LineStyle = XlLineStyle.xlContinuous
End With
.Font.Italic = True
End With
Dim intRow As Integer
Dim intTop As Integer
intTop = 3
For intRow = 1 To myWorkSheet.Range("Data").Rows.Count
Dim intRowOffset As Integer
intRowOffset = intRow + intTop
myWorkSheet.Cells(intRowOffset, 2).Font.Bold = True
myWorkSheet.Cells(intRowOffset, 2).Value = 5
Next
myWorkBook.Save
myWorkBook.Close
End SubWhat a piece of work is man, how noble in reason, how infinite in faculties, in form and moving how express and admirable . . . and yet to me, what is this quintessence of dust? -- Hamlet, Act II, Scene ii.
-
:rolleyes: :-D hi der, If possible could someone please solve this problem of mine. I am a beginner and want to get my results in Excel format but the output which i need has to be formatted and i am not able to get that kinda format. Have already developed the application with backend in Access and need to get the data frm Access and display it in Excel. Have already incorp. Crystal reports and have put the export tab on it. but what i need is an Excel format. If anybody can do it please mail me i can send u the Excel file in the format which i want.:-O:zzz::laugh::cool::doh::rose::)
In order to enter values in the sample Excel sheet you sent over, I'd do one of three things: 1) Hard-code the cell addresses of the locations in the spreadsheet where you intend to place values, i.e.,
myWorkSheet.Range("B6").Value = rsTable("field").Value2) Define each location in the template where you intend to store a value as a named range, then:myWorkSheet.Range("CashCollectionValueOne").Value = rsTable("field").Value3) Store the relative addresses of each location in the formatted sheet in a cross-reference table, then lookup the location of a value in the cross-reference. What a piece of work is man, how noble in reason, how infinite in faculties, in form and moving how express and admirable . . . and yet to me, what is this quintessence of dust? -- Hamlet, Act II, Scene ii.