Export Ramdom Excel Data 2

[Finedean]

Hi Everyone,
Does anyone know a program to help me export data from excel to Microsoft Access?
The data in excel is not in row/Column format.

Thanks in advance
dean

 

[SkipVought]

Hi,

What data do you want to put WHERE in Access?

In Excel's VB Editor you can use ADO Data Objects to do something to an Access database.

Do you want to ADD a row, UPDATE a row, DELETE a row?

Need to know a bit more about want you want to do.

Please be CLEAR, COMCISE & COMPLETE.

Skip,

_[red][/red]

 

[Finedean]

I have an excel sheet that contains random data. this is an example of the excel sheet:


Plan ID: 1231 XXXX, Inc. 401(k) Profit Sharing Plan

Report period: 4/01/2006 to 6/30/2006
Activity fees are paid from plan assets:
Distribution fee amount: $0.00
Count: 1
Amount due: $0.00
Participant count: 130
Computed fee: $0.00
Loan maintenance fee: $0.00
Computed asset balance: $1,445,365.99
Computed fee: $2,710.06

Plan ID: 55 YYYY, Inc. Profit Sharing Plan

Report period: 4/01/2006 to 6/30/2006
Activity fees are paid from plan assets:
Distribution fee amount: $0.00
Count: 1
Amount due: $0.00
Participant count: 133
Computed fee: $0.00
Loan maintenance fee: $0.00
Computed asset balance: $1,569,259
Computed fee: $2,710.06
etc..


I am looking for a program or a code to extract only some data in row/columns format from the excel sheet not all of the data, and export it to a microsoft access table.

If it's possible something like this:
PlanID Participant count Computed asset balance:
1231 130 $1,445,365.99
55 133 $1,569,259
....

I hope this clear.
Thanks

 

[SkipVought]

You have not described your sheet very well. I need to know how to identify WHAT you want to send and WHERE the data is.

Is you example ALL in one column? If not, what is in column A, B, C etc.

What is the key identifier?

How will you be communicating that information to the program?

Skip,

_[red][/red]

 

[Finedean]

Thanks for your quick reply

Plan Id is in Column A to L
Participant count is in Column A to C
The amount (130,..) is in Column G to H
Computed Assets is in A to D
The amount ($1,445,365.99) is in F to H

I am not sure what you mean by these questions:
What is the key identifier?

How will you be communicating that information to the program?

 

[SkipVought]

Key Identifier: From the data on the sheet, how is it related to the data in the DATABASE? Is there a Key Value or ID?

Exactly what cell, in the first example, is the PlanID VALUE in. Is PlanID: in A1? Is 1231 XXXX in B1? Or is the entire string Plan ID: 1231 XXXX, Inc. 401(k) Profit Sharing Plan in A1???

Do you have MERGED CELLS, when you say "The amount (130,..) is in Column G to H" is G:H MERGED?

This will not be an easy solution for you to figure out. There is not "program" that someone is going to pull out of a hat to fix process this dredful sheet design.

Skip,

_[red][/red]

 

[Finedean]

I did not think it was going to be easy. I have never done anything like this before that's why I was asking if anyone knows of a program to help.

When I put G To H I meant G:H, or A to L it means A:L

Key Identifier will be Plan ID

Plan ID is in Cell A4:L4
Participant count is in A40:C40
The amount is in G40:H40
Computed Assets is in A5454
The amount F54:H54

Thanks

 

[SkipVought]

So if you select A4, in the FORMULA BAR you will see...
[tt]
Plan ID: 1231 XXXX, Inc. 401(k) Profit Sharing Plan
[/tt]
CORRECT?

In G40 you see
[tt]
130
[/tt]
CORRECT?

Skip,

_[red][/red]

 

[Finedean]

when you click A4 Plan ID: 1231 XXXX, Inc. 401(k) Profit Sharing Plan is in A4:L4. 130 is in G40:H40
I do not know how it happened.

 

[SkipVought]

That is NOT what I asked.

I asked what is in the FORMULA BAR!!!!

Skip,

