Hopefully this is an easy question- I just need to be pointed to the right command.
I have a database that needs to regularly import spreadsheets (they are already formatted correctly). I will be handing this database off to the folks here when I leave, and am trying to automate it as much as possible so that the non-Access folks (not that I am a programmer, but I know the basics fairly well) will be able to use it without any problems at all.
I could use DoCmd.TransferSpreadsheet to import a file with an input box to ask for a filename and location. However, the people who will be using this database probably won’t remember the name of the file they just created, nor will they know the path through the network to find where it is located. I can’t make it a standard location, as depending on the user and month the directory will change. Is there an easy way I can bring up the standard file locating browse window or dialogue box and pass the name/loc on to TransferSpreadsheet?
I’m not an Access programmer. However, the Windows SDK function that does this is called GetOpenFileName(). Does anything like that appear in the documentation? Do you have any way of calling the SDK directly?
You can assign the file name and directory location under the Docmd.TransferSpreadsheet. There are numerous qualifiers after the command itself. Go into your form and type Docmd.transferspreadsheet, when you hit the spacebar afterwards, there will pop up the qualifiers you can use for that command. You will probably have to create some variables to store the correct location, filename, then plug them into the Docmd. line at execution. Check the help files under docmd to learn more. I think what you are trying to do is relatively straightforward.
To call the windows Open/Save dialog box, you need to use an API. Go here and copy the text from the gray box into a new module. When you paste the text, you’ll notice that the line breaks don’t all work, so you’ll have to fix them.