[RESOLVED] input info from excel to a database

hi
i have this code which reads in my excel file

Code:

---

Dim xl As New Excel.Application

    Dim xlsheet As Excel.Worksheet

    Dim xlwbook As Excel.Workbook

 

  ' Const SHEET_NAME As String = "CreditRatings"

    Dim i As Integer

    Dim j As Integer

    Dim sBuffer As String

    Dim LastRowColB As Integer

    Dim fn As Integer

   

   

 

    Set xlwbook = xl.Workbooks.Open("c:\CreditRatingsReport.xls")

  Set xlsheet = xlwbook.Sheets.Item("CreditRatings")

 

    LastRowColB = xl.Range("B65536").End(xlUp).row

    fn = FreeFile

    'Open sOUTPUT_PATH & "\Ivory_SecuritiesIssued" & ".csv" For Append As #fn

    For i = 2 To LastRowColB

        sBuffer = ""

        For j = 2 To 24

 

        If IsDate(xlsheet.Cells(i, j)) Then

            sBuffer = sBuffer & Format(xlsheet.Cells(i, j)) & "|"

        Else

            sBuffer = sBuffer & Trim(xlsheet.Cells(i, j)) & "|"

        End If

           

        Next j

        Print #fn, Left(sBuffer, Len(sBuffer) - 1)

    Next i

 

    'Cleanup

    Set xlsheet = Nothing

    If Not xl.ActiveWorkbook Is Nothing Then xl.ActiveWorkbook.Close

    Set xlwbook = Nothing

    xl.Quit

    Set xl = Nothing

---

what i need to do it add this info to a table in my db. i dont need all the info to go in just certain fields.
any ideas how this is done

see this link

****ing blocked from it at work. what a joke
thanks will see if i can get away with it for day and look at it tonight

hi
this is my code for pulling the info from the excel spreadsheet
is seem to work so happy days

Code:

---

Dim xl As New Excel.Application

    Dim xlsheet As Excel.Worksheet

    Dim xlwbook As Excel.Workbook

 

  ' Const SHEET_NAME As String = "CreditRatings"

    Dim i As Integer

    Dim j As Integer

    Dim sBuffer As String

    Dim LastRowColB As Integer

    Dim fn As Integer


    Set xlwbook = xl.Workbooks.Open("c:\CreditRatingsReport.xls")

  Set xlsheet = xlwbook.Sheets.Item("CreditRatings")



    LastRowColB = xl.Range("B65536").End(xlUp).row

    fn = FreeFile

    'Open sOUTPUT_PATH & "\Ivory_SecuritiesIssued" & ".csv" For Append As #fn

    For i = 2 To LastRowColB

        sBuffer = ""

        For j = 1 To 24



        If IsDate(xlsheet.Cells(i, j)) Then

            sBuffer = sBuffer & Format(xlsheet.Cells(i, j)) & "|"

        Else

            sBuffer = sBuffer & Trim(xlsheet.Cells(i, j)) & "|"

        End If

           

        Next j

      ' MsgBox sBuffer

    Next i

---

now its the next part i cant get i need to put that into the table. but i have to stick to the structure in here so can any one help please.
i need to get to to put the info into the table buy doing it something like this

Code:

---

DBCALL "SELECT", "ACRT", Cmd$ -- calls the table in the database

 
 Cmd$ = "INSERT INTO ACRT (CNO, MoodysRate, SNPRate, FITCHRate, MoodysWatch, snPWatch, FITCHWatch, MoodysIRB) Values )
   
   
 
   
    DBCALL "add", "ACRT", Cmd$ adds the info into tthe table
       
    If result <> dbSUCCESS And result <> 100 Then
                MsgBox "Data Base Unavailable or Unexpected error in Data Base or batch running."
                Message 43
                result = 1
               
    End If

---

its the middle part i can figure out the insert into table part anyone have any ideas

can anyone help really stuck and need to get this done

can anyone help please

can u attach the project as a zip file? and need clear info abt ur exact requirement and problem.

i cant zip the file up sorry work wont allow it.
what i need to do is when i hit the update button its goes to the excel file i point to to loops through it and add that info into the table i have.
now i have the lopping through the file bit done i think well it loops through it anyway.
so that part im stuck on is getting that info to insert into the table.
so im basically stuck on this part

Code:

---

Cmd$ = "INSERT INTO ACRT (CNO, MoodysRate, SNPRate, FITCHRate, MoodysWatch, snPWatch, FITCHWatch, MoodysIRB) Values )

---

but when i insert it i want it to to it automatically through the insert so is an array the best way of doing this

sorry i cant understand with out seeing the data files, u dont need to attach the original files, just save as them with similar kind of datas/purpose, if posible u try.

ok say this is the excel sheet im working of
the fields highlighted in red are the ones i want to put the info out of
and the code is basically this

Code:

---

Private Sub UpdateFunction()
 
  Dim FileSystemObject As Object
