Solved

VB excel

Posted on 2004-08-13
8
176 Views
Last Modified: 2010-05-02
This is my vb code to execute sql code and then send the result to a csv file; I then save it as an excel file. The problem is date fields in my csv file gets written as :
DOB
#1940-10-10#
#1940-10-10#
#1940-10-10#
If I go to the excel file and delete the # , in the formula bar,  then it changes to 10/10/1940. But I want to do that in the program so that that I don’t have to go and manually remove all those # signs..  Please paste the code, in my original code.. so that I can understand what you are talking about.

Option Explicit
Public strUserName As String
Public strPassWord As String
Public strBegDate As String
Public strEndDate As String
Private sFolderLoc As String
Private sSQL As String
Private conn As ADODB.Connection
Private rstRecordSet As ADODB.Recordset


Const SQL_Home = "C:\sql"
Public Sub Main()
Dim connStr As String
Dim sFileName As String
Dim sSQL As String

   On Error GoTo main_error
   
connStr = "PROVIDER=MSDASQL;" & _
            "DRIVER={microsoft odbc for oracle};" & _
            "SERVER=rxh_prod;" & _
            "UID=" & ";PWD=" & ";"
   
   
   
   

    Set conn = New ADODB.Connection

    conn.CursorLocation = adUseClient

    conn.Open connStr
   
    DoEvents

    Screen.MousePointer = vbHourglass
   
    sFolderLoc = "C:\"   ' assign output location
   
    Set rstRecordSet = New ADODB.Recordset
   
   strBegDate = ConvertDate(Format(DateAdd("m", -8, Now), "mm/dd/yyyy"))
   strEndDate = ConvertDate(Format(DateAdd("d", -1, Now), "mm/dd/yyyy"))

   

   
    sSQL = " "
    sSQL = Build_Query(SQL_Home & "\sample.sql")
       
    rstRecordSet.Open sSQL, conn, adOpenForwardOnly
   

     sFileName = sFolderLoc & "\test.csv"
     
rstRecordSet.Close
   
     
     WriteExcelReport1 sFileName
     
     rstRecordSet.Close

     Screen.MousePointer = vbDefault
   
     
     

    If Not rstRecordSet Is Nothing Then     'unload form and close recordset and connection to Oracle
       If rstRecordSet.State <> 0 Then
              rstRecordSet.Close
       End If
       Set rstRecordSet = Nothing
    End If

    If Not conn Is Nothing Then
       conn.Close
       Set conn = Nothing
    End If
   
       
    MsgBox "Finished"
   
    Screen.MousePointer = vbNormal
   
    End
   
main_error:

    Screen.MousePointer = vbDefault
   
    MsgBox "Error- " & Err.Description & " - contact Report Solutions"
   
    If Not rstRecordSet Is Nothing Then     'unload form and close recordset and connection to Oracle
       If rstRecordSet.State <> 0 Then
              rstRecordSet.Close
       End If
       Set rstRecordSet = Nothing
    End If

    If Not conn Is Nothing Then
       conn.Close
       Set conn = Nothing
    End If
   
    End
   
    End Sub


   
Private Function Build_Query(sFileName As String)
   
    Dim sInput As String
    Dim sOutput As String
   
    Open sFileName For Input As #1

    Line Input #1, sInput   ' Get a line of input from the file(sFileName) and store it in the sInput
                            '  string
   
    sOutput = sInput
   
    Do While Not EOF(1)
       
        Line Input #1, sInput
        sOutput = sOutput & Chr(10) & sInput
    Loop
   
    sOutput = Replace(sOutput, "&begdate", "'" & strBegDate & "'")
    sOutput = Replace(sOutput, "&enddate", "'" & strEndDate & "'")
    Close #1
    Build_Query = sOutput
   
End Function

Private Sub remove_file(fname As String)
    If Len(Dir(fname)) Then
        Kill fname
    End If
End Sub
Public Function ConvertDate(ByVal strDate As Date) As String
 
  'convert  date to Oracle date
 
  Dim strDateOut As String
  'Dim ConvertDate As String
 
 
  Select Case Val(Format(strDate, "mm"))
    Case 1
        strDateOut = Format(strDate, "dd") & "-JAN-" & Format(strDate, "YYYY") & ""
    Case 2
        strDateOut = Format(strDate, "dd") & "-FEB-" & Format(strDate, "YYYY") & ""
    Case 3
        strDateOut = Format(strDate, "dd") & "-MAR-" & Format(strDate, "YYYY") & ""
    Case 4
        strDateOut = Format(strDate, "dd") & "-APR-" & Format(strDate, "YYYY") & ""
    Case 5
        strDateOut = Format(strDate, "dd") & "-MAY-" & Format(strDate, "YYYY") & ""
    Case 6
        strDateOut = Format(strDate, "dd") & "-JUN-" & Format(strDate, "YYYY") & ""
    Case 7
        strDateOut = Format(strDate, "dd") & "-JUL-" & Format(strDate, "YYYY") & ""
    Case 8
        strDateOut = Format(strDate, "dd") & "-AUG-" & Format(strDate, "YYYY") & ""
    Case 9
        strDateOut = Format(strDate, "dd") & "-SEP-" & Format(strDate, "YYYY") & ""
    Case 10
        strDateOut = Format(strDate, "dd") & "-OCT-" & Format(strDate, "YYYY") & ""
    Case 11
        strDateOut = Format(strDate, "dd") & "-NOV-" & Format(strDate, "YYYY") & ""
    Case Else
        strDateOut = Format(strDate, "dd") & "-DEC-" & Format(strDate, "YYYY") & ""
  End Select

  ConvertDate = strDateOut
 
