Export from Excel to Access

[FTM]

Hello everyone,
I have a problem that I've been working on for quite a while now. I have recieved a lot of help from members of this board but I'm still stuck. Here is the spreadsheet...

[TABLE="width: 349"]
<colgroup><col style="width: 48pt;" width="64"> <col style="width: 103pt; mso-width-source: userset; mso-width-alt: 5010;" width="137"> <col style="width: 48pt;" width="64"> <col style="width: 50pt; mso-width-source: userset; mso-width-alt: 2450;" width="67"> <col style="width: 52pt; mso-width-source: userset; mso-width-alt: 2523;" width="69"> <col style="width: 48pt;" width="64"> <tbody>[TR]
[TD="class: xl65, width: 64, bgcolor: transparent"]Part[/TD]
[TD="class: xl73, width: 137, bgcolor: transparent"]Description [/TD]
[TD="class: xl66, width: 64, bgcolor: transparent"]Pack Quantity[/TD]
[TD="class: xl66, width: 67, bgcolor: transparent"]Injection Molded Parts[/TD]
[TD="class: xl66, width: 69, bgcolor: transparent"]Post Injection Processed Parts[/TD]
[TD="class: xl84, width: 64, bgcolor: transparent"]Less that full boxes[/TD]
[/TR]
[TR]
[TD="class: xl70, bgcolor: #EEECE1"]2483[/TD]
[TD="class: xl71, bgcolor: #EEECE1"]1/3 Basket[/TD]
[TD="class: xl72, bgcolor: #EEECE1"]72[/TD]
[TD="class: xl74, bgcolor: #EEECE1"]8[/TD]
[TD="class: xl88, bgcolor: #002060"] [/TD]
[TD="class: xl74, bgcolor: #EEECE1"]0[/TD]
[/TR]
[TR]
[TD="class: xl67, bgcolor: transparent"]2480[/TD]
[TD="class: xl68, bgcolor: transparent"]2/3 Basket[/TD]
[TD="class: xl69, bgcolor: transparent"]40[/TD]
[TD="class: xl76, bgcolor: transparent"]33[/TD]
[TD="class: xl88, bgcolor: #002060"] [/TD]
[TD="class: xl76, bgcolor: transparent"]0[/TD]
[/TR]
[TR]
[TD="class: xl70, bgcolor: #EEECE1"]2468[/TD]
[TD="class: xl71, bgcolor: #EEECE1"]Bottom Freezer Basket[/TD]
[TD="class: xl72, bgcolor: #EEECE1"]18[/TD]
[TD="class: xl74, bgcolor: #EEECE1"]58[/TD]
[TD="class: xl88, bgcolor: #002060"] [/TD]
[TD="class: xl74, bgcolor: #EEECE1"]0[/TD]
[/TR]
[TR]
[TD="class: xl67, bgcolor: transparent"]2438[/TD]
[TD="class: xl68, bgcolor: transparent"]Meat Pan[/TD]
[TD="class: xl69, bgcolor: transparent"]22[/TD]
[TD="class: xl76, bgcolor: transparent"]176[/TD]
[TD="class: xl88, bgcolor: #002060"] [/TD]
[TD="class: xl76, bgcolor: transparent"]0[/TD]
[/TR]
[TR]
[TD="class: xl70, bgcolor: #EEECE1"]2411[/TD]
[TD="class: xl71, bgcolor: #EEECE1"]Vegetable Pan Frame[/TD]
[TD="class: xl72, bgcolor: #EEECE1"]22[/TD]
[TD="class: xl74, bgcolor: #EEECE1"]33[/TD]
[TD="class: xl88, bgcolor: #002060"] [/TD]
[TD="class: xl74, bgcolor: #EEECE1"]0[/TD]
[/TR]
[TR]
[TD="class: xl67, bgcolor: transparent"]2586-1[/TD]
[TD="class: xl68, bgcolor: transparent"]Vegetable Pan Frame[/TD]
[TD="class: xl69, bgcolor: transparent"]20[/TD]
[TD="class: xl88, bgcolor: #002060"] [/TD]
[TD="class: xl76, bgcolor: transparent"]30[/TD]
[TD="class: xl76, bgcolor: transparent"]0[/TD]
[/TR]
[TR]
[TD="class: xl85, bgcolor: #002060"] [/TD]
[TD="class: xl86, bgcolor: #002060"] [/TD]
[TD="class: xl87, bgcolor: #002060"] [/TD]
[TD="class: xl89, bgcolor: #002060"] [/TD]
[TD="class: xl89, bgcolor: #002060"] [/TD]
[TD="class: xl89, bgcolor: #002060"] [/TD]
[/TR]
[TR]
[TD="class: xl70, bgcolor: #EEECE1"]2554[/TD]
[TD="class: xl71, bgcolor: #EEECE1"]3/4 Basket[/TD]
[TD="class: xl72, bgcolor: #EEECE1"]40[/TD]
[TD="class: xl74, bgcolor: #EEECE1"]26[/TD]
[TD="class: xl88, bgcolor: #002060"]j[/TD]
[TD="class: xl74, bgcolor: #EEECE1"]0[/TD]
[/TR]
[TR]
[TD="class: xl67, bgcolor: transparent"]2464[/TD]
[TD="class: xl68, bgcolor: transparent"]Full Basket[/TD]
[TD="class: xl69, bgcolor: transparent"]22[/TD]
[TD="class: xl76, bgcolor: transparent"]24[/TD]
[TD="class: xl88, bgcolor: #002060"] [/TD]
[TD="class: xl76, bgcolor: transparent"]0[/TD]
[/TR]
[TR]
[TD="class: xl70, bgcolor: #EEECE1"]2561[/TD]
[TD="class: xl71, bgcolor: #EEECE1"]Bottom Freezer Basket[/TD]
[TD="class: xl72, bgcolor: #EEECE1"]18[/TD]
[TD="class: xl74, bgcolor: #EEECE1"]22[/TD]
[TD="class: xl88, bgcolor: #002060"] [/TD]
[TD="class: xl74, bgcolor: #EEECE1"]0[/TD]
[/TR]
[TR]
[TD="class: xl67, bgcolor: transparent"]2549[/TD]
[TD="class: xl68, bgcolor: transparent"]Meat Pan[/TD]
[TD="class: xl69, bgcolor: transparent"]22[/TD]
[TD="class: xl76, bgcolor: transparent"]130[/TD]
[TD="class: xl88, bgcolor: #002060"] [/TD]
[TD="class: xl76, bgcolor: transparent"]0[/TD]
[/TR]
[TR]
[TD="class: xl70, bgcolor: #EEECE1"]2456[/TD]
[TD="class: xl71, bgcolor: #EEECE1"]Vegetable Pan Frame[/TD]
[TD="class: xl72, bgcolor: #EEECE1"]20[/TD]
[TD="class: xl74, bgcolor: #EEECE1"]4[/TD]
[TD="class: xl88, bgcolor: #002060"] [/TD]
[TD="class: xl74, bgcolor: #EEECE1"]0[/TD]
[/TR]
[TR]
[TD="class: xl67, bgcolor: transparent"]2586-2[/TD]
[TD="class: xl68, bgcolor: transparent"]Vegetable Pan Frame[/TD]
[TD="class: xl69, bgcolor: transparent"]20[/TD]
[TD="class: xl88, bgcolor: #002060"] [/TD]
[TD="class: xl76, bgcolor: transparent"]20[/TD]
[TD="class: xl76, bgcolor: transparent"]0[/TD]
[/TR]
[TR]
[TD="class: xl85, bgcolor: #002060"] [/TD]
[TD="class: xl86, bgcolor: #002060"] [/TD]
[TD="class: xl87, bgcolor: #002060"] [/TD]
[TD="class: xl89, bgcolor: #002060"] [/TD]
[TD="class: xl89, bgcolor: #002060"] [/TD]
[TD="class: xl89, bgcolor: #002060"] [/TD]
[/TR]
[TR]
[TD="class: xl70, bgcolor: #EEECE1"]2675[/TD]
[TD="class: xl71, bgcolor: #EEECE1"]1/3 Basket[/TD]
[TD="class: xl72, bgcolor: #EEECE1"]92[/TD]
[TD="class: xl74, bgcolor: #EEECE1"]9[/TD]
[TD="class: xl88, bgcolor: #002060"] [/TD]
[TD="class: xl74, bgcolor: #EEECE1"]0[/TD]
[/TR]
[TR]
[TD="class: xl67, bgcolor: transparent"]2674[/TD]
[TD="class: xl68, bgcolor: transparent"]2/3 Basket[/TD]
[TD="class: xl69, bgcolor: transparent"]36[/TD]
[TD="class: xl76, bgcolor: transparent"]24[/TD]
[TD="class: xl88, bgcolor: #002060"] [/TD]
[TD="class: xl76, bgcolor: transparent"]0[/TD]
[/TR]
[TR]
[TD="class: xl70, bgcolor: #EEECE1"]2676[/TD]
[TD="class: xl71, bgcolor: #EEECE1"]Bottom Freezer Basket [/TD]
[TD="class: xl72, bgcolor: #EEECE1"]24[/TD]
[TD="class: xl74, bgcolor: #EEECE1"]32[/TD]
[TD="class: xl88, bgcolor: #002060"] [/TD]
[TD="class: xl74, bgcolor: #EEECE1"]0[/TD]
[/TR]
[TR]
[TD="class: xl67, bgcolor: transparent"]2666[/TD]
[TD="class: xl68, bgcolor: transparent"]Vegetable Pan Frame[/TD]
[TD="class: xl69, bgcolor: transparent"]10[/TD]
[TD="class: xl76, bgcolor: transparent"]145[/TD]
[TD="class: xl88, bgcolor: #002060"]I[/TD]
[TD="class: xl76, bgcolor: transparent"]0[/TD]
[/TR]
[TR]
[TD="class: xl70, bgcolor: #EEECE1"]2586-3[/TD]
[TD="class: xl71, bgcolor: #EEECE1"]Vegetable Pan Frame[/TD]
[TD="class: xl72, bgcolor: #EEECE1"]20[/TD]
[TD="class: xl88, bgcolor: #002060"] [/TD]
[TD="class: xl74, bgcolor: #EEECE1"]28[/TD]
[TD="class: xl74, bgcolor: #EEECE1"]0[/TD]
[/TR]
[TR]
[TD="class: xl85, bgcolor: #002060"] [/TD]
[TD="class: xl86, bgcolor: #002060"] [/TD]
[TD="class: xl87, bgcolor: #002060"] [/TD]
[TD="class: xl89, bgcolor: #002060"] [/TD]
[TD="class: xl89, bgcolor: #002060"] [/TD]
[TD="class: xl89, bgcolor: #002060"] [/TD]
[/TR]
[TR]
[TD="class: xl70, bgcolor: #EEECE1"]1752[/TD]
[TD="class: xl71, bgcolor: #EEECE1"]Ice Box Door Outer[/TD]
[TD="class: xl72, bgcolor: #EEECE1"]14[/TD]
[TD="class: xl74, bgcolor: #EEECE1"]18[/TD]
[TD="class: xl88, bgcolor: #002060"] [/TD]
[TD="class: xl74, bgcolor: #EEECE1"]0[/TD]
[/TR]
[TR]
[TD="class: xl67, bgcolor: transparent"]1818[/TD]
[TD="class: xl68, bgcolor: transparent"]Ice Box Door Outer[/TD]
[TD="class: xl69, bgcolor: transparent"]14[/TD]
[TD="class: xl88, bgcolor: #002060"] [/TD]
[TD="class: xl76, bgcolor: transparent"]0[/TD]
[TD="class: xl76, bgcolor: transparent"]0[/TD]
[/TR]
[TR]
[TD="class: xl70, bgcolor: #EEECE1"]1753[/TD]
[TD="class: xl71, bgcolor: #EEECE1"]Ice Box Door Inner[/TD]
[TD="class: xl72, bgcolor: #EEECE1"]19[/TD]
[TD="class: xl74, bgcolor: #EEECE1"]45[/TD]
[TD="class: xl88, bgcolor: #002060"] [/TD]
[TD="class: xl74, bgcolor: #EEECE1"]0[/TD]
[/TR]
[TR]
[TD="class: xl80, bgcolor: transparent"]1817[/TD]
[TD="class: xl81, bgcolor: transparent"]Ice Box Door Inner[/TD]
[TD="class: xl82, bgcolor: transparent"]19[/TD]
[TD="class: xl88, bgcolor: #002060"] [/TD]
[TD="class: xl83, bgcolor: transparent"]0[/TD]
[TD="class: xl83, bgcolor: transparent"]0[/TD]
[/TR]
[TR]
[TD="class: xl77, bgcolor: #EEECE1"]1405[/TD]
[TD="class: xl78, bgcolor: #EEECE1"]Air Tower[/TD]
[TD="class: xl79, bgcolor: #EEECE1"]35[/TD]
[TD="class: xl75, bgcolor: #EEECE1"]225[/TD]
[TD="class: xl90, bgcolor: #002060"] [/TD]
[TD="class: xl75, bgcolor: #EEECE1"]0[/TD]
[/TR]
</tbody>[/TABLE]