_[red][/red]

 

[Finedean]

When you type A4 in the formula you get:
Plan ID: 1231 Artemide, Inc. 401(k) Profit Sharing Plan
G40 returns 130
A54 Returns Computed Assets
F54 Returns $1,445,365.99
A40 returns Participant count

 

[SkipVought]

Copy this into a MODULE in Excel VB Editor. Then run Main

Sub Main()
'loads a new sheet with a table of values for
'PlanID, Participant count & Computed asset balance
    Dim lRow As Long, wsSource As Worksheet, wsTable As Worksheet, lRowOut As Long, a
    Set wsSource = ActiveSheet
    Set wsTable = Worksheets.Add
    With wsTable
        .Cells(1, 1).Value = "PlanID"
        .Cells(1, 2).Value = "Participant count"
        .Cells(1, 3).Value = "Computed asset balance"
    End With
    With wsSource
        lRowOut = 2
        For lRow = 1 To LastCell(wsSource).Row
            Select Case Trim(Left(.Cells(lRow, 1).Value, 8))
                Case "Plan ID:"
                    a = Split(.Cells(lRow, 1).Value, " ")
                    wsTable.Cells(lRowOut, 1).Value = a(2)
                Case "Particip"
                    wsTable.Cells(lRowOut, 2).Value = .Cells(lRow, "G").Value
                Case "Computed"
                    If .Cells(lRow, 1).Value = "Computed asset balance:" Then
                        wsTable.Cells(lRowOut, 3).Value = .Cells(lRow, "F").Value
                        lRowOut = lRowOut + 1
                    End If
            End Select
        Next
    End With
End Sub
Function LastCell(ws As Worksheet) As Range
    With ws.Cells
        Set LastCell = .Find("*", , , , xlByRows, xlPrevious)
    End With
End Function

This will compile a table from which you can insert into a table in Access.

Skip,

_[red][/red]

 

[Finedean]

Amazing. You're genuis. My friend is a programmer but He couldn't do it. thank you so much.
I need to ask one more thing.

I need to add another field called computed field. I played with the code little bit but it did not do it. The field Computed Field heading was added to the sheet but not the data. can you look at it. I highlighted the part I added.
Computed Fee is in Cell F57


Sub Main()
'loads a new sheet with a table of values for
'PlanID, Participant count & Computed asset balance
Dim lRow As Long, wsSource As Worksheet, wsTable As Worksheet, lRowOut As Long, a
Set wsSource = ActiveSheet
Set wsTable = Worksheets.Add
With wsTable
.Cells(1, 1).Value = "PlanID"
.Cells(1, 2).Value = "Participant count"
.Cells(1, 3).Value = "Computed asset balance"
.Cells(1, 4).Value = "Computed fee"
End With
With wsSource
lRowOut = 2
For lRow = 1 To LastCell(wsSource).Row
Select Case Trim(Left(.Cells(lRow, 1).Value, 8))
Case "Plan ID:"
a = Split(.Cells(lRow, 1).Value, " ")
wsTable.Cells(lRowOut, 1).Value = a(2)
Case "Particip"
wsTable.Cells(lRowOut, 2).Value = .Cells(lRow, "G").Value
Case "computed fee"
wsTable.Cells(lRowOut, 3).Value = .Cells(lRow, "F").Value
Case "Computed"
If .Cells(lRow, 1).Value = "Computed asset balance:" Then
wsTable.Cells(lRowOut, 3).Value = .Cells(lRow, "F").Value
lRowOut = lRowOut + 1
End If
End Select
Next
End With
End Sub
Function LastCell(ws As Worksheet) As Range
With ws.Cells
Set LastCell = .Find("*", , , , xlByRows, xlPrevious)
End With
End Function

 

[Finedean]

I forgot to mention that computed fee is in the excel sheet more than once.
The one I am looking for is:
Computed fee: $2,710.06

Thanks again
Dean

 

[SkipVought]

What COLUMN is the Computed Fee Value in?

Skip,

_[red][/red]

 

[Finedean]

F57
Thanks

 

