Go Back   Access World Forums > Microsoft Access Discussion > Modules & VBA

 
Reply
 
Thread Tools Rating: Thread Rating: 2 votes, 5.00 average. Display Modes
Old 12-20-2010, 02:40 PM   #1
ianverrier
Newly Registered User
 
Join Date: Dec 2010
Posts: 3
Thanks: 0
Thanked 0 Times in 0 Posts
ianverrier is on a distinguished road
TransferSpreadsheet Non-continuous ranges

I am trying to import a large spreadsheet into access with the following code:

DoCmd.TransferSpreadsheet acImport, , "import_" & worksheet_list!worksheet, path & "\input_files\" & rnc_list!RNC & ".xls", True, worksheet_list!worksheet & "!A:HA"

But I get this error: search key not found


But it works with the range: A:GV and A:HA so there is nothing wrong with the field names. So I assume it is over the max number of fields.

I need field A and HA in the same table since A is used to identify the record.

To solve this problem I want to import 2 ranges into the same table but I can't figure out the syntax. (eg. A:AG and CA:HA)

I tried
!A:AG,CA:HA
!(A:AG,CA:HA)

If this is not posiible is there another way to import tables with too many field names when the first field need to be present to identy which record the rest of the fields are attached to.

Thanks
Ian

ianverrier is offline   Reply With Quote
Old 12-20-2010, 02:54 PM   #2
boblarson
Smeghead
 
boblarson's Avatar
 
Join Date: Jan 2001
Location: Oregon, USA
Posts: 32,068
Thanks: 97
Thanked 1,828 Times in 1,579 Posts
boblarson is a splendid one to behold boblarson is a splendid one to behold boblarson is a splendid one to behold boblarson is a splendid one to behold boblarson is a splendid one to behold boblarson is a splendid one to behold boblarson is a splendid one to behold
Re: TransferSpreadsheet Non-continuous ranges

You can't use Transferspreadsheet like that. You would need to use Excel Automation to do it.

But why not just import the whole thing to a transitional table and then just append the columns you want to the live table. Then clear the transitional table for the next use.
__________________

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
boblarson is offline   Reply With Quote
Old 12-20-2010, 03:17 PM   #3
ianverrier
Newly Registered User
 
Join Date: Dec 2010
Posts: 3
Thanks: 0
Thanked 0 Times in 0 Posts
ianverrier is on a distinguished road
Re: TransferSpreadsheet Non-continuous ranges

Thanks for the response. I can't import the whole spreadsheet to a transitional table beacause it has too many fields.

ianverrier is offline   Reply With Quote
Old 12-20-2010, 03:37 PM   #4
boblarson
Smeghead
 
boblarson's Avatar
 
Join Date: Jan 2001
Location: Oregon, USA
Posts: 32,068
Thanks: 97
Thanked 1,828 Times in 1,579 Posts
boblarson is a splendid one to behold boblarson is a splendid one to behold boblarson is a splendid one to behold boblarson is a splendid one to behold boblarson is a splendid one to behold boblarson is a splendid one to behold boblarson is a splendid one to behold
Re: TransferSpreadsheet Non-continuous ranges

Quote:
Originally Posted by ianverrier View Post
Thanks for the response. I can't import the whole spreadsheet to a transitional table beacause it has too many fields.
Then Excel Automation code is the only way for you.

Code:
Function TestExceladfd()
    Dim objXL As Object
    Dim xlWB As Object
    Dim xlWS As Object
    Dim strTempPathAndFileName As String
    Dim strPathAndFileName As String
    Const xlToLeft As Long = -4159
 
    Set objXL = CreateObject("Excel.Application")
    objXL.Visible = True

    strPathAndFileName = "C:\Somefolder\Somefile.xls"
    strTempPathAndFileName = "C:\SomeOtherFolder\SomeOtherFileName.xls"
 
    Set xlWB = objXL.Workbooks.Open(strPathAndFileName)
   ' saves the workbook as another file so we can delete unwanted columns
    xlWB.SaveAs strTempPathAndFileName

    xlWB.Close

    Set xlWB = objXL.Workbooks.Open(strTempPathAndFileName)

    Set xlWS = xlWB.Worksheets("SheetNameHere")
 
    ' selects and removes the extraneous columns
    xlWS.Columns("AH:BZ,").Select
    objXL.Selection.Delete Shift:=xlToLeft
 
    xlWB.Save
    xlWB.Close
    objXL.Quit
 
    Set objXL = Nothing

    DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "TableName", strTempPathAndFileName, True
 
    If Dir(strTempPathAndFileName) <> "" Then
        Kill (strTempPathAndFileName)
    End If
 

End Function
So this opens the workbook, then copies it, by using a save as, to another location and then deletes the unwanted columns (I didn't include any that might be after column CH so if so you will need to modify).

Then it will save the temp file, do the import, and then deletes the temp file.

Hopefully this will help.

__________________

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
boblarson is offline   Reply With Quote
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Continuous Form Calculation - Multiple Date Ranges mbw85 Forms 1 07-11-2008 07:05 PM
A continuous form and continuous subform, possible? steve ferry Forms 8 08-10-2005 03:20 AM
Continuous Subforms within Continuous Forms CAS Forms 3 02-09-2002 08:32 PM
Ranges.. redblaze Queries 1 01-20-2002 09:05 AM
Continuous subform on continuous mainform Rachael Forms 0 12-09-2001 06:55 PM




All times are GMT -8. The time now is 12:30 AM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World