I have this script that somebody wrote for me and I've fiddled with, a lot...

Sub Export_Inventory_Report_to_Access_29()
'
' Test2 Macro

Dim rng As Range
Dim s(1) As String
Dim SQL As String
Dim i As Long
Dim db As Object 'DAO.Database
Dim rs As Object 'DAO.Recordset
ActiveWindow.ScrollWorkbookTabs Position:=xlFirst
Sheets("Inventory Report").Select
Range("A2").Select

'//Open Home Workstation Database

Set db = DBEngine(0).OpenDatabase("C:\users\Mann\My Documents\Production Report and Inventory Management\Inventory Report and Machine Utilization Database\Inventory Report.Accdb")

Set rs = db.OpenRecordset("Inventory Report")

'//Range of values to update

Set rng = Sheet1.Range("A2:H26").CurrentRegion

'//First Half of SQL Statement

s(0) = "INSERT INTO Table1 (Part, Description, Pack Quantity, Box Count, Odds, Actual Inventory) VALUES ( "

'//Loop rows and create rest of the SQL Statement, then run SQL commands

For i = 2 To rng.Rows.Count

If Len(rng.Cells(i, 4).Value) <> 0 Then

rs.AddNew
rs.Fields("Part").Value = rng.Rows(i).Cells(1).Value
rs.Fields("Description").Value = rng.Rows(i).Cells(2).Value
rs.Fields("Pack Quantity").Value = rng.Rows(i).Cells(3).Value
rs.Fields("Box Count").Value = rng.Rows(i).Cells(4).Value
rs.Fields("Odds").Value = rng.Rows(i).Cells(6).Value
rs.Fields("Actual Inventory Total").Value = rng.Rows(i).Cells(7).Value
rs.Update

