Export random excel data to access

  • Thread starter Thread starter ielmrani via AccessMonster.com
  • Start date Start date
I

ielmrani via AccessMonster.com

Hi,
is there a way to export random excel data to an access table?
Thanks in advance.
Ismail
 
Hi John,
thank you for your quick repy.
I apologize for posting the wrong issue.

What I am trying to do now is import some data (not all data) from excel to
an access table.
I need to import ONLY the following Columns from excel to an access table:
Plan ID xxx
Participant count 132
Computed asset balance: $1,528,172.03
Computed fee $2,865.32

Here is an example of the excel sheet where the data is being imported from:


As you can see the data is not organized into columns and rows.


Plan ID: xxxx Artemide, Inc. 401(k) Profit Sharing Plan

Report period: 7/01/2006 to 9/30/2006
Run date: 10/9/2006 1:12:23PM
Fee schedule: 75BP/4500 75BP / 4500 Annual

Distribution fee amount: $0.00 Count: 1
Amount due: $0.00
Loan fee amount: $0.00 Count: 0 Amount due:
$0.00
Transfer fee amount: $0.00 Count: 7 Amount
due: $0.00
Withdrawal fee amount: $0.00 Count: 0
Amount due: $0.00
Fee rate: $0.00
Participant count: 132
Minimum asset fee assessed: $0.00
Computed asset balance: $1,528,172.03
Rate: 0.001875 basis points
Computed fee: $2,865.32
 
Hi,

This is possible. You need to write VBA code to let Access open the
workbook and extract the values from individual cells before appending
them to your table.

A lot depends on just how the worksheet is laid out.

If the values you want are always in the same positions on the worksheet
(e.g. Plan ID is always in cell B1, Participant count is always in D12
and so on), you can do something like this to get the values (air code):

Dim oBook As Excel.Workbook
Dim oSheet
Dim RS as DAO.Recordset
Dim PlanId As String
Dim ParticipantCount As Long
...

'Open worksheet
Set oBook = GetObject("D:\Folder\My Workbook.xls")
Set oSheet = oBook.Worksheets("My Worksheet")

With oSheet
'get values
PlanId = .Range("B1").Value
ParticipantCount = .Range("D12").Value
...
End With

'close workbook
oBook.Close False

'append new record to table
Set RS = CurrentDB.OpenRecordset("MyTable")
With RS
.AddNew
.Fields("PlanID").Value = PlanID
.Fields("ParticipantCount").Value = ParticipantCount
...
.Update
.Close
End With



If the values you want may be in different positions in the worksheet
you'll need to write code that searches for them (e.g. start in Row 1
Column 1 and look in each cell in Column 1 until it finds
PlanID:
and then get the value from the cell to the right of that, then continue
down Column 1 until it finds
Participant Count
and look in successive cells to the right until it finds one that
contains a value.
 
Hi John,
First of all thank you for taking the time to help me.

Here is the code I typed: (I am getting this error message: Compile error
user-Defined type not defined)
in the code you'll see this line:

PlanID = .range("A:H").value
ParticipantCount = .range("G:H").Value
computedassetBalance = .range("F:H").Value
computedfee = .range("F:G").Value

because the range of PlanID, Computedfee, computedassetBalance and
computedfee fall in more than one cell. At the end of the code I put an
example of the excel sheet again to give you a better idea on how it looks.

Code

Private Sub Command0_Click()
Dim oBook As Excel.Workbook
Dim oSheet
Dim RS As DAO.Recordset
Dim PlanId As String
Dim ParticipantCount As Long
Dim computedassetBalance As Long
Dim computedfee As Long

'Open worksheet
Set oBook = GetObject("C:\MyDocument\Test.xls")
Set oSheet = oBook.Worksheets("Test")

With oSheet
'get values
PlanId = .range("A:H").Value
ParticipantCount = .range("G:H").Value
computedassetBalance = .range("F:H").Value
computedfee = .range("F:G").Value
End With

'close workbook
oBook.Close False

