misterewan
02-13-2008, 06:33 AM
Good day guys, i want to convert my excel workbook to access 2003 database. ive tried to link and import it but some fields doesn't retain their format (e.g. numbers in text format turns to number format when converted to access)..i dunno what to do now please help me..i will attach one of my files to be converted to access database..thanks in advance guys!
misterewan
02-14-2008, 01:35 AM
i've found a vba code where the first spreadsheet on a certain cell be transfered to access database and here it is:
Sub AddData()
'Original coding from XLD
Dim oConn As Object
Dim oRS As Object
Dim sSQL As String
Set oConn = CreateObject("ADODB.Connection")
oConn.Open = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & ActiveWorkbook.Path & "\cwa01.mdb"
sSQL = "INSERT INTO cwa01 (DWGNo, SheetNo, Revision, CWA, SubArea, Material, DiaInInch, Schedule, QTY, MM1, MM2, MM3, MM4, MM5, MM6, MM7, CCODE, MTYPE, AREA, UNITMHRS, MHRS, DISCODE) " & _
"VALUES ('" & [A2] & "', '" & [B2] & "', '" & [C2] & "','" & [D2] & "','" & [E2] & "','" & [F2] & "','" & [G2] & "','" & [H2] & "','" & [I2] & "','" & [Q2] & "','" & [R2] & "','" & [S2] & "','" & [T2] & "','" & [U2] & "','" & [V2] & "','" & [W2] & "','" & [X2] & "','" & [Y2] & "','" & [Z2] & "','" & [AA2] & "','" & [AB2] & "','" & [AC2] & "')"
oConn.Execute sSQL
oConn.Close
Set oConn = Nothing
End Sub
now the only problem is i want to insert all datas in all the spreadsheet lets say in sheet1,there are 1000 rows that i want to transfer to database and the next sheet,there 800 and so on. plz kindly help me with this,it will cost my work if i dont..: pray2: :banghead: : pray2:
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.