Home | Contact Us | FAQ | Search & Site Map | Link to Us
Sign In | Join | Other 45 Sites in Network
Home
Discussion GroupsFormsForms ProgrammingQueriesModules / DAO / VBAReports / PrintingMacrosDatabase DesignSecurityConversionImporting / LinkingSQL Server / ADPMultiuser / NetworkingReplicationSetup / ConfigurationDeveloper ToolkitsActiveX ControlsNew UsersGeneral 1General 2
Access DirectoryToolsTutorialsUser Groups
Related Topics
SQL ServerOther DB ProductsMS OfficeMore Topics ...

MS Access Forum / Modules / DAO / VBA / January 2006

Tip: Looking for answers? Try searching our database.

TransferSpreadsheet with range

Thread view: 
Enable EMail Alerts  Start New Thread
Thread rating: 
Ben - 09 Jan 2006 18:57 GMT
Im trying to import a spreadsheet where the column headings are on row 2 and
the data on row 3+. I have tried (with no luck) the following:

DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9,
"BindersTaken1", Directory, True, "Binders$2:65536"

The data is on the sheet called "binders" and i would like row 2 on ward
imported.

With the following code i was able to import the correct sheet but i needed
to add a range value to it:
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9,
"BindersTaken1", Directory, True, "Binders$"

Thanks for the help!
Ben
Dan Knight - 09 Jan 2006 19:46 GMT
Ben;
I'd suggest two routes:
First, if it's acceptable to modify the XL data, use VB to open an instance
of XL in your code and delete the first row prior to importing. CRUCIAL, if
you do this make sure you close your XL instance prior to exiting the sub.

Second, import the data as you've done then use a query or code to select
and delete the first record. If there's consistent data in that first row
that is not needed (ie: a Title) then use that as the criteria for your
deletion query.

Signature

Dan Knight

> Im trying to import a spreadsheet where the column headings are on row 2 and
> the data on row 3+. I have tried (with no luck) the following:
[quoted text clipped - 12 lines]
> Thanks for the help!
> Ben
fredg - 09 Jan 2006 20:36 GMT
> Im trying to import a spreadsheet where the column headings are on row 2 and
> the data on row 3+. I have tried (with no luck) the following:
[quoted text clipped - 12 lines]
> Thanks for the help!
> Ben

You did not correctly identify the Excel file path and file name. Nor
did you correctly identify the worksheet and range. I'll guess you
just want column A imported, not all columns.

Since I'm quite confused with your names, here is generic code.
Substitute your actual path, file, worksheet, and columns. Don't
forget the ! between the worksheet and range.

DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9,
"TableToImportInto", "CorrectPathToFolder\SpreadsheetName.xls", True,
"WorkSheetName!A2:A"

Note.. the path to the folder must include the drive number, i.e.
"c:\FolderName\FileName.xls".

The above will import data from the named worksheet, column A rows 2
onward (to the last row that contains data, not necessarily 65536).
The data in cell A2 will be the field name as you have written True
for that argument.

Signature

Fred
Please respond only to this newsgroup.
I do not reply to personal e-mail

 
Sign In
Join
My Latest Posts
My Monitored Threads
My Blog
My Photo Gallery
My Profile
My Homepage

Start New Thread
Enable EMail Alerts
Rate this Thread



©2008 Advenet LLC   Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.