'append new record to table
Set RS = CurrentDb.OpenRecordset("Table1")
With RS
.AddNew
.Fields("PlanID").Value = PlanId
.Fields("ParticipantCount").Value = ParticipantCount
.Fields("ComputedatassetBalance").Value = CpmBalance
.Fields("computedfee").Value = CmpuFee
.Update
.Close
End With

End Sub


Here is part of the Excel Sheet


Internal Summary Report 7/18/2006

Plan ID: xxx Namebbbbbbbbbb

Report period: 4/01/2006 to 6/30/2006

Run date: 7/18/2006 2:29:49PM
Fee schedule: 75BP/4500 75BP / 4500 Annual
Fees processed by division: No
Activity Fees
Activity fees are paid from plan assets: No
Distribution fee amount: $0.00 Count: 1 Amount due: $0.00
Loan fee amount: $0.00 Count: 0 Amount due: $0.00
Transfer fee amount: $0.00 Count: 0 Amount due: $0.00
Withdrawal fee amount: $0.00 Count: 0 Amount due: $0.00
Computed fee: $0.00
Administrative Fees
Administrative fees are paid from plan assets: No
Fee prorated for report period: No
Base administration fee: $1,125.00
"Plan asset level for waiver of base
administration fee:"
$0.00
Prorated administration fee: $1,125.00
Assess fee to participant if balance
greater than: $0.00
Fee is per participant: No
Administrative fee type: Count
Fee rate: $0.00
Participant count: 130
Computed fee: $0.00

Loan maintenance fee: $0.00
Number of loans: 0

Total loan maintenance fee: $0.00
Asset Fees

Asset fees are paid from plan assets: No
Fee prorated for report period: No
Minimum asset fee assessed: $0.00
Computed asset balance: $1,445,365.99
Asset calculation method: Exclusive
Rate: 0.001875 basis points
Computed fee: $2,710.06
General Fees
General fees are paid from plan assets: No
Fee prorated for report period: No
Managed Account Fee: $0.00

Fee description: Schedule amount: $0.00 Computed amount: $0.00

Fee description: Schedule amount: $0.00 Computed amount: $0.00

Fee description: Schedule amount: $0.00 Computed amount: $0.00

Fee description: Schedule amount: $0.00 Computed amount: $0.00

Fee description: Schedule amount: $0.00 Computed amount: $0.00



Plan ID: 1231NameCCCCCCCCCCCCCCCCCCCCc

Report period: 4/01/2006 to 6/30/2006

Run date: 7/18/2006 2:29:49PM


Fee schedule: 75BP/4500 75BP / 4500 Annual

Fees processed by division: No



Request ID: 92463


Plan Summary

Total activity fees: $0.00
Total administrative fees: $1,125.00
Total asset fees: $2,710.06

Total general fees: $0.00
Grand total: $3,835.06

Amount paid from plan assets: $0.00
Net amount due: $3,835.06


Plan ID: 169 Quad-C Management, Inc. 401(k) Profit Sharing Plan

Report period: 4/01/2006 to 6/30/2006

Run date: 7/18/2006 2:29:49PM


Fee schedule: 48BP/5333 48BP / 5333.33

Fees processed by division: No


Activity Fees

Activity fees are paid from plan assets: No
Distribution fee amount: $0.00 Count: 0 Amount due: $0.00
Loan fee amount: $0.00 Count: 0 Amount due: $0.00
Transfer fee amount: $0.00 Count: 0 Amount due: $0.00
Withdrawal fee amount: $0.00 Count: 0 Amount due: $0.00
Computed fee: $0.00
Administrative Fees

Administrative fees are paid from plan assets: No
Fee prorated for report period: No
Base administration fee: $1,333.33
"Plan asset level for waiver of base
administration fee:"


$0.00
Prorated administration fee: $1,333.33
Assess fee to participant if balance
greater than: $0.00
Fee is per participant: No
Administrative fee type: Count
Fee rate: $0.00
Participant count: 27
Computed fee: $0.00

Loan maintenance fee: $0.00

Number of loans: 0

Total loan maintenance fee: $0.00

Asset Fees

