How import Excel file and create a temp table with the imported Excel data.

I'm looking for advice on how to best to the following, preferably all within VBA coding.

1)  Browse for Excel file on user's desktop.
2)  Create temp Access table with the data in the imported Excel file.
Who is Participating?
Rey Obrero (Capricorn1)Connect With a Mentor Commented:
try this sample db
You could use PowerShell to do it:

Get-ChildItem will find your file on the desktop:
Get-ChildItem c:\users\[username]\desktop *.* -include [filename]

If the excel file is a .csv, you can then use the Import-csv powershell cmdlet to run whatever action you want on the data:

$file = Import-Csv [import filename]
foreach($line in $file)
          [enter your Access db code here]

Here's a link to examples of Access Powershell code, but without knowing exactly what you are trying to do, I can't even give you the pseudo code for it:
Boyd (HiTechCoach) Trimmell, Microsoft Access MVPCommented:
No need to make it more difficult with Powershell. There are built in functions to do what you want.

1) To select a file you canb use the Filedialog property

See this MSDN article on how to use:

2) To import he Excel file use the built-in DoCmd.TransferSpreadsheet Method

See this MSDN Article on how to use:

Boyd Trimmell, Microsoft Access MVP
aka HiTechCoach
SteveL13Author Commented:
Absolutely perfect!!!!  Thank you.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.