Solved

VISUAL BASIC - EXCEL

Posted on 2004-09-15
56
369 Views
Last Modified: 2013-11-25

 


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.
I want to do something like  Format(rstRecordSet.Fields(x), "mm/dd/yyyy")
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?


But I don’t know how to go about it. 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...PLEASE  PASTE   RELEVANT CODE  IN MY  CODE   SO THAT   I   CAN UNDERSTAND   BETTER.



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




   I want to do something like  Format(rstRecordSet.Fields(x), "mm/dd/yyyy")



But I don’t know how to go about it. 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
Comment
Question by:Sara_j_11
  • 27
  • 19
  • 5
  • +3
56 Comments
 
LVL 44

Expert Comment

by:bruintje
Comment Utility
you can use a set of formatting functions like

' ---------------------------------
Public Function GetSQLString(ByRef r_fld As ADODB.Field) As String
  GetSQLString = IIf(IsNull(r_fld.Value), "", RTrim(r_fld.Value))
End Function

Public Function GetSQLLong(ByRef r_fld As ADODB.Field) As Long
  GetSQLLong = IIf(IsNull(r_fld.Value), 0, r_fld.Value)
End Function

Public Function GetSQLDouble(ByRef r_fld As ADODB.Field) As Double
  GetSQLDouble = IIf(IsNull(r_fld.Value), 0, r_fld.Value)
End Function

