Handling null values on import from excel

[Tadynn]

Hi All,

I'm wondering if anyone can help me.

I've written a database that imports data from various excel spreadsheets over a network daily. While the import process works okay, I'm having problems with blank lines being imported as well.

I have one of my excel files with the following:

Excel File Name: Exceptions Log
Spreadsheet Name: LogData
Total Area: A1:I50
Headers: True

With the code I currently have (see below), I have to open up the spreadsheet, find the last row of data and define that in my "area" text box on my form in access (i.e A1:I23)
because row A24:I50 are blank.

Is there a way I can modify my code so that I can still look at the total area (A1:I50) and only import the rows that have data?


Specs on the textboxes below:

Path: Path and file name of excel spreadsheet importing
Area: Area on spreadsheet to import
Table: Table importing to
Access Version: 2000/02

Private Sub Command46_Click()

Dim Path As String
Dim Area As String
Dim Table As String
Dim DBSource As String

Me.Path.SetFocus
Path = Me.Path.Text
Me.TableName.SetFocus
Table = Me.TableName.Text
Me.Area.SetFocus
Area = Me.Area.Text
Me.DBSource.SetFocus
DBSource = Me.DBSource.Text

Select Case [ImportType]

Case "MS Excel"

DoCmd.TransferSpreadsheet acImport, 8, Table, Path, True, Area

Case "MS Access"

DoCmd.TransferDatabase acImport, "Microsoft Access", Path, acTable, DBSource, Table, False

End Select
End Sub

Just another note on the code if it's relevant, I'm going to add another case statement so that I can import text files (next project).


Thanks in advance

Tadynn

 

[oharab]

You could use automation to open excel, open the file, count the rows and work out the range, then close the file, exit Excel, then import, but it would be easier & probably quicker to just import everything, then run a delete query afterwards to remove the null records.

hth

ben

----------------------------------------------
Ben O'Hara

http://www.RobotParade.co.uk

"Where are all the stupid people from...
...And how'd they get so dumb?"
NoFX-The Decline
----------------------------------------------

 

[DBAMJA]

Just a thought that might work.

As long as the path and file name doesn't change you could set the spreadsheet as a linked table and just to an append to your data and it will only look at the rows that have data in them.



DBAMJA

It is said that God will give you no more than you can handle. I just wish God didn't have so much faith in me.

 

[Tadynn]

Thank you for your responses oharab and DBAMJA. Initially I had the spreadsheets linked, but was trying to steer clear of that because I thought that linking was a bit messy. Because the data doesn't always begin at the top row (A1) in the spreadsheet, but I probably could name the data range and do it that way. Which would probably give me more control over the data validation by running it through a query and then import to the table once validation is complete.

Rgrds,
Tadynn