Import / File Open dialog box for Access

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Microsoft should add an automated file open dialog box object for importing
files into Access database. I was looking for a way to do this and when I
asked on Office Discussion Group site, not only did I get VBA code, but I
found out that alot of programmers had the same question. A built in object
/ function would be very heklpful.

Thanks
 
They did. In Access 2003 (I think also in Access 2002, but I'm not certain
about that) the Access Application object has a FileDialog method. Of
course, it only works with the most recent version(s) of Access, and doesn't
work with the runtime, so most of us still use the API code. Unless you're
developing in-house applications for an organisation that has standardised
on a recent version of Office, there are just too many users of Access 2000
out there to use features that were not supported in that version.

--
Brendan Reynolds (MVP)
http://brenreyn.blogspot.com

The spammers and script-kiddies have succeeded in making it impossible for
me to use a real e-mail address in public newsgroups. E-mail replies to
this post will be deleted without being read. Any e-mail claiming to be
from brenreyn at indigo dot ie that is not digitally signed by me with a
GlobalSign digital certificate is a forgery and should be deleted without
being read. Follow-up questions should in general be posted to the
newsgroup, but if you have a good reason to send me e-mail, you'll find
a useable e-mail address at the URL above.
 
It's in ACCESS 2002 too. But it requires that your database file have a
reference set to Office 10 object library in order to use it, based on my
experience -- in addition to the other things noted by Brendan.

I've "wished" that ACCESS get the same built-in functions that EXCEL has --
GetOpenFileName and GetSaveAsFileName -- which would be wonderul.... maybe
in the next ACCESS?
 
Can you share the code with me? I am trying to allow user to attach a file
or import a file into access. I am not sure how to do that. Thank you.

John
 
I am using the below code to open my dialog box and then it populates my
listbox. I then am using a macro to export a query to that location.
Question is, how do I name the excel file I export? I am currently using the
TransferSpreadsheet and the File Name I am using is what is populated in my
list box. In other words I have
[Forms]![frmReportingDatasheetView]![FileList] in the "File Name" portian of
my TransferSpreadsheet function. When I export my query the file will go to
the chosen folder but will be named
"[Forms]![frmReportingDatasheetView]![FileList].xls"
Is there anyway to make the name something useful?
I am also having the same naming problem when I import.
Do I need to convert this to a module?
Thanks!!
 
Use an expression in the FileName portion so that it uses the value from the
FileList control (note the = sign at the beginning):

=[Forms]![frmReportingDatasheetView]![FileList]


consjoe said:
I am using the below code to open my dialog box and then it populates my
listbox. I then am using a macro to export a query to that location.
Question is, how do I name the excel file I export? I am currently using
the
TransferSpreadsheet and the File Name I am using is what is populated in
my
list box. In other words I have
[Forms]![frmReportingDatasheetView]![FileList] in the "File Name" portian
of
my TransferSpreadsheet function. When I export my query the file will go
to
the chosen folder but will be named
"[Forms]![frmReportingDatasheetView]![FileList].xls"
Is there anyway to make the name something useful?
I am also having the same naming problem when I import.
Do I need to convert this to a module?
Thanks!!
Click to expand...
<snipped >
 
Thanks Ken.
That makes since but I have another question now. The "=" worked but it
would only return a folder path not the entire file ("C:\Desktop") instead of
("C:\Desktop\SearchResults.xls") so I added "\SearchResults.xls" to the
additem line from the below code like this... Me.FileList.AddItem varFile +
"\SearchResults.xls"
So I have a button running the below code that populates my listbox field
and GoesToControl Filelist (my listbox). I then have a macro that exports
the query to the location listed in the listbox. I am calling the macro "On
Got Focus" of the list box. However the macro only exports the file part of
the time. So I am assuming that it is not always getting called. I know the
"On Got Focus" probably isnt the best place to put it so I tried calling it
at the end of the below code but couldn't get that to work right either.
Any ideas as to make this macro run everytime.
Thanks Again.

Ken Snell (MVP) said:
Use an expression in the FileName portion so that it uses the value from the
FileList control (note the = sign at the beginning):

=[Forms]![frmReportingDatasheetView]![FileList]


consjoe said:
I am using the below code to open my dialog box and then it populates my
listbox. I then am using a macro to export a query to that location.
Question is, how do I name the excel file I export? I am currently using
the
TransferSpreadsheet and the File Name I am using is what is populated in
my
list box. In other words I have
[Forms]![frmReportingDatasheetView]![FileList] in the "File Name" portian
of
my TransferSpreadsheet function. When I export my query the file will go
to
the chosen folder but will be named
"[Forms]![frmReportingDatasheetView]![FileList].xls"
Is there anyway to make the name something useful?
I am also having the same naming problem when I import.
Do I need to convert this to a module?
Thanks!!
Click to expand...
<snipped >
Click to expand...
 
To combine a string path with a value from a form's control, this is what I
use in a macro:

="C:\Desktop\" & [Forms]![frmReportingDatasheetView]![FileList]

It appears, though, that you're not using a macro but are using VBA code
(they're not the same in ACCESS). Assuming that you're using VBA to run the
TransferSpreadsheet method, use the Click event of the button and then this
would be the syntax for the TransferSpreadsheet part:

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9,
"TableOrQueryName", "C:\Desktop\" &
[Forms]![frmReportingDatasheetView]![FileList]

--

Ken Snell
<MS ACCESS MVP>



consjoe said:
Thanks Ken.
That makes since but I have another question now. The "=" worked but it
would only return a folder path not the entire file ("C:\Desktop") instead
of
("C:\Desktop\SearchResults.xls") so I added "\SearchResults.xls" to the
additem line from the below code like this... Me.FileList.AddItem
varFile +
"\SearchResults.xls"
So I have a button running the below code that populates my listbox field
and GoesToControl Filelist (my listbox). I then have a macro that exports
the query to the location listed in the listbox. I am calling the macro
"On
Got Focus" of the list box. However the macro only exports the file part
of
the time. So I am assuming that it is not always getting called. I know
the
"On Got Focus" probably isnt the best place to put it so I tried calling
it
at the end of the below code but couldn't get that to work right either.
Any ideas as to make this macro run everytime.
Thanks Again.

Ken Snell (MVP) said:
Use an expression in the FileName portion so that it uses the value from
the
FileList control (note the = sign at the beginning):

=[Forms]![frmReportingDatasheetView]![FileList]


consjoe said:
I am using the below code to open my dialog box and then it populates my
listbox. I then am using a macro to export a query to that location.
Question is, how do I name the excel file I export? I am currently
using
the
TransferSpreadsheet and the File Name I am using is what is populated
in
my
list box. In other words I have
[Forms]![frmReportingDatasheetView]![FileList] in the "File Name"
portian
of
my TransferSpreadsheet function. When I export my query the file will
go
to
the chosen folder but will be named
"[Forms]![frmReportingDatasheetView]![FileList].xls"
Is there anyway to make the name something useful?
I am also having the same naming problem when I import.
Do I need to convert this to a module?
Thanks!!
Click to expand...
<snipped >
Click to expand...
Click to expand...
 
Back
Top