Asset fees are paid from plan assets: No
Fee prorated for report period: No
Minimum asset fee assessed: $0.00
Computed asset balance: $2,388,263.51
Asset calculation method: Exclusive
Rate: 0.001200 basis points
Computed fee: $2,865.92
General Fees

General fees are paid from plan assets: No
Fee prorated for report period: No
Managed Account Fee: $0.00

Fee description: Schedule amount: $0.00 Computed amount: $0.00

Fee description: Schedule amount: $0.00 Computed amount: $0.00

Fee description: Schedule amount: $0.00 Computed amount: $0.00

Fee description: Schedule amount: $0.00 Computed amount: $0.00

Fee description: Schedule amount: $0.00 Computed amount: $0.00


John said:
Hi,

This is possible. You need to write VBA code to let Access open the
workbook and extract the values from individual cells before appending
them to your table.

A lot depends on just how the worksheet is laid out.

If the values you want are always in the same positions on the worksheet
(e.g. Plan ID is always in cell B1, Participant count is always in D12
and so on), you can do something like this to get the values (air code):

Dim oBook As Excel.Workbook
Dim oSheet
Dim RS as DAO.Recordset
Dim PlanId As String
Dim ParticipantCount As Long
...

'Open worksheet
Set oBook = GetObject("D:\Folder\My Workbook.xls")
Set oSheet = oBook.Worksheets("My Worksheet")

With oSheet
'get values
PlanId = .Range("B1").Value
ParticipantCount = .Range("D12").Value
...
End With

'close workbook
oBook.Close False

'append new record to table
Set RS = CurrentDB.OpenRecordset("MyTable")
With RS
.AddNew
.Fields("PlanID").Value = PlanID
.Fields("ParticipantCount").Value = ParticipantCount
...
.Update
.Close
End With

If the values you want may be in different positions in the worksheet
you'll need to write code that searches for them (e.g. start in Row 1
Column 1 and look in each cell in Column 1 until it finds
PlanID:
and then get the value from the cell to the right of that, then continue
down Column 1 until it finds
Participant Count
and look in successive cells to the right until it finds one that
contains a value.


Hi John,
thank you for your quick repy.
Click to expand...
[quoted text clipped - 46 lines]
Click to expand...
 
Hi John,
Please disregard my last Post.

I am on the next stage because I solved one problem. I have no clue how to
do this one.
If I have an access table like this:

Field1 Field2

Plan ID: 123 Namebbb
ParCount 130
Compfee 158798
Computed asset balance 12587
Date 6/30/2006
Fee rate 0
Asset Fees 0

Plan ID: 456 Name DDDD
ParCount 129
Compfee 158799
Computed asset balance 12588
Date 7/30/2006
Fee rate 0
Asset Fees 0

Plan ID: 023 Namebbb
ParCount 132
Compfee 158794
Computed asset balance 12570
Date 6/30/2006
Fee rate 0
Asset Fees 0
ect...

is it possible to have another access table/Query that contain only the
following:

PlanID ParCount Compfee Computed asset balance
123 130 158798 12587
456 129 158799 12588
023 132 158794 12570


Thank you in advance
Hi John,
First of all thank you for taking the time to help me.

Here is the code I typed: (I am getting this error message: Compile error
user-Defined type not defined)
in the code you'll see this line:

PlanID = .range("A:H").value
ParticipantCount = .range("G:H").Value
computedassetBalance = .range("F:H").Value
computedfee = .range("F:G").Value

because the range of PlanID, Computedfee, computedassetBalance and
computedfee fall in more than one cell. At the end of the code I put an
example of the excel sheet again to give you a better idea on how it looks.

Code

Private Sub Command0_Click()
Dim oBook As Excel.Workbook
Dim oSheet
Dim RS As DAO.Recordset
Dim PlanId As String
Dim ParticipantCount As Long
Dim computedassetBalance As Long
Dim computedfee As Long

'Open worksheet
Set oBook = GetObject("C:\MyDocument\Test.xls")
Set oSheet = oBook.Worksheets("Test")

With oSheet
'get values
PlanId = .range("A:H").Value
ParticipantCount = .range("G:H").Value
computedassetBalance = .range("F:H").Value
computedfee = .range("F:G").Value
End With

