Hi,

I have researched importing from Excel in other posts but I am still having problems I can't solve.

I have this code in the onclick procedure of a command button;



Sub Command0_Click()


Dim strFilter As String
Dim strInputFileName As String

strFilter = ahtAddFilterItem(strFilter, "Excel Files (*.XLS)", "*.XLS")
strInputFileName = ahtCommonFileOpenSave( _
Filter:=strFilter, OpenFile:=True, _
DialogTitle:="Please select an input file...", _
Flags:=ahtOFN_HIDEREADONLY)

Dim YourPath As String

DoCmd.TransferSpreadsheet acImport, , "allaccounts", YourPath, True


End Sub



When I click on the button it opens up the dialog box for me to select the file to import, however when I select the file and click open I get;

runtime error 2522,
The action or method requires a file name argument.

The debug shows this is a problem with my doCmd line. I am trying to import into an existing table named "allaccounts". Any ideas where I am going wrong?

Your Docmd.Transfspreadsheet command doesn't specify which file you are importing from.

Your code never sets the value of YourPath variable. It does set the value of strInputFileName (I believe your code also will open the selected file, which I don't believe you want to do -- see the OpenFile argument in the ahtCommonFileOpenSave function call), so either use that variable in the TransferSpreadsheet call, or else set its value to YourPath variable.

I now remember why I have given up learning VBA so many times! This is hard work!

My code is now;

Sub Command0_Click()


Dim strFilter As String
Dim strInputFileName As String

strFilter = ahtAddFilterItem(strFilter,
strInputFileName = ahtCommonFileOpenSave( _
Filter:=strFilter, OpenFile:=True, _
DialogTitle:="Please select an input file...", _
Flags:=ahtOFN_HIDEREADONLY)

Dim YourPath As String
YourPath = strFilter

DoCmd.TransferSpreadsheet acImport, , "allaccounts", YourPath, True



Does this make sense?
When I select the file now I get the error;

Run Time error 3011
The Microsoft Jet Database Engine could not find the object 'D:\PersonalData\ExcelFiles (*.XLS)'
Make sure the object exists and that you spell it's name and path correctly

I figure this is referring to this part of code,

"Excel Files (*.XLS)", "*.XLS")

and that this code is making it look for files named "ExcelFiles *.XLS" when actually I am just asking it to display these file types. But i'm not sure how to change this part of the code. I don't mind if it shows all file types.

Change this line of code:

YourPath = strFilter


to this:

YourPath = strInputFileName

Is that all the code?

Thanks so much.

Just so I know for the future, when I am defining YourPath as a variable why would I refer to strInputFileName and not strFilter?

And as strInputFileName was already defined as a variable could I just not have referred to this in the DoCmd as below?

DoCmd.TransferSpreadsheet acImport, , "allaccounts", strInputFileName, True

I am presuming that by defining YourPath as a variable all I am doing is basically making
YourPath = strInputFileName

These probably seem stupid questions but I am desperate to understand this better!

If you declare something as a variable it's empty/0 until you set it a value.

As Ken pointed out you didn't set a value for YourPath in your code.

As to why you should use strInputFileName instead of strFilter that's because this piece of code
gets a filename and puts it in strInputFileName.

What you probably should do is replace YourPath here with strInputFileName.

Yes, you can use strInputFileName in the TransferSpreadsheet code line.

Thanks. That makes it clearer for next time!