Public Function GetSQLDate(ByRef r_fld As ADODB.Field) As Date
  GetSQLDate = IIf(IsNull(r_fld.Value), 0, format(r_fld.Value, "dd/mm/yyyy")
End Function
' ---------------------------------

these can be pasted in a seperated module you call lib or something

then in your original code you can use them with date fields like

GetSQLDate(rstRecordSet.Fields(x))

or string fields like

GetSQLString(rstRecordSet.Fields(x))

to be more precise in your code like
'-------------------------

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
                       
' shortened for clarity

        Do While Not rstRecordSet.EOF

                    Write #1, GetSQLDate(rstRecordSet.Fields(0)), GetSQLString(rstRecordSet.Fields(1))

        rstRecordSet.MoveNext
        Loop
        Close #1

      'rstRecordSet.Close
    End If
End Sub

'-------------------------

HTHAB
0
 
LVL 3

Expert Comment

by:domj
Comment Utility
You should just be able to replace the ...rstRecordSet.Fields(x)... entry with the one you suggested ...Format(rstRecordSet.Fields(x), "mm/dd/yyyy")... Because I dont know the index number of the field I can't paste into code for you but you just need to replace a with b.

Dom
0
 
LVL 23

Expert Comment

by:ahammar
Comment Utility
I didn't read your code because I don't have time right now but couldn't you take the "#" out just before it writes to the csv file.  Here is a short example:

If x is the string variable that holds the date...say #1940-10-10# then maybe this code just before it writes to the csv file:

'Remember x = #1940-10-10#  Change x to the variable you are using to hold the date
'(I think this will only work if the variable is defined as a string...not a date)

x = left(x, len(x - 1))
x = right(x, len(x - 1))


That will remove the pound signs and make x = to 1940-10-10  but I don't know if it will write to the csv file like you want it or not.


Another idea would be to write an excel macro to do it for you which would be very simple, but would require an extra step on your part.

Just a thought...

Cheers!
ahammar
0
 
LVL 9

Expert Comment

by:tkalchev
Comment Utility
What about using OLE Automation to connect to Excel and not use intermediate .csv file ?
If this is a solution for you please let me know and I will provide you an answer.
0
 

Author Comment

by:Sara_j_11
Comment Utility
yes OLE automation to connect to Excel and not use intermediate .csv file will be a great solution for me in the long run.

0
 
LVL 9

Expert Comment

by:tkalchev
Comment Utility
Or even something better :

dim xlConn as new adodb.connection
dim xlRecordset as new adodb.recordset
Dim strConnStr As String
Dim strExcelFileName as string
Dim oExcel As Object
Dim oBook As Object
Dim oSheet As Object

'insert your Oracle select code here and fill the object rstRecordset

strExcelFileName = sFolderLoc & "\EXCELREPORT_" & Format(Now, "mm""-""dd""-""yyyy") & ".xls"

strConnStr = "Provider=MSDASQL.1;Persist Security Info=False;" & _
    "Extended Properties=""DSN=Excel Files;DBQ=" & strExcelFileName & ";" & _
    "DefaultDir=" & strPath & ";DriverId=790;FIL=excel 8.0;" & _
    "MaxBufferSize=2048;PageTimeout=5;"""

xlConn.ConnectionString = strConnStr

'Create an empty XLS file, containing only one sheet, named "MySheet"
Set oExcel = CreateObject("Excel.Application")
oExcel.DisplayAlerts=false
Set oBook = oExcel.Workbooks.Add
if oBook.Worksheets.Count>1 then
  do
    oBook.Worksheets(1).delete
  loop until oBook.Worksheets.Count=1
end if
oBook.Worksheets(1).Name="MySheet"
dim i as integer
for i=0 to 19
  oBook.Worksheets(1).Cells(1,i+1) = rstRecordSet.Fields(i).Name
next i
oBook.SaveAs sFolderLoc & "\EXCELREPORT_" & Format(Now, "mm""-""dd""-""yyyy") & ".xls", xlWorkbookNormal
oBook.Close
set oBook = nothing
oExcel.quit
set oExcel=nothing

xlConn.open
dim S as string
do while not rstRecordSet.EOF
  xlRecordset.Open "select * from MySheet$", xlConn
  xlRecordset.AddNew
  for i = 0 to 19
    xlRecordset.Fields(i) = rstRecordset.Fields(i)
  next i
  xlRecordset.Update
loop

xlConn.Close
0
 
LVL 8

Expert Comment

by:YoungBonzi
Comment Utility
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")

'------------------------------
   'Format the column (I used column 'a' but change to suit your needs)
    Range("a:a").Select
    Selection.NumberFormat = "m/d/yyyy"
'-------------------------------
           
   
   '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
 

Author Comment

by:Sara_j_11
Comment Utility
youngbonzi Has posted this piece of code,

'Format the column (I used column 'a' but change to suit your needs)
    Range("a:a").Select
    Selection.NumberFormat = "m/d/yyyy"

But can you pl. tell me how to solve the main problem that I have? Let me give an example to clearer.

 There is a column in my final report
Column a
45
67
45
90
#null#
67
I want to be able to take out that null field and just make it appear as a blank field.
Kindly insert code in my code.

expert tkalchev , i dont see the part of your code that handles that..


0
 

Author Comment

by:Sara_j_11
Comment Utility
bruintje   I tried ur code ; but it is not making the change.. I have tried to be clearer this time by giving an example. Experts pl. see my example.
0
 
LVL 8

Accepted Solution

by:
YoungBonzi earned 250 total points
Comment Utility
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")

'------------------------------
   'Format the column (I used column 'a' but change to suit your needs)
    Range("a:a").Select
    Selection.NumberFormat = "m/d/yyyy"
'------------------

'------------------
   'Find the "#null#" value one cell at a time
   rowNumber = 1
   

   Range("d1").CurrentRegion.Select          'stops before blank cells, you can use 'Columns("d").Select' instead... (slower)
   numberOfRows = Selection.Rows.Count  
   
   Do While rowNumber < numberOfRows
      Range("d" & rowNumber).Select
      If ActiveCell.Value = "#null#" Or ActiveCell.Value = "#NULL#" Then
         ActiveCell.ClearContents
      End If
      rowNumber = rowNumber + 1
   Loop

'-------------------------------
           
   
   '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


======================
If you column already has a blank in it check out the comment, there's probably a better way to find the number of rows though.
0
 
LVL 8

Expert Comment

by:YoungBonzi
Comment Utility
whoups... i used column 'd' in the second snippet just so you know!
0
 
LVL 9

Expert Comment

by:tkalchev
Comment Utility
Change

  for i = 0 to 19
    xlRecordset.Fields(i) = rstRecordset.Fields(i)
  next i

to

  for i = 0 to 19
    if IsNull(rstRecordset.Fields(i)) then
      xlRecordset.Fields(i) = ""
    else
      xlRecordset.Fields(i) = rstRecordset.Fields(i)
    end if
  next i

0
 
LVL 9

Expert Comment

by:tkalchev
Comment Utility
Or something more stupid but surely works fine :)

  for i = 0 to 19
    if ("a" & rstRecordset.Fields(i))="a" then
      xlRecordset.Fields(i) = ""
    else
      xlRecordset.Fields(i) = rstRecordset.Fields(i)
    end if
  next i
0
 

Author Comment