Else
Exit For

End If
Next i

'//Close database
'On Error Move Next

rs.Close
Set rs = Nothing
db.Close
Set db = Nothing

End Sub

Now...

What I want to do is to loop through the spreadsheet and copy data from the spreadsheet to the MS Access Database if there is data <> 0 in the D Column, cell by cell. I want to be able to loop past cells that are empty and formatted in the dark blue.

Truth be told I've written four different scripts that loop through the afforementioned script and then exits the loop when the contents of the D Column = 0.

I can move the Row labeled 1752 to the database. The Script that I hacked on that attempts the same move with 1753 ether copies 1752 or doesn't do anything. Same thing with the row labeled 1405.

So...

Who has time and the inclination to assist me in this endevor?

Thanks in advance for your assistance.

Regards,

FTM

 

[marc8484]

Why not assign a value to empty cells or perhaps assign a format to the columns so it can search and pull data?

 

[FTM]

Why not assign a value to empty cells or perhaps assign a format to the columns so it can search and pull data?

I did exactly as you suggest thinking that I would write an MS Access query to go through the database and blow qaway all the records containing bad data. Then I thought what a hassle that would be as the database got bigger and bigger. So I decided to stick with data integrity on the spreadsheet side of the problem.

I'm thinkingt that what I'll end up doing is write a script using nested loops, "For i = 2483 and j <> 0..." something like that.

