Import/export data & selecting file location

[David]

Hi

I'm using this code to import data from an excel spreadsheet:

DoCmd.TransferSpreadsheet , acSpreadsheetTypeExcel9, "tblStudents", _
"\\Egusersrv\Staff - Shared Work\PE Teach & Report\Imported
Spreadsheets/ImportStudents.xls", True

Which works fine if the spreadsheet is always in the pre-defined
location. Ideally what I would prefer to do is have the user locate
the file themselves, with perhaps the system defaulting to a folder
initially when the dialogue window opens. I would also like to apply
the same process to an export procedure from Access into an Excel
template too. Grateful for any help.

regards

David

[Allen Browne]

Call the Windows FileOpen/Save dialog.

Details in:
Call Windows File Open/Save Dialog box
at:
http://www.mvps.org/access/api/api0001.htm

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"David" <de********@gmail.comwrote in message
news:12**********************************@m73g2000 hsh.googlegroups.com...

>
I'm using this code to import data from an excel spreadsheet:

DoCmd.TransferSpreadsheet , acSpreadsheetTypeExcel9, "tblStudents", _
"\\Egusersrv\Staff - Shared Work\PE Teach & Report\Imported
Spreadsheets/ImportStudents.xls", True

Which works fine if the spreadsheet is always in the pre-defined
location. Ideally what I would prefer to do is have the user locate
the file themselves, with perhaps the system defaulting to a folder
initially when the dialogue window opens. I would also like to apply
the same process to an export procedure from Access into an Excel
template too. Grateful for any help.

regards

David

[David]

On 14 Jul, 14:48, "Allen Browne" <[e-mail]:

Call the Windows FileOpen/Save dialog.

Details in:
* * Call Windows File Open/Save Dialog box
at:
* *http://www.mvps.org/access/api/api0001.htm

--
Allen Browne - Microsoft MVP. *Perth, Western Australia
Tips for Access users -http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"David" <[e-mail] in message

news:12**********************************@m73g2000 hsh.googlegroups.com...

I'm using this code to import data from an excel spreadsheet:

DoCmd.TransferSpreadsheet , acSpreadsheetTypeExcel9, "tblStudents", _
"\\Egusersrv\Staff - Shared Work\PE Teach & Report\Imported
Spreadsheets/ImportStudents.xls", True

Which works fine if the spreadsheet is always in the pre-defined
location. *Ideally what I would prefer to do is have the user locate
the file themselves, with perhaps the system defaulting to a folder
initially when the dialogue window opens. *I would also like to apply
the same process to an export procedure from Access into an Excel
template too. *Grateful for any help.

regards

David- Hide quoted text -

- Show quoted text -

Hi- I really don't want to appear lazy but I have looked at that
already and it doesn't make much sense, sorry my programming skills
are basic and I can't see how I tailor the code I already have to the
great long routine listed there! Do I need to copy and paste it into
a module and then call the module in my form code, some how connecting
them together??

[Allen Browne]

API calls can look frightening if you've not used them before. This is
largely copy'n'paste stuff through, and it really is worth your time to get
this to work. If you do much with Access, you will need the functionality
again soon.

1. In Access, open the code window (Ctrl+G.)

2. Choose New Module on the Insert menu.
Access opens a code window.

3. Copy everything from the webpage from the Code Start to the Code End
lines, and paste into your new module.

4. Save the module with a name such as Module1.

5. To ensure Access understands the code, choose Compile on the Debug menu.

6. To test it, open the Immediate Window (Ctrl+G), and enter:
? TestIt()

You can then adapt the 2 examples at the top of the page - i.e.
ahtAddFilterItem() and ahtAddFilterItem() - to suit your needs.

My experience is that this is the only reliable way to do this in Access.
Other approaches fail when you need a new file name, fail when you switch
versions, require extra libraries, fail on different versions of Windows, or
just don't work properly.

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"David" <de********@gmail.comwrote in message
news:e1**********************************@59g2000h sb.googlegroups.com...
On 14 Jul, 14:48, "Allen Browne" <[e-mail]:

