Empty Records - Excell to Access

[adamr99]

I am trying to import data from excell into access. I am importing a small amount of data from a lot of spreadsheets (more than 700).

Unfortunately, what it does, is inserts in the following format:

166 full data records (the correct data too)
79 BLANK records
1 record with data
79 BLANK records
1 record with data
79 BLANK records
1 record with data
79 BLANK records
121 records with data
79 BLANK records
1 record with data
79 BLANK records


there seems to be a pattern with one exception in the middle there...... I have tried about everything to solve this problem.

I am running some VBA code that gets the spreadsheet's URL from another "path" spreadsheet, then opens them up and grabs the data. HELP PLEASE!!

ADAM
HEre is the code:


Option Compare Database
Option Explicit


Private Sub Command0_Click()

Dim objXL As Object
Dim xlWB As Object
Dim xlWS As Object

Dim objXL_path As Object
Dim xlWB_path As Object
Dim xlWS_path As Object
'some objects to refer to Excel

Dim db As DAO.Database
Dim sample_info As DAO.Recordset
Dim species_comp As DAO.Recordset
Dim bio_vol As DAO.Recordset
Dim letter_look As DAO.Recordset
Dim sample_look As DAO.Recordset
Dim genus_look As DAO.Recordset
Dim percent_done As Double
Dim sample_date As Date
Dim Sample_ID As String 'string to hold the sampleid as it is being generated
Dim Job_ID As String
Dim Lake_ID As Integer
Dim i As Integer
Dim j As Integer
Dim data_file As String
Dim data_sheet As String


