transfer spreadsheet

[ajetrumpet]

Folks,

I need to use the transfer spreadsheet function to get an unknown amount of rows from Excel over into an existing table in Access. I am getting this data off of the internet, into a CSV, and then over to Access that way.

I need to use code similar to the .xlDown syntax in Excel (in place of the range I have specified). Can I write that with the function? Does Access read it?

Here are the steps I got right now. If it's not the best, please suggest how I can improve the process. thanks!

Dim strTABLE As String

strTABLE = "my" & MonthName(Month(Date)) & "transactions"

  DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12, "mymarchtransactions", _
                            "mymarchtransactions.csv", no, [u][color=red][B]"A2:D100"[/B][/u][/color]

DoCmd.SetWarnings False

    DoCmd.RunSQL "INSERT INTO tblzone (transactiondate, Postingdate, transactiondescription, amount) " & _
                 "SELECT [f1], [f2], [f3], [f4] FROM " & strTABLE

DoCmd.SetWarnings True

      CurrentDb.TableDefs.Delete (strTABLE)

 

[chergh]

Use the address property of the range object to return the range you want to import.

 

[ajetrumpet]

I don't think I followed that Chergh....

Should I experiment with this in Excel or can you give me a syntax hint here...???

 

[chergh]

Create a spreadsheet with some data in the first row and then run the following code in the immediate window.

debug.Print thisworkbook.Worksheets("Sheet1").range("A1").end(xltoright).address(RowAbsolute:=False, columnabsolute:=false)

 

[namliam]

You dont need to specify a range... if you dont, access will simply import all

 

[ajetrumpet]

Thank you chergh.

Namliam,

sometimes I can't import all, because the first row will contain column headings (sometimes), and I don't want those...

 

[qdogfball]

AJE,

Can you setup a dynamic named range in Access like in Excel?

http://www.contextures.com/xlNames01.html

=OFFSET($A$1,0,0,COUNTA($A:$A),1)

I am doing the opposite here, coming from Access to a pivot table in Excel

 

[namliam]

Namliam,

sometimes I can't import all, because the first row will contain column headings (sometimes), and I don't want those...

When an excel sheet contains headers I allways import into a staging table to "circumvent" the headers while storing the data into a table.
Then append to my final table, this prevents a lot of mess.