by:Sara_j_11
Comment Utility
expert tkalchev , I am sorry if i am asking a stupid question,
but if I use the code that u had posted(which does the pasting of column) names etc in very few lines of code as opposed to the code that I had.. what about the connection to the oracle database to exceute the query ? Your connection string looks different.. Can u please paste ur code in my code so that I can understand more clearly. Also can u please include comments for the foll. lines of code...
strConnStr = "Provider=MSDASQL.1;Persist Security Info=False;" & _
    "Extended Properties=""DSN=Excel Files;DBQ=" & strExcelFileName & ";" & _
    "DefaultDir=" & strPath & ";DriverId=790;FIL=excel 8.0;" & _
    "MaxBufferSize=2048;PageTimeout=5;"""

xlConn.ConnectionString = strConnStr

xlRecordset.Open "select * from MySheet$", xlConn

Thanks in advance. I think we are getting there!
0
 

Author Comment

by:Sara_j_11
Comment Utility
when u say 'insert your Oracle select code here and fill the object rstRecordset, are you saying to use the connection string that I have in my code as below and then execute query and then
rstRecordSet.Open sSQL, conn, adOpenForwardOnly
   

     sFileName = sFolderLoc & "\ExcelReport.csv" ??


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"
0
 
LVL 9

Expert Comment

by:tkalchev
Comment Utility
Yes, I mean exactly this code :


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
   
0
 
LVL 9

Expert Comment

by:tkalchev
Comment Utility
The connection string

 "Provider=MSDASQL.1;Persist Security Info=False;" & _
    "Extended Properties=""DSN=Excel Files;DBQ=" & strExcelFileName & ";" & _
    "DefaultDir=" & strPath & ";DriverId=790;FIL=excel 8.0;" & _
    "MaxBufferSize=2048;PageTimeout=5;"""

is for connecting to the Excel via ODBC
0
 

Author Comment

by:Sara_j_11
Comment Utility
Dear expert Young Bonzi!!
I just tried compiled ur code today. I had to make one change
Do While rowNumber <= numberOfRows (leaaser than or equal to)
But it worked great.
But I have several columns which contain null values (48 columns - total ),
so if I keep repeating these steps maually ; it is laborious... Can u please come up with a loop or something to handle this ?


Range("l1").CurrentRegion.Select          'stops before blank cells, you can use 'Columns("d").Select' instead... (slower)
   numberOfRows = Selection.Rows.Count

   Do While rowNumber <= numberOfRows
      Range("l" & rowNumber).Select
      If ActiveCell.Value = "#null#" Or ActiveCell.Value = "#NULL#" Then
         ActiveCell.Value = ""
      End If
      rowNumber = rowNumber + 1
   Loop
   
   rowNumber = 1


   Range("o1").CurrentRegion.Select          'stops before blank cells, you can use 'Columns("d").Select' instead... (slower)
   numberOfRows = Selection.Rows.Count

   Do While rowNumber <= numberOfRows
      Range("o" & rowNumber).Select
      If ActiveCell.Value = "#null#" Or ActiveCell.Value = "#NULL#" Then
         ActiveCell.Value = ""
      End If
      rowNumber = rowNumber + 1
   Loop
   rowNumber = 1


   Range("p1").CurrentRegion.Select          'stops before blank cells, you can use 'Columns("d").Select' instead... (slower)
   numberOfRows = Selection.Rows.Count

   Do While rowNumber <= numberOfRows
      Range("p" & rowNumber).Select
      If ActiveCell.Value = "#null#" Or ActiveCell.Value = "#NULL#" Then
         ActiveCell.Value = ""
      End If
      rowNumber = rowNumber + 1
   Loop
   rowNumber = 1


0
 

Author Comment

by:Sara_j_11
Comment Utility
tkalchev i am yet to try ur automation method ; i will try it and post if I have any problems.
Thanks expert tkalchev!!
0
 

Author Comment

by:Sara_j_11
Comment Utility
dear expert tkalchav,
I just tried  ur code, I put a breakpoint in the line:
xlConn.Open strConnStr
When it gets to this point it gives me an error saying:
Microsoft ODBC Driver Manager)  Data source Name - not found and no default driver specified.
Could you pl. tell me what this error is and how to rectify it?
Thanks!!
0
 

Author Comment

by:Sara_j_11
Comment Utility
Experts Young Bonzi and tkalchev , Please see my comments above ; I think I  am getting closer to ending this and awarding points.. Pl. reply to my comments.
0
 
LVL 8

Expert Comment

by:YoungBonzi
Comment Utility
Hi Sara, try this loop. There is probably a way to find the last column but for now change the "Do While x < 5" to whatever suits your needs.
==================

x = 0

Do While x < 26
varChar = Chr(Asc("A") + x)

