Visual Basic : Generate A Csv File

First Add reference to Microsoft Activex Data Objects 2.5 Library

Option Explicit

Private m_cnDatabase As ADODB.Connection

Private Sub cmdExport_Click()

Call ExportToCVS("tbl_Watcher")

End Sub

Private Sub Form_Load()

Set m_cnDatabase = New ADODB.Connection

With m_cnDatabase

.CursorLocation = adUseClient

.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=D:\folder\Project1\AccessDirectory.mdb;"

.Open

End With

End Sub

Private Sub ExportToCVS(ByRef sTable As String)

Dim sExportLine As String

Dim rsData As ADODB.Recordset

Dim sSql As String

Dim hFile As Long

Dim oField As ADODB.Field

On Error GoTo PROC_ERR

' ' Open the table. '

Set rsData = New ADODB.Recordset

With rsData

.ActiveConnection = m_cnDatabase

.CursorLocation = adUseClient

.CursorType = adOpenForwardOnly

.LockType = adLockReadOnly

.Source = "SELECT * FROM " & sTable

.Open

If (.State = adStateOpen) Then

hFile = FreeFile Open "C:\Temp\" & sTable & ".CSV" For Output As hFile

' Print file header with fieldnames.

sExportLine = ""

For Each oField In .Fields

sExportLine = sExportLine & oField.Name & ","

Next

sExportLine = VBA.Left$(sExportLine, Len(sExportLine) - 1)

Print #hFile, sExportLine

Do Until .EOF

sExportLine = ""

For Each oField In .Fields

sExportLine = sExportLine & oField.Value & ","Next

sExportLine = VBA.Left$(sExportLine, Len(sExportLine) - 1)

Print #hFile, sExportLine

.MoveNext

Loop

End If

End With

PROC_EXIT: ' ' Clean up and exit gracefully. '

If (Not rsData Is Nothing) Then

With rsData

If (.State <> adStateClosed) Then

.Close

End If

End With

End If

If (hFile <> 0) Then

Close hFile

End If

PROC_ERR:

Select Case Err.Number

Case Is <> 0

MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure ExportToCVS of Form frmMain"

Err.Clear

Resume PROC_EXIT

End Select

End Sub

Useful Excel Visual Basic Macros for Programmers

Excel VB Macro is exactly useful because it can do things in your workbooks for you, like manipulating cells and worksheets. Excel Visual Basic (VB) gives us a number of methods to interact with worksheets and cells, and I will cover some of the more intuitive methods here.
One of my favorite ways to interact with cells in a worksheet is rather direct. I like it because it is easy to double-check and conceptualize.
Let's look at the basic statement that is the second line in the following very short Excel macro.
Sub put_value_in_Cell
Worksheets("Sheet2").Cells(3, 7).Value = 1
End Sub

This statement assigns the Cell located at (3,7) in Sheet2 the value 1. That literally means that is you go to that sheet1 in your active Excel Workbook, you'll see a 1 in the cell located at (3,7).
What is this (3,7)? We are using index numbers for the column instead of the column-letters you might be accustomed to. Note: Index for cells begins at 1, not zero. E.g. there isn'tt a row zero. I'll re-type the last macro, but this time using variables that could make it more clear.


Sub put_value_in_Cell ()
my_row = 3 '
my_column = 7
my_workSheet = "Sheet2"
Worksheets(my_workSheet).Cells(my_row, my_column).Value = 1
End Sub

Using these index numbers inside loops can really be useful to get stuff done. For example, to go down the first column of our Excel worksheet and put a zero into the first 10 cells, you could do something like this:

Sub an_example()
For row_counter = 1 to 10
Worksheets("Sheet1").Cells(row_counter, 1).Value = 0
Next row_counter
End Sub

or generally...

Sub an_example()
For row_counter = 1 to 10
'Whatever you want your macro to do...Check or assign values, etc.
Next row_counter
End Sub

To compare the values of two cells, you can do this:

Sub an_example2()
For row_counter = 1 to 10
If Worksheets("Sheet1").Cells(1, 1).Value = _
Worksheets("Sheet1").Cells(1, 2).Value Then
'Whatever you want done if the cells are equal in value.
'Note the _ in the If statement is there because it allows
'a statement to span multiple lines so we can see it all once
without off the page like this line.
End if
Next row_counter
End Sub

To compare the values of two cells, you can do this:

It does not stop with the values of cells either. You may have noticed earlier that we used .Value after specifying a cell. However there are other properties and methods of cells that Excel VB provides that are really useful. For example the following statement that could be in a macro checks whether the cell contains a formula:

If Worksheets("Sheet1").Cells(theRow, theCol).HasFormula Then

There really are quite a number of things that may be accomplished with these techniques.

Write on excel sheets Using Visual basic 6.0

if you want to export data to Excel you can do two things :
1. load Excel application
2. using Recordset to import the data (you must have an excel file to do this)first of all you must add 'Microsoft Excel [version] Object Library' reference to your program you can do this by choose menu Project >> References ..
1. Load Excel App (you must have Microsoft Excell installed)
a. declare variable as Excel.Application and Excel.Workbookex.
dim objExcel as Excel.Application,
objBook as Excel.WorkBookb. open Excel application
On Error Resume Next
Set objExcel = GetObject(, "Excel.Application") 'if excel already open you can use
GetObject
If Err.Number ThenErr.ClearSet objExcel = CreateObject("Excel.Application") 'or CreateObject to open new Excel Applicationc. create new workbook and new sheetSet
objWorkbook = objExcel.Workbooks.AddobjWorkbook.ActiveSheet.Cells(1, 1) = value ' cell(1,1) means cell A1 ;
value is the value you want to fillnote :
you can also use range to merge cell and fill the valueex.objWorkbook.ActiveSheet.Range("A4:A6").Merge
objWorkbook.ActiveSheet.Range("A4:A6").Value = value
2. Using Recordset (you musn't have Microsoft Excell installed but you must have an excel file with the first row filled with any value --> for a column header; otherwise this 'Insert Into' statement wont work)
a. create and open a connection (i'm using ADODim Conn As ADODB.ConnectionSet Conn = New ADODB.ConnectionConn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=FileName.xls;Extended Properties=Excel 4.0;"b. fill the value to excell sheetsConn.Execute "Insert Into [Sheet1$] (A1) VALUES ('value') " ' [Sheet1$] is an active sheet

Coming on Patch Tuesday: 8 bulletins, 6 critical

The final Microsoft Patch Tuesday for 2008 will be a big one: 8 bulletins covering serious code execution flaws in a wide range of ubiquitous software.
According to the company’s advance notice mechanism, six of the eight bulletins will be rated “critical,” Microsoft’s highest severity rating. The “critical” updates will cover holes in the Windows operating system, Internet Explorer, Windows Media Player, Visual Basic and Visual Studio, Microsoft Word and Microsoft Excel.