importing .csv and .xls files to access

[TheLost]

I'm importing .csv and .xls files into access using docmd.transferspreadsheet and it's simple enough except for a couple of issues I'm having:
1. the .csv file has leading ' and if I converti it .txt if has leading " in some of the columns .....what's the best way to get rid of these and is it better before or after the importing process.



2. a .xls file has a few rows of totals at the top so I need the importing to start at row A7 instead of A1...is there a way to control where it starts rather than manipulating the file prior to importing because it's all going to done by clicking a button on a form by user.

oh, and there's a 3. one of the .xls has additional blank rows that it seems to import into the access table....what's th solution for that.

All the files will vary in number of records on a daily basis so I can't specify range like the DoCmd.

Any help would be greatly appreciated!
Isabel.

[June7]

1. the import transferspreadsheet method can't eliminate characters so must 'clean up' before or after.

2. you can specify a range with transferspreadsheet method
http://msdn.microsoft.com/en-us/library/bb214134(v=office.12).aspx

3. must be something, even a space, in some cell on the row causing the row to import

[TheLost]

Do you know how to update the table column with an expression that would that would update the column with a replace ....to replace the "'" with ""?

[June7]

Try:

UPDATE Table1 SET Table1.test = Replace([test],"'","");

I just noticed your statement about the variability of rows.

VBA code could read the file and determine the last row with data and use that to define the range.

[TheLost]

ok, I think if I put that Update code in a query it might fix the problem.
I had already seen that info in the link you provided....seems like there should be an easier way to determine the end of the data.

[June7]

I don't see anything in that link that would programmatically determine the last row holding data. It does describe the syntax for the Range parameter but it does not determine what the last row of data is.