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
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
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
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
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
[RESOLVED] input info from excel to a database
Reply With Quote
