Import from Excel Error

[croix18]

Hey Everybody,

I'm currently running Access 2002. What I am trying to accomplish is to "pass reporting off" to another person. I'm assuming they know very little of Access/Excel and therefore hope to automate 95% of the process. The problem is they have a report where it will always return some previous records (reason beyond me). Now, the last two columns are Null in the Excel file, inserted as Null by Crystal with an correct title. The Excel file will then be sent straight to the user, not me.

The last two columns/fields are text fields which are meant to be filled in by the user. Based on these two empty fields, I can determine which records are new and therefore are safe to delete. Does it matter if the fields are Null or should I set them up as "" if possible? Giving them a Default Value of "" in Table Design view doesn't change success of the import, it still fails.

The data coming in from Crystal is valid, but I have a problem when I try to import the data into Access from Excel. I get an 'External table is not in the expected format' error. My Excel file has one sheet, just going to be named Sheet1. The value in A1 is text, not blank. Currently I am using TransferSpreadsheet command using arguments below:

' Call encapsulated Open File box, then transfer spreadsheet.
ReportName = GetOpenFile(, "Select Scrub-Other File")
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel3, _
        "tblOther", ReportName, True

I have no idea what the difference between the Spreadsheet types are, but I have tried the default and worked down to type 3...still no different.

As far as I understand the TransferSpreadsheet method, the num of columns in Excel have to match the number fields so that's why the last two null coluumns are being inserted. Also, is there a bug with TransferSpreadsheet that when Excel is open and you call the method, it doesn't exit Excel properly?

Thx,

Mike

 

[croix18]

I have no idea what the difference between the Spreadsheet types are, but I have tried the default and worked down to type 3...still no different.

Might have missed one now that I'm looking at them, I think it was 4 or 5...going to try them both now, just in case.

 

[croix18]

Ok, got it to work on the Importing front, using acSpreadsheetTypeExcel5. Some documentation on what these mean would be GREAT, Microsoft.

However, I still can't get the Excel process to release from RAM. The END method doesn't seem to be working from this forum Excel process still running because I don't want to close the users other files. Any ideas?