-
June 15th, 2012, 06:35 AM
#1
input info from excel to a database
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
-
June 15th, 2012, 08:19 AM
#2
Re: input info from excel to a database
really stuck on this can anyone help
-
June 15th, 2012, 11:58 PM
#3
Re: input info from excel to a database
Well for one thing this statement is missing a lot of required info
Code:
Cmd$ = "INSERT INTO ACRT (CNO, MoodysRate, SNPRate, FITCHRate, MoodysWatch, snPWatch, FITCHWatch, MoodysIRB) Values )
It should be
Code:
Cmd$ = "Insert Into TableName ( Your field names as above) Values ( The actual values for each field listed in the first part)
I have no idea what this line is actually doing
Code:
DBCALL "add", "ACRT", Cmd$ adds the info into tthe table
And here you are checking result but you have not assigned result=anything so this is not goin to work either unless of course result is defined as a public variable and is assigned in the DBCall routine
Code:
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
Always use [code][/code] tags when posting code.
-
June 18th, 2012, 04:02 AM
#4
Re: input info from excel to a database
hi the part im missing alot is really the part im stuck on.
for this part
Code:
It should be
Cmd$ = "Insert Into TableName ( Your field names as above) Values ( The actual values for each field listed in the
i want the values to be entered automatically. its a big long spreadsheet and this will be run on a daily basic and the spreadsheet values will change
is an array the best way to get this done or is there a better way
-
June 18th, 2012, 07:49 AM
#5
Re: input info from excel to a database
The values would be variables that you would read from the spread sheet then they would be inserted into the database. This would be done in a loop so it calls the insert for each new record. Keep in mind Insert is only for adding new records it is not for updating existing records.
Of course if Your goal is to have lots of data entered into a database where people are entering new records all the time the best way would be to drop Excel from the equation and create a vb program for data entry/edited that talks directly to the database.
Always use [code][/code] tags when posting code.
-
June 18th, 2012, 07:59 AM
#6
Re: input info from excel to a database
thanks will give it a go.
info will be deleted from table each time we do an add.
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|