End Function



Private Sub WriteOutReport1(ByVal sFileName As String)
 'remove_file (sFileName)
     
     If rstRecordSet.RecordCount > 0 Then 'put data into file which will be read
     Open sFileName For Output As #1
     
        Write #1, rstRecordSet.Fields(0).Name, rstRecordSet.Fields(1).Name, _
                        rstRecordSet.Fields(2).Name, rstRecordSet.Fields(3).Name, _
                        rstRecordSet.Fields(4).Name, rstRecordSet.Fields(5).Name, _
                        rstRecordSet.Fields(6).Name, rstRecordSet.Fields(7).Name, _
                        rstRecordSet.Fields(8).Name, rstRecordSet.Fields(9).Name, _
                        rstRecordSet.Fields(10).Name, rstRecordSet.Fields(11).Name, _
                        rstRecordSet.Fields(12).Name, rstRecordSet.Fields(13).Name, _
                        rstRecordSet.Fields(14).Name, rstRecordSet.Fields(15).Name, _
                        rstRecordSet.Fields(16).Name, rstRecordSet.Fields(17).Name, _
                        rstRecordSet.Fields(18).Name, rstRecordSet.Fields(19).Name
                       


        Do While Not rstRecordSet.EOF
                    Write #1, rstRecordSet.Fields(0), rstRecordSet.Fields(1), _
                        rstRecordSet.Fields(2), rstRecordSet.Fields(3), _
                        rstRecordSet.Fields(4), rstRecordSet.Fields(5), _
                        rstRecordSet.Fields(6), rstRecordSet.Fields(7), _
                        rstRecordSet.Fields(8), rstRecordSet.Fields(9), _
                        rstRecordSet.Fields(10), rstRecordSet.Fields(11), _
                        rstRecordSet.Fields(12), rstRecordSet.Fields(13), _
                        rstRecordSet.Fields(14), rstRecordSet.Fields(15), _
                        rstRecordSet.Fields(16), rstRecordSet.Fields(17), _
                        rstRecordSet.Fields(18), rstRecordSet.Fields(19)
                       

        rstRecordSet.MoveNext
        Loop

        Close #1

      'rstRecordSet.Close
    End If
End Sub

Private Sub WriteExcelReport1(ByVal sFileName As String)
   'remove_file (sFileName)
     'Create a new instance of Excel
   Dim oExcel As Object
   Dim oBook As Object
   Dim oSheet As Object
   Set oExcel = CreateObject("Excel.Application")
       
   'Open the text file
   Set oBook = oExcel.Workbooks.Open("C:\test.csv")

           
   
   'Save as Excel workbook and Quit Excel
   oBook.SaveAs sFolderLoc & "\EXCELREPORT_" & Format(Now, "mm""-""dd""-""yyyy") & ".xls", xlWorkbookNormal
   oExcel.Quit
   
    'remove_file (sFileName) 'this removes the csv file so that I only  see  the excel file
   
End Sub

0
Comment
Question by:Sara_j_11
  • 5
  • 3
8 Comments
 
LVL 76

Expert Comment

by:David Lee
ID: 11797720
How about

    Format(rstRecordSet.Fields(x), "mm/dd/yyyy")

for whichever fields are date fields in WriteutRepoirt1?
0
 
LVL 76

Expert Comment

by:David Lee
ID: 11797724
That should be WriteOutReport1 in my last post.
0
 

Author Comment

by:Sara_j_11
ID: 11814087
Thanks, but I can also getting another problem ; in some caaes the date field is empty; sometimes another field(not the date) is also empty ; in such cases I would like that field to be empty in the report. But uit shows up as #null# how do I avoid this?
0
 
LVL 76

Accepted Solution

by:
David Lee earned 500 total points
ID: 11814535
I'm not sure I can answer that question.  I don't use or have access to Oracle.  In VB a date field is apparently not allowed to be empty or to contain a null value.  Merely creating a variable of type date causes it to contain the value 12:00:00 AM 12/31/1899.  I would use the debugger and check the both the data type and value of one of these date fields that returns #null#.  If it is truly returning a null value, then we can use the IsNull test to check for that.  Something like:

    Iif(IsNull(rstRecordSet.Fields(x)), "", Format(rstRecordSet.Fields(x), "mm/dd/yyyy"))