[SkipVought]

Sub Main()
'loads a new sheet with a table of values for
'PlanID, Participant count & Computed asset balance
Dim lRow As Long, wsSource As Worksheet, wsTable As Worksheet, lRowOut As Long, a
Set wsSource = ActiveSheet
Set wsTable = Worksheets.Add
With wsTable
.Cells(1, 1).Value = "PlanID"
.Cells(1, 2).Value = "Participant count"
.Cells(1, 3).Value = "Computed asset balance"
.Cells(1, 4).Value = "Computed fee"
End With
With wsSource
lRowOut = 2
For lRow = 1 To LastCell(wsSource).Row
Select Case Trim(Left(.Cells(lRow, 1).Value, 8))
Case "Plan ID:"
a = Split(.Cells(lRow, 1).Value, " ")
wsTable.Cells(lRowOut, 1).Value = a(2)
Case "Particip"
wsTable.Cells(lRowOut, 2).Value = .Cells(lRow, "G").Value
Case "Computed"
Select Case Trim(.Cells(lRow, 1).Value)
Case "Computed asset balance:"
wsTable.Cells(lRowOut, 3).Value = .Cells(lRow, "F").Value
Case "Computed fee:"
wsTable.Cells(lRowOut, 3).Value = .Cells(lRow, "F").Value
lRowOut = lRowOut + 1
End Select
End Select
Next
End With
End Sub



Skip,

_[red][/red]

 

[Finedean]

Hi Skip,
It did not work. Before you add the new line this is what got:
PlanID Participant count Computed asset Computed fee
1231 130 1445365.99
169 27 2388263.51
AAM 27 4067256.32

New Code:

PlanID Participant count Computed asset Computed fee
1231 130 0
2710.06
169 27 0
2865.92
AAM 27 0
4779.03
ACOU 22 0

now 2710.06 etc is under computed asset not computed fee.
Computed fee should equal 2710.06, 2865.92 etc..
And Computed assest should equal 1445365.99, 2388263.51 etc.

thanks

 

[SkipVought]

Sorry. Too much multi-tasking. I'm at home today, remodeling our master bath - crown molding etc.

Sub Main()
'loads a new sheet with a table of values for
'PlanID, Participant count & Computed asset balance
    Dim lRow As Long, wsSource As Worksheet, wsTable As Worksheet, lRowOut As Long, a
    Set wsSource = ActiveSheet
    Set wsTable = Worksheets.Add
    With wsTable
        .Cells(1, 1).Value = "PlanID"
        .Cells(1, 2).Value = "Participant count"
        .Cells(1, 3).Value = "Computed asset balance"
        .Cells(1, 4).Value = "Computed fee"
    End With
    With wsSource
        lRowOut = 2
        For lRow = 1 To LastCell(wsSource).Row
            Select Case Trim(Left(.Cells(lRow, 1).Value, 8))
                Case "Plan ID:"
                    a = Split(.Cells(lRow, 1).Value, " ")
                    wsTable.Cells(lRowOut, 1).Value = a(2)
                Case "Particip"
                    wsTable.Cells(lRowOut, 2).Value = .Cells(lRow, "G").Value
                Case "Computed"
                    Select Case Trim(.Cells(lRow, 1).Value)
                        Case "Computed asset balance:"
                            wsTable.Cells(lRowOut, 3).Value = .Cells(lRow, "F").Value
                        Case "Computed fee:"
                            wsTable.Cells(lRowOut, 4).Value = .Cells(lRow, "F").Value
                            lRowOut = lRowOut + 1
                    End Select
            End Select
        Next
    End With
End Sub

Skip,

_[red][/red]

 

[Finedean]

I really appreciate your help and patience.

this what I get now:

PlanID Partic Compu Computed
1231 130 0
1445365.99 2710.06
169 27 0
2388263.51 2865.92
AAM 27 0
4067256.32 4779.03
ACOU 22 0

As you can see the 0 should not be there and these 2 #'s (1445365.99 and 2710.06)should be on the same line as planid line.
I am sorry this is not working.