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...

Part	Description	Pack Quantity	Injection Molded Parts	Post Injection Processed Parts	Less that full boxes
2483	1/3 Basket	72	8		0
2480	2/3 Basket	40	33		0
2468	Bottom Freezer Basket	18	58		0
2438	Meat Pan	22	176		0
2411	Vegetable Pan Frame	22	33		0
2586-1	Vegetable Pan Frame	20		30	0
2554	3/4 Basket	40	26	j	0
2464	Full Basket	22	24		0
2561	Bottom Freezer Basket	18	22		0
2549	Meat Pan	22	130		0
2456	Vegetable Pan Frame	20	4		0
2586-2	Vegetable Pan Frame	20		20	0
2675	1/3 Basket	92	9		0
2674	2/3 Basket	36	24		0
2676	Bottom Freezer Basket	24	32		0
2666	Vegetable Pan Frame	10	145	I	0
2586-3	Vegetable Pan Frame	20		28	0
1752	Ice Box Door Outer	14	18		0
1818	Ice Box Door Outer	14		0	0
1753	Ice Box Door Inner	19	45		0
1817	Ice Box Door Inner	19		0	0
1405	Air Tower	35	225		0

<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>
</tbody>

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