This will test the value in the field coming from the recordset to see if it contains a null value.  If it does, then we'll return an empty string, otherwise we'll return a formatted date.  If the recorset field doesn't actually contain a null value, then maybe it contains a string value of null (i.e. "null"), in which case we can modify the statement I gave to check for that.  That would look like:

    Iif(rstRecordSet.Fields(x) = "null", "", Format(rstRecordSet.Fields(x), "mm/dd/yyyy"))

Otherwise, let me know what the type and value of the field is and we'll see if we can devise a test for it.
0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 

Author Comment

by:Sara_j_11
ID: 12059693
THIS MAY NOT WORK SINCE i AM SAVING THE CSV FIle to an excel file ; so I dont think I can access
the recordset again . So I need to figure out a way to save the result set into excel format, in a different way than what I am doing, so that I can use this solution...
0
 
LVL 76

Expert Comment

by:David Lee
ID: 12060578
Hi, Sara_j_11.

I don't understand what you mean by "I don't think I can access the recordset again."  Does that mean that you only had one chance at doing this and cannot access the Oracle table again?  Otherwise, why would you need to access the recordset a second time to make this work?
0
 

Author Comment

by:Sara_j_11
ID: 12064590
Because I have this statement (rstRecordSet.Close) after I create the csv file and then I am calling another function to save the csv file as an excel file... Please see my code; If you think I can change it, please make the changes and paste in the code so that I will be able to undestand more clearly since I am new to VB.. Thanks...
0
 
LVL 76

Expert Comment

by:David Lee
ID: 12070974
I looked over your code.  Unless there are some missing pieces of code I don't understand several aspects of the Main subroutine.  Look through the code below for my comments.  Mostly I don't understand what the procedure WriteOutReport1 is for since I don't see it being used.


Public Sub Main()
    Dim connStr As String
    Dim sFileName As String
    Dim sSQL As String
    On Error GoTo main_error
    connStr = "PROVIDER=MSDASQL;" & _
            "DRIVER={microsoft odbc for oracle};" & _
            "SERVER=rxh_prod;" & _
            "UID=" & ";PWD=" & ";"
    Set conn = New ADODB.Connection
    conn.CursorLocation = adUseClient
    conn.Open connStr
    DoEvents
    Screen.MousePointer = vbHourglass
    sFolderLoc = "C:\"   ' assign output location
    Set rstRecordSet = New ADODB.Recordset
    strBegDate = ConvertDate(Format(DateAdd("m", -8, Now), "mm/dd/yyyy"))
    strEndDate = ConvertDate(Format(DateAdd("d", -1, Now), "mm/dd/yyyy"))
    sSQL = " "
    sSQL = Build_Query(SQL_Home & "\sample.sql")
    rstRecordSet.Open sSQL, conn, adOpenForwardOnly
    sFileName = sFolderLoc & "\test.csv"

    'Why close the recordset just two lines after opening it and without doing anything with it?
    rstRecordSet.Close
   
    'Shouldn't there be a call to WriteOutReport1 before running the code to import the output into Excel?  
    WriteExcelReport1 sFileName
     
    'At this point the recordset is already closed.  Why close it again?
    rstRecordSet.Close
    Screen.MousePointer = vbDefault

    'The recordset isn't going to equal nothing at this point and it's already closed.  I don't see any use for this code.
    If Not rstRecordSet Is Nothing Then     'unload form and close recordset and connection to Oracle
       If rstRecordSet.State <> 0 Then
              rstRecordSet.Close
       End If
       Set rstRecordSet = Nothing
    End If

    'What's the point in this test?  Just close the connection and set it to nothing.
    If Not conn Is Nothing Then
       conn.Close
       Set conn = Nothing
    End If
    MsgBox "Finished"
    Screen.MousePointer = vbNormal
    End
main_error:
    Screen.MousePointer = vbDefault
    MsgBox "Error- " & Err.Description & " - contact Report Solutions"
    If Not rstRecordSet Is Nothing Then     'unload form and close recordset and connection to Oracle
       If rstRecordSet.State <> 0 Then
              rstRecordSet.Close
       End If
       Set rstRecordSet = Nothing
    End If
    If Not conn Is Nothing Then
       conn.Close
       Set conn = Nothing
    End If
    End
End Sub
0

Featured Post

Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Suggested Solutions

There are many ways to remove duplicate entries in an SQL or Access database. Most make you temporarily insert an ID field, make a temp table and copy data back and forth, and/or are slow. Here is an easy way in VB6 using ADO to remove duplicate row…
Introduction In a recent article (http://www.experts-exchange.com/A_7811-A-Better-Concatenate-Function.html) for the Excel community, I showed an improved version of the Excel Concatenate() function.  While writing that article I realized that no o…
Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…
Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…

746 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now