Set db = CurrentDb
Set sample_info = db.OpenRecordset("TEST_Sample_Info_FINAL&quot 'open our recordsets
Set letter_look = db.OpenRecordset("TEST_Letter_Lookup&quot
Set sample_look = db.OpenRecordset("Sample_ID_Master&quot
Set genus_look = db.OpenRecordset("Genus_lookup&quot



Set objXL_path = CreateObject("Excel.Application&quot
Set xlWB_path = objXL_path.workbooks.Open("C:\!SECLIMNO_USER\Adam\path.xls&quot 'THE PATH SPREADSHEE FOR THE OTHERS
Set xlWS_path = xlWB_path.worksheets("Sheet1&quot

j = 1
With xlWS_path

data_file = (.range("A" + CStr(j)))
data_sheet = (.range("B" + CStr(j)))

End With

Do While (data_file <> &quot;&quot

Me.Text6.SetFocus
Me.Text6.Text = data_file
Me.Text8.SetFocus
Me.Text8.Text = data_sheet


Set objXL = CreateObject(&quot;Excel.Application&quot
Set xlWB = objXL.workbooks.Open(data_file)
Set xlWS = xlWB.worksheets(data_sheet)
'set references to our excel worksheet
Me.Text1.SetFocus
Me.Text1.Text = &quot;&quot;
Me.Text3.SetFocus
Me.Text3.Text = &quot;&quot;


'this is the manipulation of the spreadsheet for the Sample_Information_Final table
With xlWS

'*******************fill sample_info************************************
sample_info.AddNew 'create the new record in sample_info_final
sample_info.Update
sample_info.Edit
sample_info.Update

sample_info.MoveLast 'edit the newly created record
sample_info.Edit

sample_date = .range(&quot;B2&quot
Lake_ID = .range(&quot;B1&quot

sample_look.MoveFirst

Dim isnotfound As Boolean
isnotfound = True

While (isnotfound)
If (sample_date = sample_look.Fields(4)) Then
If (Lake_ID = CInt(sample_look.Fields(1))) Then
isnotfound = False

Else
sample_look.MoveNext
End If


Else
sample_look.MoveNext
End If

Wend



Sample_ID = sample_look.Fields(0)
Job_ID = &quot;PHYTO&quot; + Sample_ID
.range(&quot;K109&quot = &quot;Cyanophycae&quot;
.range(&quot;K124&quot = &quot;Dinobryon&quot;
xlWB.Save
sample_info.Fields(1) = Job_ID 'fill sample_information_FINAL
sample_info.Fields(2) = Sample_ID
sample_info.Fields(3) = (.range(&quot;C2&quot * 1000)
sample_info.Fields(4) = .range(&quot;D2&quot
sample_info.Fields(5) = .range(&quot;E2&quot
sample_info.Fields(6) = .range(&quot;N106&quot
sample_info.Fields(7) = 0
sample_info.Fields(8) = &quot;416822-1&quot;
sample_info.Fields(9) = 200
sample_info.Fields(10) = &quot;XX&quot;
sample_info.Fields(11) = &quot;XX&quot;
'field 11 is automatically set to NO

sample_info.Update




End With
percent_done = 0
j = j + 1
With xlWS_path
data_file = .range(&quot;A&quot; + CStr(j))
data_sheet = .range(&quot;B&quot; + CStr(j))

End With




'*******************close the files************************************


xlWB.Close
objXL.Quit


Set xlWS = Nothing
Set xlWB = Nothing
Set objXL = Nothing

Loop

xlWB_path.Close
objXL_path.Quit


Set xlWS_path = Nothing
Set xlWB_path = Nothing
Set objXL_path = Nothing

sample_info.Close

letter_look.Close
sample_look.Close
genus_look.Close


Set sample_info = Nothing
Set species_comp = Nothing
Set bio_vol = Nothing
Set letter_look = Nothing
Set sample_look = Nothing
Set genus_look = Nothing

Set db = Nothing
'tidy up time


MsgBox (&quot;Successful Run&quot


End Sub

 

[VicM]

Adam:

Because I'm not yet fully awake, I haven't delved deeply into your code.

However, one line caught my eye:

Do While (data_file <> &quot;&quot

In Excel, there is a distinct difference between an empty cell (Null) and an empty string (&quot;&quot in a cell.

I would try using:

Do While Not(IsNull(data_file))

Let me know if this helped any,

Vic

 

[adamr99]

I am not sure if it will matter.

Data_file is a variable that holds the path of the particular sheet that I am trying to extrapolate data out of.

It is read in from a path.xls sheet that simply has paths in one colum and sheet #'s in another.

Just ran the program with above changes with no luck.

Here is what I have tried:

Putting the open/close of the tables inside/outside of the loop.
Doing a different number of spreadsheets in my batch (instead of all 700).

I cannot thank you enough for your reply however. Thank you.

Any more Ideas?
Adam

 

[VicM]

Adam:

Some questions: 1) what do Text6 and Text8 represent; 2) should there be more than one record between the blank lines, except for the 121; (I'm assuming that each set of 79 blank rows delineates the start of reading from another spreadsheet); and 3) have you tried stepping thru the code one line at a time to get a feel for the sequence of the code?

Vic

 

[adamr99]

Both Text6 and Text8 are unimportant. They are on the form that this code is behind to display the current spreadsheet path and the current sheet name that I am grabing data from.

You are correct in your assumption.. ACTUALLY, I am only pulling one record per spreadsheet for this data run... I have 2 others that I need to fix (same error) that are pulling hundreds of records out of each sheet.

I have steped through the code. With no interesting new details....

Agan, I can't thank you enough.
Adam

 

[adamr99]

For anyone searching this - the solution that I went with was to simply populate some empty text boxes on my form with the data that I wanted to add, and then run an append query to add the data using the text box data instead of trying to add it in VBA.

Good Luck!

Adam

 

[Jedi420]

Hello,


I have a similar problem when just importing one spreadsheet. I get about 136 blank records (including the correct data alllll the way at the bottom of the table). There seems to have been no real solution for this as of yet ... only a workaround (which isnt bad!). Ill keep lookin and if I find something, Ill come back and post.

-Jedi420

A man who has risked his life knows that careers are worthless, and a man who will not risk his career has a worthless life.