rowNumber = 1
   
   Range(varChar & "1").CurrentRegion.Select    
   numberOfRows = Selection.Rows.Count
   
   Do While rowNumber < numberOfRows
      Range(varChar & rowNumber).Select
      If ActiveCell.Value = "#null#" Or ActiveCell.Value = "#NULL#" Then
         ActiveCell.ClearContents
      End If
      rowNumber = rowNumber + 1
   Loop
x = x + 1
Loop

=================
Happy testing!
0
 
LVL 9

Expert Comment

by:tkalchev
Comment Utility
Ok, it seems that you don't have a DSN, called "Excel Files". Normally it is autogenerated when you install the MS Office. What is the language of your operating system? If it is not an English one, then probably the DSN name is different. For example in the German Windowses it is called "Excel-Dateien". Please chesk in your ODBC configurator what is the exact name of the DSN, it is in the section "User DSN" and should be something similar.
0
 

Author Comment

by:Sara_j_11
Comment Utility
hello expert kalchev!!!
I added the excel driver (user DSN - Microsoft excel driver.xls)
So it exceutes that line . It also exeutes xlConn.Open, but when it reaches the xlRecordset.Open "select * from MySheet$", xlConn
it gives an error saying that
(Microsoft ODBC Excel Driver )    Syntax  Error in the from Clause.
Thanks for you support!!

0
 
LVL 9

Expert Comment

by:tkalchev
Comment Utility
What Excel version do you have? This works fine in my place with Excel XP.
Try to replace

select * from MySheet$

with

select * from [MySheet$];
0
 

Author Comment

by:Sara_j_11
Comment Utility
I tried that , exceution passes that statement : xlRecordset.Open "select * from [MySheet$]", xlConn
But on the next statement : xlRecordset.AddNew , I get the following error:
Current Recordset does not support updating. This may be a limitation of the provider , or of the selected locktype... How do I handle this...
0
 

Author Comment

by:Sara_j_11
Comment Utility
excel version is 97, but there are some machines that have 2000 ; so it needs to be compatible with  2000 too..
0
Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

 

Author Comment

by:Sara_j_11
Comment Utility
should I do something like this: 'oColumn.Locked = False,
If yes can u pl. give me the correct details, tkalchev?
0
 
LVL 9

Expert Comment

by:tkalchev
Comment Utility
Hello!

Try this :

 xlRecordset.Open "select * from [MySheet$]", xlConn, adOpenDynamic, adLockOptimistic
0
 

Author Comment

by:Sara_j_11
Comment Utility
Ok when I tried that it executes that statement , but when it comes to the point  -- xlRecordset.Update, it gives me an error saing that 'operation must use an updateable query'.
This is last step. I think if this works, then it will be successful, since this is the last step of the program..
0
 
LVL 9

Expert Comment

by:tkalchev
Comment Utility
Hm, very strange, probably this is a limitation of Excel 97 not to allow bidirectional querys. In the moment I don't have Excel 97 installed on my machine to test how it works exactly ..
I think we must change the statements a little bit. Try the following code :

Instead of this code in my first post :
xlConn.open
dim S as string
do while not rstRecordSet.EOF
  xlRecordset.Open "select * from MySheet$", xlConn
  xlRecordset.AddNew
  for i = 0 to 19
    xlRecordset.Fields(i) = rstRecordset.Fields(i)
  next i
  xlRecordset.Update
loop

Put this code :
xlConn.Open
dim strUpdateSQL as string
do while not rstRecordSet.EOF
  strUpdateSQL = "insert into [MySheet$] values ("
  for i=1 to 18
    if ("a" & rstRecordset.Fields(i))="a" then
      strUpdateSQL = strUpdateSQL & " '', "
    else
      strUpdateSQL = strUpdateSQL & rstRecordset.Fields(i) & ","
    end if
  next i
  xlRecordset.Open strUpdateSQL,xlConn
loop
0
 
LVL 9

Expert Comment

by:tkalchev
Comment Utility
Ops, sorry, read the last piece of code like this :

xlConn.Open
dim strUpdateSQL as string
do while not rstRecordSet.EOF
  strUpdateSQL = "insert into [MySheet$] values ("
  for i=1 to 19
    if ("a" & rstRecordset.Fields(i))="a" then
      strUpdateSQL = strUpdateSQL & " '', "
    else
      strUpdateSQL = strUpdateSQL & rstRecordset.Fields(i) & ","
    end if
  next i
  strUpdateSQL = left(strUpdateSQL,len(strUpdateSQL)-1) & ")"
  xlRecordset.Open strUpdateSQL,xlConn