'close workbook
oBook.Close False

'append new record to table
Set RS = CurrentDb.OpenRecordset("Table1")
With RS
.AddNew
.Fields("PlanID").Value = PlanId
.Fields("ParticipantCount").Value = ParticipantCount
.Fields("ComputedatassetBalance").Value = CpmBalance
.Fields("computedfee").Value = CmpuFee
.Update
.Close
End With

End Sub

Here is part of the Excel Sheet


Internal Summary Report 7/18/2006

Plan ID: xxx Namebbbbbbbbbb

Report period: 4/01/2006 to 6/30/2006

Run date: 7/18/2006 2:29:49PM
Fee schedule: 75BP/4500 75BP / 4500 Annual
Fees processed by division: No
Activity Fees
Activity fees are paid from plan assets: No
Distribution fee amount: $0.00 Count: 1 Amount due: $0.00
Loan fee amount: $0.00 Count: 0 Amount due: $0.00
Transfer fee amount: $0.00 Count: 0 Amount due: $0.00
Withdrawal fee amount: $0.00 Count: 0 Amount due: $0.00
Computed fee: $0.00
Administrative Fees
Administrative fees are paid from plan assets: No
Fee prorated for report period: No
Base administration fee: $1,125.00
"Plan asset level for waiver of base
administration fee:"
$0.00
Prorated administration fee: $1,125.00
Assess fee to participant if balance
greater than: $0.00
Fee is per participant: No
Administrative fee type: Count
Fee rate: $0.00
Participant count: 130
Computed fee: $0.00

Loan maintenance fee: $0.00
Number of loans: 0

Total loan maintenance fee: $0.00
Asset Fees

Asset fees are paid from plan assets: No
Fee prorated for report period: No
Minimum asset fee assessed: $0.00
Computed asset balance: $1,445,365.99
Asset calculation method: Exclusive
Rate: 0.001875 basis points
Computed fee: $2,710.06
General Fees
General fees are paid from plan assets: No
Fee prorated for report period: No
Managed Account Fee: $0.00

Fee description: Schedule amount: $0.00 Computed amount: $0.00

Fee description: Schedule amount: $0.00 Computed amount: $0.00

Fee description: Schedule amount: $0.00 Computed amount: $0.00

Fee description: Schedule amount: $0.00 Computed amount: $0.00

Fee description: Schedule amount: $0.00 Computed amount: $0.00



Plan ID: 1231NameCCCCCCCCCCCCCCCCCCCCc

Report period: 4/01/2006 to 6/30/2006

Run date: 7/18/2006 2:29:49PM


Fee schedule: 75BP/4500 75BP / 4500 Annual

Fees processed by division: No



Request ID: 92463


Plan Summary

Total activity fees: $0.00
Total administrative fees: $1,125.00
Total asset fees: $2,710.06

Total general fees: $0.00
Grand total: $3,835.06

Amount paid from plan assets: $0.00
Net amount due: $3,835.06


Plan ID: 169 Quad-C Management, Inc. 401(k) Profit Sharing Plan

Report period: 4/01/2006 to 6/30/2006

Run date: 7/18/2006 2:29:49PM


Fee schedule: 48BP/5333 48BP / 5333.33

Fees processed by division: No


Activity Fees

Activity fees are paid from plan assets: No
Distribution fee amount: $0.00 Count: 0 Amount due: $0.00
Loan fee amount: $0.00 Count: 0 Amount due: $0.00
Transfer fee amount: $0.00 Count: 0 Amount due: $0.00
Withdrawal fee amount: $0.00 Count: 0 Amount due: $0.00
Computed fee: $0.00
Administrative Fees

Administrative fees are paid from plan assets: No
Fee prorated for report period: No
Base administration fee: $1,333.33
"Plan asset level for waiver of base
administration fee:"


$0.00
Prorated administration fee: $1,333.33
Assess fee to participant if balance
greater than: $0.00
Fee is per participant: No
Administrative fee type: Count
Fee rate: $0.00
Participant count: 27
Computed fee: $0.00

Loan maintenance fee: $0.00

