import excel sheet to access VBA

  • Thread starter Thread starter chrrif
  • Start date Start date
C

chrrif

I add data to a table of access by reading an excel sheet.
I have to add a record to the table.
Sub readexcelsheet()
Dim db As Database
Dim rsOrder As Recordset
Dim strDirName As String
Dim path As String

Const NO_FILES_IN_DIR As Long = 9
Const INVALID_DIR As Long = 13
'On Error GoTo readexcelsheetPartition_err

Set db = CurrentDb
'assign a path
strDirName = "c:\Approval\2006\partition\"
'strDirName = "\\Cogserver1\Engineering\Approval\2006\partition\"
' strDirName = CurDir & "\Approval\2006\partition\"
'create a list
list = GetAllFilesInDir(strDirName)
'variable to display
k = 0

'parcourir la liste de fichier
For i = 0 To UBound(list)
order1 = list(i)
' order1 = RTrim(order1, 4)
path = strDirName + order1
Size = Len(order1) - 4
extension = LCase(Right(order1, 4))
If StrComp(extension, ".xls") = 0 Then
' extract the order number
order1 = Left(order1, Size)
'check if the work order number exists
If checkifWOIDExistsInworkorder(order1) = False Then
'check if the order sheet exists
If (DoesExcelSheetExist("OrderSheet", path)) Then
'MsgBox "sheet exists"
''Debug.Print order1
'Set rsOrder = db.OpenRecordset("select * from
[Order] where OrderId='" & order1 & "'")
'rsorder.MoveLast
k = 1
Form_ReadWorkOrders.Lst_ReadWorkOrders.AddItem
(order1)
DoCmd.TransferSpreadsheet acImport,
acSpreadsheetTypeExcel8, "workorder", strDirName & order1 & ".xls",
True, "OrderSheet!A1:AW2"
Call connectionupdateMySQL
Else
MsgBox "sheet doesn't exists"
End If
End If
End If

Next i
If k = 1 Then
Form_ReadWorkOrders.Lst_ReadWorkOrders.AddItem ("Read
Orders")
ElseIf k = 0 Then
Form_ReadWorkOrders.Lst_ReadWorkOrders.AddItem ("No Order")
End If
End Sub

I have to tranfer the data from excel sheet of more than one excel
file.
the access database file size in growing "bloating" from 30 mg to 500
MG.

I'm looking for another way to import the record on an excel sheet.
 
Bloating is regrettably common when working with Access. Be sure to
compact-and-repair the database regularly.

The procedure you've posted could be simplified, but I don't see
anything in it that might particularly exacerbate the tendency to bloat.
Do any of the functions and subroutines it calls do things that tend to
increase bloat (e.g. create temporary tables, create and delete database
objects)?

I add data to a table of access by reading an excel sheet.
I have to add a record to the table.
Sub readexcelsheet()
Dim db As Database
Dim rsOrder As Recordset
Dim strDirName As String
Dim path As String

Const NO_FILES_IN_DIR As Long = 9
Const INVALID_DIR As Long = 13
'On Error GoTo readexcelsheetPartition_err

Set db = CurrentDb
'assign a path
strDirName = "c:\Approval\2006\partition\"
'strDirName = "\\Cogserver1\Engineering\Approval\2006\partition\"
' strDirName = CurDir & "\Approval\2006\partition\"
'create a list
list = GetAllFilesInDir(strDirName)
'variable to display
k = 0

'parcourir la liste de fichier
For i = 0 To UBound(list)
order1 = list(i)
' order1 = RTrim(order1, 4)
path = strDirName + order1
Size = Len(order1) - 4
extension = LCase(Right(order1, 4))
If StrComp(extension, ".xls") = 0 Then
' extract the order number
order1 = Left(order1, Size)
'check if the work order number exists
If checkifWOIDExistsInworkorder(order1) = False Then
'check if the order sheet exists
If (DoesExcelSheetExist("OrderSheet", path)) Then
'MsgBox "sheet exists"
''Debug.Print order1
'Set rsOrder = db.OpenRecordset("select * from
[Order] where OrderId='" & order1 & "'")
'rsorder.MoveLast
k = 1
Form_ReadWorkOrders.Lst_ReadWorkOrders.AddItem
(order1)
DoCmd.TransferSpreadsheet acImport,
acSpreadsheetTypeExcel8, "workorder", strDirName & order1 & ".xls",
True, "OrderSheet!A1:AW2"
Call connectionupdateMySQL
Else
MsgBox "sheet doesn't exists"
End If
End If
End If

Next i
If k = 1 Then
Form_ReadWorkOrders.Lst_ReadWorkOrders.AddItem ("Read
Orders")
ElseIf k = 0 Then
Form_ReadWorkOrders.Lst_ReadWorkOrders.AddItem ("No Order")
End If
End Sub

I have to tranfer the data from excel sheet of more than one excel
file.
the access database file size in growing "bloating" from 30 mg to 500
MG.

I'm looking for another way to import the record on an excel sheet.
Click to expand...
 
Back
Top