Unless anyone else has better ideas.

Thanks in advance for your help.

Regards,

FTM

 

[FTM]

K...

Here's what I did. It Works, in the event that anyone needs a reference. I'm sure that somebody that knows what they're doing can streamline the process.

Thanks everyone for the assistance .

Sub Export_Inventory_Report_to_Access_IMPPs()
'
' Export_Inventory_Report_to_Access Macro
'
' Declare variables...
Dim FinalRow As Integer
'Dim FinalCol As Integer
Dim i As Integer
' Dim j As Integer
' Dim k As Integer
' Dim l As Integer
' Dim Press As String
' Dim Shift As String
Dim Sheet As String
Dim rng As Range
Dim s(1) As String
Dim SQL As String
Dim db As Object 'DAO.Database
Dim rs As Object 'DAO.Recordset

Application.ScreenUpdating = False
'//Open Laptop Database

'Set db = DBEngine(0).OpenDatabase("C:\users\220042816\My Documents\Production Report and Inventory Management\Inventory Report and Machine Utilization Database\Inventory Report.Accdb")

'//Open Home Workstation Database

Set db = DBEngine(0).OpenDatabase("C:\users\Mann\My Documents\Production Report and Inventory Management\Inventory Report and Machine Utilization Database\Inventory Report.Accdb")