Number of loans: 0

Total loan maintenance fee: $0.00

Asset Fees

Asset fees are paid from plan assets: No
Fee prorated for report period: No
Minimum asset fee assessed: $0.00
Computed asset balance: $2,388,263.51
Asset calculation method: Exclusive
Rate: 0.001200 basis points
Computed fee: $2,865.92
General Fees

General fees are paid from plan assets: No
Fee prorated for report period: No
Managed Account Fee: $0.00

Fee description: Schedule amount: $0.00 Computed amount: $0.00

Fee description: Schedule amount: $0.00 Computed amount: $0.00

Fee description: Schedule amount: $0.00 Computed amount: $0.00

Fee description: Schedule amount: $0.00 Computed amount: $0.00

Fee description: Schedule amount: $0.00 Computed amount: $0.00
Hi,
Click to expand...
[quoted text clipped - 62 lines]
Please respond in the newgroup and not by email.
Click to expand...
Click to expand...
 
No, you can't get from the Access table you describe to the query result
you want.

The reason is that there is nothing in the table that indicates which of
the "ParCount" records belongs to which of the "Plan ID" records.

Remember that relational databases do not promise to store records in
any specific order. The only way to be certain that a query returns
records in the order you want them is to explicitly sort it on one or
more fields or expressions: but there is no such field in your table.

One possible solution is to add a column in Excel containing a number
that increments with each new Plan ID. Here's a formula that can do
this. Assuming that your Field1 and Field2 are Columns A and B of the
worksheet, with the first Plan ID entry in A2, enter this
1
in C2, then this
=IF(ISERR(FIND("Plan ID:",A2)),C1,C1+1)
in C3 and fill down to the end of your table. You should get something
like this:

A B C

Plan ID: 123 Namebbb 1
ParCount 130 1
Compfee 158798 1
Computed asset balance 12587 1
Date 6/30/2006 1
Fee rate 0 1
Asset Fees 0 1
1
Plan ID: 456 Name DDDD 2
ParCount 129 2
Compfee 158799 2
Computed asset balance 12588 2
Date 7/30/2006 2
Fee rate 0 2
Asset Fees 0 2
2
Plan ID: 023 Namebbb 3
ParCount 132 3
Compfee 158794 3
....

Having imported that into Access you have the vital link that shows
which record goes with which, and it's possible to build a query that
joins four copies of the table on the new field and gives you the result
you need.
 
Thank you so much John. Your solution worked.

John said:
No, you can't get from the Access table you describe to the query result
you want.

The reason is that there is nothing in the table that indicates which of
the "ParCount" records belongs to which of the "Plan ID" records.

Remember that relational databases do not promise to store records in
any specific order. The only way to be certain that a query returns
records in the order you want them is to explicitly sort it on one or
more fields or expressions: but there is no such field in your table.

One possible solution is to add a column in Excel containing a number
that increments with each new Plan ID. Here's a formula that can do
this. Assuming that your Field1 and Field2 are Columns A and B of the
worksheet, with the first Plan ID entry in A2, enter this
1
in C2, then this
=IF(ISERR(FIND("Plan ID:",A2)),C1,C1+1)
in C3 and fill down to the end of your table. You should get something
like this:

A B C

Plan ID: 123 Namebbb 1
ParCount 130 1
Compfee 158798 1
Computed asset balance 12587 1
Date 6/30/2006 1
Fee rate 0 1
Asset Fees 0 1
1
Plan ID: 456 Name DDDD 2
ParCount 129 2
Compfee 158799 2
Computed asset balance 12588 2
Date 7/30/2006 2
Fee rate 0 2
Asset Fees 0 2
2
Plan ID: 023 Namebbb 3
ParCount 132 3
Compfee 158794 3
...

Having imported that into Access you have the vital link that shows
which record goes with which, and it's possible to build a query that
joins four copies of the table on the new field and gives you the result
you need.
Hi John,
Please disregard my last Post.
Click to expand...
[quoted text clipped - 37 lines]
456 129 158799 12588
023 132 158794 12570
Click to expand...
Click to expand...
 
Back
Top