PDA

View Full Version : Solved: Excel to Access



Slicemahn
03-01-2007, 12:34 PM
Hello everyone!

Currently I have a spreadsheet in which I need to load data within a tanle holding 5 fields. I have attached the spreadsheet with names of the database fields. I don't know what would be better or aeasier in terms of uploading to the datanase: a function or a subprocedure. The purpose of the VBA script is to go through the entire spreadsheet and enter only cells where there are values into the database. The total columns need not to be included. Here's my code for the preliminary workings of the spreadsheet:

Sub Preliminary()
Dim initEndCol As Integer
Dim workEndCol As Integer
Dim EndRow As Integer
Dim initRow As Integer
Dim workRow As Integer
Dim c As Integer
Dim g As Integer
initRow = Cells(1, 1).End(xlDown).Row - 1
EndCol = Cells(1, 3).End(xlToRight).Column
EndRow = Cells(65536, 1).End(xlUp).Row
For c = initRow To EndCol
If Cells(initRow, c) <> "Cnt" Then
Columns(c).Delete
End If
Next c
workRow = Cells(1, 1).End(xlDown).Row
workEndCol = Cells(1, 3).End(xlToRight).Column
For g = workRow To EndRow
If CountA.Cells(g, workEndCol) <> 0 Then
Call UpdateDB
End If
workEndCol = workEndCol + 1
Next g

End Sub


UpdateDB is the database tanle I am trying to populate with the values. Your help is welcomed and greatly appreciated

Slice