Call the Windows FileOpen/Save dialog.

Details in:
Call Windows File Open/Save Dialog box
at:
http://www.mvps.org/access/api/api0001.htm

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users -http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"David" <[e-mail] in message

news:12**********************************@m73g2000 hsh.googlegroups.com...

I'm using this code to import data from an excel spreadsheet:

DoCmd.TransferSpreadsheet , acSpreadsheetTypeExcel9, "tblStudents", _
"\\Egusersrv\Staff - Shared Work\PE Teach & Report\Imported
Spreadsheets/ImportStudents.xls", True

Which works fine if the spreadsheet is always in the pre-defined
location. Ideally what I would prefer to do is have the user locate
the file themselves, with perhaps the system defaulting to a folder
initially when the dialogue window opens. I would also like to apply
the same process to an export procedure from Access into an Excel
template too. Grateful for any help.

regards

David- Hide quoted text -

- Show quoted text -

Hi- I really don't want to appear lazy but I have looked at that
already and it doesn't make much sense, sorry my programming skills
are basic and I can't see how I tailor the code I already have to the
great long routine listed there! Do I need to copy and paste it into
a module and then call the module in my form code, some how connecting
them together??

[David]

On 14 Jul, 15:07, "Allen Browne" <[e-mail]:

API calls can look frightening if you've not used them before. This is
largely copy'n'paste stuff through, and it really is worth your time to get
this to work. If you do much with Access, you will need the functionality
again soon.

1. In Access, open the code window (Ctrl+G.)

2. Choose New Module on the Insert menu.
Access opens a code window.

3. Copy everything from the webpage from the Code Start to the Code End
lines, and paste into your new module.

4. Save the module with a name such as Module1.

5. To ensure Access understands the code, choose Compile on the Debug menu.

6. To test it, open the Immediate Window (Ctrl+G), and enter:
* * ? TestIt()

You can then adapt the 2 examples at the top of the page - i.e.
ahtAddFilterItem() and ahtAddFilterItem() - to suit your needs.

My experience is that this is the only reliable way to do this in Access.
Other approaches fail when you need a new file name, fail when you switch
versions, require extra libraries, fail on different versions of Windows,or
just don't work properly.

--
Allen Browne - Microsoft MVP. *Perth, Western Australia
Tips for Access users -http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"David" <[e-mail] in message

news:e1**********************************@59g2000h sb.googlegroups.com...
On 14 Jul, 14:48, "Allen Browne" <[e-mail]:

Call the Windows FileOpen/Save dialog.

Details in:
Call Windows File Open/Save Dialog box
at:
http://www.mvps.org/access/api/api0001.htm

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users -http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"David" <[e-mail] in message

news:12**********************************@m73g2000 hsh.googlegroups.com....

I'm using this code to import data from an excel spreadsheet:

DoCmd.TransferSpreadsheet , acSpreadsheetTypeExcel9, "tblStudents", _
"\\Egusersrv\Staff - Shared Work\PE Teach & Report\Imported
Spreadsheets/ImportStudents.xls", True

Which works fine if the spreadsheet is always in the pre-defined
location. Ideally what I would prefer to do is have the user locate
the file themselves, with perhaps the system defaulting to a folder
initially when the dialogue window opens. I would also like to apply
the same process to an export procedure from Access into an Excel
template too. Grateful for any help.

regards

David- Hide quoted text -

- Show quoted text -

Hi- I really don't want to appear lazy but I have looked at that
already and it doesn't make much sense, sorry my programming skills
are basic and I can't see how I tailor the code I already have to the
great long routine listed there! *Do I need to copy and paste it into
a module and then call the module in my form code, some how connecting
them together??- Hide quoted text -

- Show quoted text -

Allen, many thanks I will try this- I just needed the Dummies
introduction, which you have just given me!