TransferSpreadsheet Question?

[BennyWong]

Hello All,
I am using Access 2000. I am using the feature TransferSpreadsheet and if the path of the spreadsheet is
present then the database works fine. If the spreadsheet
path is not the same I get the VBA Run-time error as such:

Run-time error '3011'
the Microsoft Jet database engine could not find the object
'C:\TBLCLINETBLANK.XLS'. Make sure the object exists and that
you spell its name and the path name correctly.

How can I fix this problem with a message for the user to
ensure that the spreadsheet path is present at the designated
location. Thanks in advance for assistance.

 

[kewo99]

Try this:


If Dir("C:\TBLCLINETBLANK.XLS&quot= "" then
msgbox("The file is not at ...........&quot
Else
<your normal processing code goes here>
End IF

Ken

 

[BennyWong]

Hello Ken,
Thanks for your response. I tried it and is working just great! I having a different problem in that when the
user enters data into the spreadsheet and skips any number of lines when I import the spreadsheet into the database it
carries over the number of lines. How can I just have only the data and not the added skipped lines?
Thanks for your assistance and quick response in advance.

 

[kewo99]

You could use a delete query to remove the blank records after they have been imported.

Delete * From <tablename> Where <Fieldname> is Null;

Ken

 

[BennyWong]

Hello Ken,
Thank you so much for your response. You're right I could have a delete query to clear the null field on the table. Why couldn't I have thought of it. Still a newbie?
Again, I appreciate your quick response and Thank you very much for your time and assistance.

Benny

 

[marlun]

Hi kewo99!
How do I write the If command when I have two files that needs to be checked for filepath or names.
I mean something like
If Dir(&quot;C:\TBLCLINETBLANK.XLS&quot; OR &quot;C:\TBLCLINETBLANK2.XLS&quot; )= &quot;&quot; then
How do i write so that it checks that both files are correct when I press the actionbutton??

thanks

 

[kewo99]

Hi Marlun,

Since I do alot of checking for the existance of files, I use a simple User Defined Function called FileExists() which returns True if the file exists and False if not.

Function FileExists(PathFile as String) as Boolean
FileExists = len(Dir(PathFile))>0
End Function

Using this for your example to check for 2 files:

If FileExists(&quot;C:\TBLCLINETBLANK.XLS&quot And FileExists(&quot;C:\TBLCLINETBLANK2.XLS&quot Then ......


Ken