Set rs = db.OpenRecordset("Inventory Report")

' Calculate the length of the form...
FinalRow = (Cells(Rows.Count, 2).End(xlUp).Row)
'FinalCol = Cells(1, Columns.Count).End(xlToLeft).Column
For i = 2 To FinalRow Step 1

'Select cells to examine based on For/Next loop Step...

Sheets("Inventory Report").Select
Cells(i, 4).Select

'Check for Valid Data...

If ActiveCell <> 0 Then

'Transfer data from the Inventory Report Spreadsheet to the Inventory Report and Machine Utilization Database
'Inventory Report Table...
'//Range of values to update

Set rng = Sheet1.Range("A2:J26").CurrentRegion

'//First Half of SQL Statement

s(0) = "INSERT INTO Table1 (Part, Description, Pack Quantity, Box Count, Odds, Actual Inventory, Inventory Target) VALUES ( "

rs.AddNew
rs.Fields("Part").Value = rng.Rows(i).Cells(1).Value
rs.Fields("Description").Value = rng.Rows(i).Cells(2).Value
rs.Fields("Pack Quantity").Value = rng.Rows(i).Cells(3).Value
rs.Fields("Box Count").Value = rng.Rows(i).Cells(4).Value
rs.Fields("Odds").Value = rng.Rows(i).Cells(6).Value
rs.Fields("Actual Inventory Total").Value = rng.Rows(i).Cells(7).Value
rs.Fields("Inventory Target").Value = rng.Rows(i).Cells(10).Value
rs.Update

End If

Next i

rs.Close
Set rs = Nothing
db.Close
Set db = Nothing
Application.ScreenUpdating = True
'
End Sub