473,326 Members | 2,010 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,326 software developers and data experts.

Is there a way to automate importation of Excel data?

I have around 400 excel files filled with data that I need to import
in ACCESS. The tricky part is that they must be imported one at time,
and properly corrected before importing the next one. I already
automated most of the formatting task of the raw data, and I am
wondering if it is possible to automate the original importation too
(like when I click on FILE>Import).

Anyone have an idea?
Nov 13 '05 #1
3 1777
This is a import-Function for excel-data but you can find several examples
on the net.

Public Function TRANSimportTurnoverMonthYear(strSourceFile As String, iMonth
As Byte, iYear As Long) As Boolean

On Error GoTo errHandling

Dim iLoop, iCountDoubles As Integer
Dim iComID As Long
Dim bTransactionActive As Boolean
Dim objRsImport As ADODB.Recordset
Dim objRsDestination As ADODB.Recordset

' Function-succes-status
TRANSimportTurnoverMonthYear = False
'Set values
iCountDoubles = 0
Dim objConnSQL As ADODB.Connection
Set objConnSQL = CurrentProject.Connection
'Connect to datasource
Set objRsImport = New ADODB.Recordset
Dim objConnExcel As ADODB.Connection
Set objConnExcel = New ADODB.Connection
objConnExcel.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" &
strSourceFile & ";Extended Properties=""Excel 8.0;HDR=No;IMEX=1"""
objRsImport.Open "Select * from [Sheet1$]", objConnExcel,
adOpenForwardOnly, adLockReadOnly, adCmdText
DoCmd.Hourglass True
objRsImport.MoveFirst
'Open TargetTable
Set objRsDestination = New ADODB.Recordset
objRsDestination.Open "COMPANY_TURNOVER", objConnSQL, adOpenKeyset,
adLockOptimistic, adCmdTableDirect
'Start transaction
objConnSQL.BeginTrans
bTransactionActive = True
'MsgBox "start looping rows"
Do Until objRsImport.EOF
iComID = FindComIdFromVeroNr(CLng(objRsImport.Fields(0)))
If iComID = 0 Then
'No import: No matching company found FindCompanyNAME(
Else
If ExistsComTurnover(iComID, iYear, iMonth) Then
iCountDoubles = iCountDoubles + 1
gActionFailureText = gActionFailureText & "No import for
" & FindCompanyNAME(CurrentProject.Connection, iComID) & " (Ycomm_ID=" &
iComID & ") : value exists!" & vbCrLf
If iCountDoubles > 9 Then
gActionFailureText = "To many doubles. Check total
second import!"
End If
Else
With objRsDestination
.AddNew
.Fields("COMTURN_COM_ID").Value = iComID
.Fields("COMTURN_MONTH").Value = DateSerial(iYear,
iMonth, 1)
.Fields("COMTURN_LAST_UPDATE_DATE").Value = Date
.Fields("COMTURN_AMOUNT").Value =
objRsImport.Fields(2)
.Update
End With
End If
End If
objRsImport.MoveNext
Loop
'COMMIT OR ROLLBACK IMPORT
If objConnSQL.Errors.Count = 0 And Err.Number = 0 And iCountDoubles
< 10 Then
objConnSQL.CommitTrans
bTransactionActive = False
TRANSimportTurnoverMonthYear = True
If Len(gActionFailureText) > 0 Then gActionFailureText = vbCrLf
& "BUT" & vbCrLf & gActionFailureText
Else
objConnSQL.RollbackTrans
bTransactionActive = False
TRANSimportTurnoverMonthYear = False
End If

' Clear memory
objRsImport.Close
Set objRsImport = Nothing
objRsDestination.Close
Set objRsDestination = Nothing
objConnSQL.Close
Set objConnSQL = Nothing
objConnExcel.Close
Set objConnExcel = Nothing
'succes
DoCmd.Hourglass False
Exit Function

errHandling:
DoCmd.Hourglass False
If bTransactionActive Then objConnSQL.RollbackTrans
MsgBox Err.Number & " " & Err.Description
End Function
"John Marble" <fr******@gmail.com> wrote in message
news:28**************************@posting.google.c om...
I have around 400 excel files filled with data that I need to import
in ACCESS. The tricky part is that they must be imported one at time,
and properly corrected before importing the next one. I already
automated most of the formatting task of the raw data, and I am
wondering if it is possible to automate the original importation too
(like when I click on FILE>Import).

Anyone have an idea?

Nov 13 '05 #2
"Filips Benoit" <be***********@pandora.be> wrote in
news:_N**********************@phobos.telenet-ops.be:
This is a import-Function for excel-data but you can find several
examples on the net.


Why do this sequentially when you can link the table and use SQL,
just like with a native Access table?

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc
Nov 13 '05 #3
jr
Just use link tables
If there is any data that needs tidying up before the main processing
Create a set of queries which filter pout the source data rubbish and use
these queries as a platform for your genuine data processing or reporting
queries.

Finally you can create a form with click button to refresh the link tables
every time new data is needed for import.

A note of warning though - Link tables are cast in stone -You cant change
the data file source structure or format And your source files must always
reside in the same file path location.

"John Marble" <fr******@gmail.com> wrote in message
news:28**************************@posting.google.c om...
I have around 400 excel files filled with data that I need to import
in ACCESS. The tricky part is that they must be imported one at time,
and properly corrected before importing the next one. I already
automated most of the formatting task of the raw data, and I am
wondering if it is possible to automate the original importation too
(like when I click on FILE>Import).

Anyone have an idea?

Nov 13 '05 #4

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

3
by: deko | last post by:
So I've decided to convert from Early Binding to Late Binding. Now that I've been baptized, I need some instruction in the faith. My former ways were thus: Dim xlapp As Excel.Application Set...
1
by: Michael Wu | last post by:
I wonder if anyone can shed some light on how to automate Excel from a C# program. What I like to do is this, 1) Find if an Excel spreadsheet is alreay opened (identify the Excel file path) 2)...
11
by: David Lozzi | last post by:
Hello, I need to automate importation of a excel file into a table. Here's my scenario: I'm writing an ASP.NET application where users can pull reports on imported data. The imported data is...
4
by: Ivan | last post by:
Hi All, I have tried to automate excel in vb.net and i found a problem that i can't find the solution in anywhere... i hope someone can help me in this group.... the problem is i try using...
0
by: beary | last post by:
I am using php5 with mysql and also using excel 2003 running on winxp. Anyway, I am currently opening my excel.xls file, then saving as csv file, then closing, then copying it over to the web...
2
by: =?Utf-8?B?QWxleGFuZGVyIFd5a2Vs?= | last post by:
Is it possible to automate a COM object ebmeded in an excel document run the process and return the results in a C# .NET application? Or better yet extract the com object some how and just run it...
0
by: ishay44 | last post by:
Hello! I try to build (using Visual 2005 and Excel 2007) the example described in the Microsoft Help and Support "How to automate Excel from MFC and Visual C++ 2005 or Visual C++ .NET to fill or...
8
by: =?Utf-8?B?SmVycnkgQw==?= | last post by:
I have a web site that automates excel. This site runs in server 2003. iis 6.0 office 2003 installed I moved this app to server 2008 iis 7.0 office 2003 installed Now when I try to automate...
8
by: billelev | last post by:
I have a number of models that I need to run at regular times each week. The models may be based in Access, Excel or a math based statistics package called R. The models may also be a combination...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.