[AccessD] Excel & CSV File Import

Stuart,

Thanks for that. All works great, but I get a sub script out range error
when the line feed gets to a blank/null row. Any ideas on how I can handle
that?

Thanks
Vlad

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Stuart McLachlan
Sent: Thursday, 7 August 2003 8:24 PM
To: Access Developers discussion and problem solving; ACTEBS
Subject: Re: [AccessD] Excel & CSV File Import


On 7 Aug 2003 at 19:36, ACTEBS wrote:

> Hi List,
>  
> Has anyone got any code that can import Excel & CSV files 
> programmatically - not using transfer text/excel functions. The reason 
> I ask is that I have some unusually formatted spreadsheets whose 
> column names change from month to month and also the CSV file I need 
> to import all the info from about row 20 onwards.
>  
If you can save the Excel as CSV as well, you can just open them all 
for input, read them a line at a time and decide on how to handle 
each line.  I've got quite a few routines that do this for various 
sorts of input.

As long as you can identify lines either by line count or content of 
one of the fields, you can parse out the data you need and dump it 
into any table you want.

In it's simplest form, just do something like the following. You can build
whatever conditions you need (repeating line counters, 
string comparisons etc) in the "If...End If"  section to handle the 
particular format of the input file

Sub GetData(strInputFile as String)
	Dim strTemp as String
     Dim  strParsedData() as String
     Dim lngLinecount as Long
     Dim rs as DAO.Recordset

	Open strInputFile for Input as #1
     Set rs = CurrentDb.OpenRecorset("tblMyTable")
	While not EOF(1)
           lngLinecount = lngLinecount + 1
		Line Input #1, strTemp
           strParsedData = Split(strTemp,",")
           If............................    Then 
	           rs.Addnew 
      	      rs!Field1 =  strParsedData(1)  
           	 .....              
            	rs.Update
           End If
      Wend
	Close #1
End Sub

  
 
-- 
Lexacorp Ltd
http://www.lexacorp.com.pg
Information Technology Consultancy, Software Development,System 
Support.



_______________________________________________
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com