Import a specific Excel worksheet in Access using VBA

[rndmxy2k13]

Hi everybody,

I m trying to import an Excel worksheet which is not the first thats being displayed in the overview into Access.
When I try running my VBA code its telling me that I ve entered an expression which does not have the required data type for the argument.

My Code:

Code:

Option Compare Database '------------------------------------------------------------ ' AutQry ' '------------------------------------------------------------ Function AutQry() On Error GoTo AutQry_Err DoCmd.TransferSpreadsheet acImport, 10, "richtigerpfad", True, "Tabelle2!", "" DoCmd.OpenForm "Auswahlabfrage", acNormal, "", "", , acNormal AutQry_Exit: Exit Function AutQry_Err: MsgBox Error$ Resume AutQry_Exit End Function

What am I doing wrong?

[JoeM]

You need a File Name argument with a full path and file name.
See: http://msdn.microsoft.com/en-us/libr...ffice.12).aspx

[rndmxy2k13]

You need a File Name argument with a full path and file name.
See: http://msdn.microsoft.com/en-us/libr...ffice.12).aspx

Sorry I forgot to translate this part, "richtigerpfad" actually means "correct path".
I wrote this so I wouldn t have to copy the whole path and forgot to translate it before posting here , the path is actually correct
Still, I cannot chose the particular worksheet I want to import data from.

Thanks for the quick response anyhow!

[JoeM]

I often use Named Ranges. If you name the range you want to import in Excel, you can then use that named range in the Range argument.

[rndmxy2k13]

Thank you for your quick responses, naming ranges is a good idea, but doesnt really seem to solve my problem.
I'm trying to choose the second worksheet as shown in the picture:
Of course I could just just switch the two sheets and have it imported with the standard command but I'm trying to get rid of this step to fasten the overall import process.
Any ideas?

[rndmxy2k13]

Solved the issue, I had to use the executecode action and put "public" in front of the function name within my VBA module.

[JoeM]

Thank you for your quick responses, naming ranges is a good idea, but doesnt really seem to solve my problem

Did you try it? If you used named ranges, it doesn't matter what sheet the range is on, you do not need to include a sheet reference in your range, nor does it need to be the first sheet when the file is opened. Simply reference the named range.
Try it, you'll see!

[rndmxy2k13]

Ok JoeM you're right, I just tried it and it worked.
Thanks for the help