Stopping an Import program

  • Thread starter BTU_needs_assistance_43
  • Start date
B

BTU_needs_assistance_43

Anybody got any advice on how to write in some code such that when I import
cells from Excel to a table in Access, my program will add every cell
starting with a cell I specify until it comes to one with nothing but 0 in it
and then stops the cell before?
It doesn't matter if it takes the information out one cell at a time or if
it takes them out rows at a time or columns at a time just so long as when it
reads a 0 or #DIV/0! value it doesnt add those cells and stops.
 
B

BTU_needs_assistance_43

Let me amend that. I need the program to take in values from the entire table
starting at the cell i specify, but exclude only the rows that are all 0's
and #DIV/0!'s. The format they send these reports in isn't going to change so
I have to adjust my program to beat this unfortunately.
 
D

Dale_Fye via AccessMonster.com

You have a couple of options,

1. I generally like to create an import table, one that I use to import data
from Excel. I modify the data types of the fields that might come in with
bad data (#Div/0!). Then I import the data into that table. Once you do
that, you can run queries that filter out the records with "bad data" and
import them into the table where you want the "good data".

2. You can use Office Automation. Add a reference to the Microsoft Excel xx.
0 Object Library to your code. Then do something like the following,
probably in the command button on a form.

Public Sub import_Excel()

Dim xl As Excel.Application
Dim wbk As Excel.Workbook
Dim sht As Excel.Worksheet
Dim intRow As Integer
Dim strSQL As String

Set xl = CreateObject("Excel.Application")
xl.Visible = True
Set wbk = xl.Workbooks.Open("C:\Users\dale.fye\Documents\Work\Access\
ImportTest.xlsm", , True)
Set sht = wbk.Sheets(1)

intRow = 2
'loop until the row value is empty
'this will include values where the cell is 0
Do While Len(sht.Cells(intRow, 3) & "") <> 0

If sht.Cells(intRow, 3) <> 0 Then

'you will obviously need to wrap text strings in quotes, but this
'should give you an idea
strSQL = "INSERT INTO Table1 (field1, field2, field3) " _
& "Values (" & Chr$(34) & sht.Cells(intRow, 4) & Chr$(34)
& ", " _
& Chr$(34) & sht.Cells(intRow, 5) & Chr$(34)
& ", " _
& Chr$(34) & sht.Cells(intRow, 7) & Chr$(34)
& ")"
CurrentDb.Execute strSQL
End If

intRow = intRow + 1
Loop

wbk.Close
xl.Quit

End Sub

HTH
Dale
 
Top