Set FileSystemObject = CreateObject("Scripting.FileSystemObject")
 FileSystemObject.CopyFile "c:\CreditRatingsReport.xls", "H:\CreditRatingsReport.xls"
 
 Dim xl As New Excel.Application

    Dim xlsheet As Excel.Worksheet

    Dim xlwbook As Excel.Workbook

 

  ' Const SHEET_NAME As String = "CreditRatings"

    Dim i As Integer

    Dim j As Integer

    Dim sBuffer As String

    Dim LastRowColB As Integer

    Dim fn As Integer



    Set xlwbook = xl.Workbooks.Open("c:\CreditRatingsReport.xls")

  Set xlsheet = xlwbook.Sheets.Item("CreditRatings")



    LastRowColB = xl.Range("B65536").End(xlUp).row

    fn = FreeFile

    'Open sOUTPUT_PATH & "\Ivory_SecuritiesIssued" & ".csv" For Append As #fn

    For i = 2 To LastRowColB

        sBuffer = ""

        For j = 1 To 24



        If IsDate(xlsheet.Cells(i, j)) Then

            sBuffer = sBuffer & Format(xlsheet.Cells(i, j)) & "|"

        Else

            sBuffer = sBuffer & Trim(xlsheet.Cells(i, j)) & "|"

        End If

           

        Next j

      ' MsgBox sBuffer

    Next i
   
 
    DBCALL "SELECT", "ACRT", Cmd$

 
 Cmd$ = "INSERT INTO ACRT (CNO, MoodysRate, SNPRate, FITCHRate, MoodysWatch, snPWatch, FITCHWatch, MoodysIRB) Values (EUROCLEAR BANK)"
  ' Cmd$ = Cmd$ + "'" + sBuffer + "',"+ CRLF$")))
   
   
 
   
  '  DBCALL "SELECT", "ACRT", Cmd$
       
  '  If result <> dbSUCCESS And result <> 100 Then
  '            MsgBox "Data Base Unavailable or Unexpected error in Data Base or batch running."
  '            Message 43
  '            result = 1
               
  ' End If
               

    'Cleanup

    Set xlsheet = Nothing

    If Not xl.ActiveWorkbook Is Nothing Then xl.ActiveWorkbook.Close

    Set xlwbook = Nothing

    xl.Quit

    Set xl = Nothing

       

    result = 0


 

   

 

   
End Sub

---

i asked u to attach with the project files / mdb files also.

hi
i cant get the db file as i only have a way to look at it. dont have access to it to take a copy of the table.
i can send the project file but it wont work on ur pc as its a weird system we work of. it was built by some one else and we dont have source code to it. but we can add new parts to it like im doing. so all that u will be abel to see and use is the code i posted

here is code file im working off

here is the code i was giving from the person who set up the table for me. not sure if this will help

check the attached project, it copy the 4 column from excel to access DB

thanks will have a look.

went with it this way seems to work

Code:

---

Private Sub UpdateFunction()
 
 ' Dim FileSystemObject As Object
'Set FileSystemObject = CreateObject("Scripting.FileSystemObject")
' FileSystemObject.CopyFile "c:\CreditRatingsReport.xls", "H:\CreditRatingsReport.xls"
 
 Dim xl As New Excel.Application

    Dim xlsheet As Excel.Worksheet

    Dim xlwbook As Excel.Workbook
 

  ' Const SHEET_NAME As String = "CreditRatings"

    Dim i As Integer

    Dim j As Integer

    Dim sBuffer As String

    Dim LastRowColB As Integer

    Dim fn As Integer
'Call DeleteFunction
 


 Set xlwbook = xl.Workbooks.Open("c:\CreditRatingsReport.xls")

  Set xlsheet = xlwbook.Sheets.Item("CreditRatings")



    LastRowColB = xl.Range("B65536").End(xlUp).row

    fn = FreeFile

    'Open sOUTPUT_PATH & "\Ivory_SecuritiesIssued" & ".csv" For Append As #fn

    For i = 2 To LastRowColB

        sBuffer = ""
       
       
  Cmd$ = "INSERT INTO ACRT (CNO  ,MOODYSRATE ,SNPRATE  ,FITCHRATE ,MOODYSWATCH  ,SPWATCH ,FITCHWATCH    ) Values ("
 '

'MOODYSIRB    ,FITCHIRB        , SPIRB
 
      ' For j = 1 To 24

Cmd$ = Cmd$ & "'" & xlsheet.Cells(i, 1) & "',"

Cmd$ = Cmd$ & "'" & xlsheet.Cells(i, 4) & "',"
Cmd$ = Cmd$ & "'" & xlsheet.Cells(i, 9) & "',"
Cmd$ = Cmd$ & "'" & xlsheet.Cells(i, 14) & "',"
Cmd$ = Cmd$ & "'" & xlsheet.Cells(i, 21) & "',"
Cmd$ = Cmd$ & "'" & xlsheet.Cells(i, 22) & "',"
Cmd$ = Cmd$ & "'" & xlsheet.Cells(i, 23) & "'"
Cmd$ = Cmd$ & ")"

        'If IsDate(xlsheet.Cells(i, j)) Then

        '  sBuffer = sBuffer & Format(xlsheet.Cells(i, j)) & "|"

        'Else

        '  sBuffer = sBuffer & Trim(xlsheet.Cells(i, j)) & "|"

        'End If

           

      '  Next j

      ' MsgBox sBuffer

    Next i
   
    DBCALL "select", "ACRT", Cmd$

 
 'Cmd$ = "INSERT INTO ACRT (CNO, MoodysRate) Values (BankNAme,MoodysRAte)"
 
  ' Cmd$ = "INSERT INTO ACRT (CNO, MoodysRate) Values (@BankNAme,@MoodysRAte)"
   
   

 
 
   
 
       
  If result <> dbSUCCESS And result <> 100 Then
              MsgBox "Data Base Unavailable or Unexpected error in Data Base or batch running."
              Message 43
              result = 1
               
    End If
               

    'Cleanup

   
   

       

   

End Sub

---

thanks for help