SQL Server 2008 data to Excel with VB6

Question

I have written this code for extract button. It works perfectly. What I'd like to do is have the Excel app open and show the data in the sheet. For now after every extract I have to go to the c: drive in order to see the spreadsheet. Also, is there a way to autofit the columns, change header color. Thanks!

Dim FileNum As Integer
Dim strSQL As String
Dim rs As ADODB.Recordset
Dim strFileName As String

FileNum = FreeFile
strFileName = "C:\Test\TESTFILE.CSV"

Open strFileName For Output As #FileNum
Write #FileNum, "ID", "FirstName", "LastName", "DOB", "SSN", "SEX"

Set rs = New ADODB.Recordset

strSQL = "Select ID, FirstName, LastName, DOB, SSN, Sex from         dbo.tblTest_Clients Where ID = " & g_lngSelectedID

rs.Open strSQL, g_cnDatabase
rs.MoveFirst

Do While Not rs.EOF
Write #FileNum, rs("ID") & vbTab; rs("FirstName") & vbTab; rs("LastName") &    vbTab; rs("DOB") & vbTab; rs("SSN") & vbTab; rs("Sex")
rs.MoveNext
Loop

rs.Close
Close #FileNum

what have you tried to get the excel file to open and to autofit columns etc.? Show us the code you tried and where it's off and we can help. — Scott Holtzman, Nov 09 '15 at 16:23

score 0 · Answer 1 · edited May 23 '17 at 12:04

You're exporting into the CSV file. CSV's can't store color information so you'd have to open it, modify the colors then save it as a .xlsx or something of the sort.

Step 1) Open the file using excel and modify the header columns via macro. You can call this macro like so:

Call Open_Excel("C:\Test\TESTFILE.CSV")

Trying to open the workbook in separate instance

Sub Open_Excel(ByVal path As String)
  'Create a Microsoft Excel instance via code
  'using late binding. (No references required)
  Dim xlApp As Excel.Application
  Dim wbExcel As Workbook
  Dim xlSheet As Worksheet

  'Create a new instance of Excel
  Set xlApp = CreateObject("Excel.Application")

  'Open workbook
  Set wbExcel = xlApp.Workbooks.Open(path)

  'Open worksheet
  Set xlSheet = wbExcel.Worksheets(1)

  'Set header to whatever RGB color you want, also add other header formatting here
  xlSheet.UsedRange.Rows(1).Interior.Color = RGB(255,255,0)

  'Autofit columns
  xlSheet.UsedRange.Columns.AutoFit

  'Set the instance of Excel visible. (It's been hiding until now)
  xlApp.Visible = True

  'Release the workbook and application objects to free up memory
  Set wbExcel = Nothing
  Set xlApp = Nothing
  Set xlSheet = Nothing
End Sub

Step 2) You can either use VBA to perform a save, or just leave it as is, and it will prompt you to save it on exit (as excel normally does).

SQL Server 2008 data to Excel with VB6

1 Answers1