loop
0
 

Author Comment

by:Sara_j_11
Comment Utility
tkalchev, I have some fields with have spaces in them like 'BC OF DALLAS' ; i THINK IT DOES NOT LIKE THE SPACE IN BETWEEN. when it gets to this line:xlRecordset.Open strUpdateSQL, xlConn ; it gives an error Syntax error(missing operator) in query expression BC OF DALLAS.
hOW do I fix this. Also could you please suggest some good reading material from internet to understand background of you code. automation
0
 
LVL 9

Expert Comment

by:tkalchev
Comment Utility
Yes, you are right, the correct code should be :

xlConn.Open
dim strUpdateSQL as string
do while not rstRecordSet.EOF
  strUpdateSQL = "insert into [MySheet$] values ("
  for i=1 to 19
    if ("a" & rstRecordset.Fields(i))="a" then
      strUpdateSQL = strUpdateSQL & " '',"
    else
      strUpdateSQL = strUpdateSQL & "'" & rstRecordset.Fields(i) & "',"
    end if
  next i
  strUpdateSQL = left(strUpdateSQL,len(strUpdateSQL)-1) & ")"
  xlRecordset.Open strUpdateSQL,xlConn
loop


In the moment i have neither vb nor excel installed on the pc, so i cannot test it, i'm sorry for this :)
0
 

Author Comment

by:Sara_j_11
Comment Utility
there is something diffeerent about the way this part of the code works:
xlConn.Open
i = 0
Dim strUpdateSQL As String
Do While Not rstRecordSet.EOF
  strUpdateSQL = "insert into [MySheet$] values ("
  For i = 0 To 19
    If ("a" & rstRecordSet.Fields(i)) = "a" Then
   
      strUpdateSQL = strUpdateSQL & " '', "
    ElseIf ("a" & rstRecordSet.Fields(i)) = 0 Then
      strUpdateSQL = strUpdateSQL & " '0', "
    Else
      strUpdateSQL = strUpdateSQL & rstRecordSet.Fields(i) & ","
    End If
  Next i
  strUpdateSQL = Left(strUpdateSQL, Len(strUpdateSQL) - 1) & ")"
  xlRecordset.Open strUpdateSQL, xlConn
Loop

When it gets to this point: xlRecordset.Open strUpdateSQL, xlConn
it throws an error: I have one column in the report which has a special character eg-- AOL/FGH . I dont know if it does not like the " / " . It says, syntax error(missing operator) in query expression 'AOL/FGH'
0
 

Author Comment

by:Sara_j_11
Comment Utility
Also can u pl. let me what this line of code does?
strUpdateSQL = Left(strUpdateSQL, Len(strUpdateSQL) - 1) & ")"
why not just  strUpdateSQL = Len(strUpdateSQL) - 1 & ")"?
Also pl. suggest some good reading material for excel automation. so that I canmmake some enhancements if neccessary...
0
 

Author Comment

by:Sara_j_11
Comment Utility
Oops sorry just disregard the question that I had sent about the left function. I understand why you did that.
0
 
LVL 9

Expert Comment

by:tkalchev
Comment Utility
Remove the declaration of xlRecordset and add
dim xlCommand as ADODB.Command
instead


and then change the upper code to :

xlConn.Open
i = 0
Dim strUpdateSQL As String
Do While Not rstRecordSet.EOF
  strUpdateSQL = "insert into [MySheet$] values ("
  for i=0 to 18
    strUpdateSQL = strUpdateSQL & "?,"
  next
  strUpdateSQL = strUpdateSQL & "?)"
  set xlCommand = new adodb.command
  xlCommand.Activeconnection=conn
  xlCommand.CommandText = strUpdateSQL
  For i = 0 To 19
    If ("a" & rstRecordSet.Fields(i)) = "a" Then
      xlCommand.Parameters.Append xlCommand.CreateParameter (,200,adParamInput,1," ")
    Else
      xlCommand.Parameters.Append xlCommand.CreateParameter (,200,len(rstRecordSet.Fields(i)),rstRecordSet.Fields(i))
    End If
  Next i
  xlCommand.Execute
Loop
0
 
LVL 9

Assisted Solution

by:tkalchev
tkalchev earned 250 total points
Comment Utility
Read tha last piece of code as :

