Importing spreadsheet data to an Access Table

[Guest]

Function Trnsfrdata()
Dim XL As Object

Set XL = CreateObject("Excel.Application")

XL.Workbooks.Open "H:\Feesdownload\FXDH.xls"

DoCmd.TransferSpreadsheet acImport, _ acSpreadsheetTypeExcel7,
"DealsDataNew", _
"H:\FeesDownload\FXDH.xls", HasFieldNames = Yes, "Sheet1!E:O"
End Function

I need help in setting the above procedure to only copy rows with data. The
procedure that runs prior to this one gathers data from a non-Microsoft
database, modifies the data to be compatible with the Access Table and calls
an Access Macro to run this code. (The call to Access is still in the works.)


The problem I run into is that on one occasion there might be a dozen rows
of data and on another a couple hundred rows. I do have some fields with
"required" set to "Yes" so only the rows with data will be pasted, but the
"Unable to Append all the data to the Table" error message pops up and I need
to avoid that.

Is there a way in this instance to have the code limit the copy for pasting
to rows with actual data?

Thanks,

Jim

 

[Guest]

You are mixing two different ways of doing things here. First, you do not
need to establish an Excel Object to use the TransferSpreadsheet method.
Second, you cannot do any filtering with the TransferSpreadsheet method other
than to define a range.

I think your best bet would be to link to the spreadsheet rather than import
it, then create a query to copy the data to your Access table. You can
filter out blank rows in the query.

 

[Guest]

That's a great idea. So simple I could not see it (as usual). ha!

Thanks,

Jim