xlConn.Open
i = 0
Dim strUpdateSQL As String
Do While Not rstRecordSet.EOF
  strUpdateSQL = "insert into [MySheet$] values ("
  for i=0 to 18
    strUpdateSQL = strUpdateSQL & "?,"
  next
  strUpdateSQL = strUpdateSQL & "?)"
  set xlCommand = new adodb.command
  xlCommand.Activeconnection=conn
  xlCommand.CommandText = strUpdateSQL
  For i = 0 To 19
    If ("a" & rstRecordSet.Fields(i)) = "a" Then
      xlCommand.Parameters.Append xlCommand.CreateParameter (,200,adParamInput,1," ")
    Else
      xlCommand.Parameters.Append xlCommand.CreateParameter (,200,adParamInput,len(rstRecordSet.Fields(i)),rstRecordSet.Fields(i))
    End If
  Next i
  xlCommand.Execute
Loop
0
 

Author Comment

by:Sara_j_11
Comment Utility
 xlCommand.CommandText  error:method or datamember not found..
0
 
LVL 9

Expert Comment

by:tkalchev
Comment Utility
Did you made this

Remove the declaration of xlRecordset and add
dim xlCommand as ADODB.Command
instead
0
 

Author Comment

by:Sara_j_11
Comment Utility
expert YoungBonzi, ur code works great. But it does not cange the #12/12/2004#  to  12/12/2004. In other words , this piece of code does not handle this...
Range("e:e").Select
    Selection.NumberFormat = "m/d/yyyy"
0
 

Author Comment

by:Sara_j_11
Comment Utility
Yes that helped , but it still goes through the loop and then throws an erorr :
Item cannot be found in the collection corresponding to the requested name or ordinal, it looks like it still iterates when i = 20 and then since there is no column at that point (since there are only 20 columns and i=20 is literally looking for a 21st column ....
0
 
LVL 9

Expert Comment

by:tkalchev
Comment Utility
The loop is

For i = 0 To 19


0
 

Author Comment

by:Sara_j_11
Comment Utility
sorry I dont think the problem is with the loop .. I am getting an error at the xlCommand.Execute line. 'No value given for one or more required parameters.'
0
 
LVL 9

Expert Comment

by:tkalchev
Comment Utility
Declare the following :

dim Ra,Pr,O

and then pass these 3 arguments to the xlCommand.Execute statement :
xlCommand.Execute Ra,Pr,O


0
 

Author Comment

by:Sara_j_11
Comment Utility
I tried that ... does not help..pl. let me know if this is right..
For i = 0 To 18
    If ("a" & rstRecordSet.Fields(i)) = "a" Then
    xlCommand.Parameters.Append xlCommand.CreateParameter(, 200, adParamInput, 1, " ")
    ElseIf ("a" & rstRecordSet.Fields(i)) = "a0" Then
   xlCommand.Parameters.Append xlCommand.CreateParameter(, 200, adParamInput, 1, "0")
    Else
    xlCommand.Parameters.Append xlCommand.CreateParameter(, 200, adParamInput, Len(rstRecordSet.Fields(i)), rstRecordSet.Fields(i))
    End If

Next i
Dim Ra, Pr, O
xlCommand.Execute Ra, Pr, O

I basically used the same code that you had sent . Loop seems to be fine , but when it gets to the xlCommand.Execute Ra, Pr, O, it throws the same error


0
 
LVL 9

Expert Comment

by:tkalchev
Comment Utility
What is your version of ADO ?
0
 

Author Comment

by:Sara_j_11
Comment Utility
Could you pl. tell me how to find that, please?
0
 
LVL 9

Expert Comment

by:tkalchev
Comment Utility
When you add a referene of ADO to your VB project, there is standing

"Microsoft ActiveX Data Objects XXX"

This XXX in the end is the version
0
 

Author Comment

by:Sara_j_11
Comment Utility
It is 2.6 library..
0
 
LVL 8

Expert Comment

by:YoungBonzi
Comment Utility
are you still trying to solve this? i can fix the vb code if this is still an active thread.
0
 

Author Comment

by:Sara_j_11
Comment Utility
Yes I am still trying to solve this...It is an active thread
0
 

Author Comment

by:Sara_j_11
Comment Utility
I am still trying to solve this based on tkalchev's answer of automation method.. I can do it using loops in vb code but tkalchevs method is so good that I want to be able to learn to solve the problem using that method. Could you help.. please..
0

Featured Post

Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

Join & Write a Comment

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…
Enums (shorthand for ‘enumerations’) are not often used by programmers but they can be quite valuable when they are.  What are they? An Enum is just a type of variable like a string or an Integer, but in this case one that you create that contains…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…

772 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