RE: Importing Data for use in report

Tech-Archive recommends: Fix windows errors by optimizing your registry

Klatuu,

Not a problem, I do tend to ramble on sometimes....
I am importing .TXT files....and for this particular database they will
always be .TXT's... Once the .TXT is imported I have a report that is based
on the fields of the TXT to print out my info.... Currently after the Data is
imported I open the report in Design and change the record source to the name
of the newly imported file, the fields are always the same. In all of this
can I include something that will update that report to use whatever file is
choosen to import ??? Or, is that leading to a whole new problem ??? Also,
nothing showed red in the Code, so, I think I have it !!!

Aaron

"Klatuu" wrote:

The lines in green with ' at the beginning are comments. They have no
effect on the code. Comments are good things.

As long as you don't see any lines in red, you should be good to go.

I sort of got lost in the thread, but if you can remind me, I can post a
version of the GetOpenFile function that will work for you.

Are you importing or exporting?
What is the file extension, or does it matter?
The only reason to specify a file extension is to make it easier for the
user to find a file in a directory.


"Graveyardblues" wrote:

Klatuu,

You Rock !!! Thanks for all of your help !!!
I opened that link & copied from right below the ****Code Start***** until
the bottom, End Function ??? It appears there are notes written in green
with ' before them ??? Will those affect this code ?? and, am I even
copying the correct Code ??? I think so ??? I saved the module as
modCommonDialog on my end....
Really appreciate your help....

Aaron


"Klatuu" wrote:

This will take some VBA, but still doable in a Macro.
You can run a VBA function from a Macro. If you go to this site, you will
see a Windows API that will present the common Open/Save file dialog. It
incudes a function called GetOpenFile. It will take a little modification,
because it is only a demo for how to use the API, but not much and I can help
with it.

First, copy the code at the site and paste it into a new module. Save the
module. Mine is named modCommonDialog. Now you will have to make some
changes. Once you have the code downloaded, let me know and I will post back
with the changes you need.

Then to use it, in the FileName box of the TransferText action in your
macro, you use it like this:

=GetOpenFile()

It will popup a dialog and allow you to navigate to the file. The file nane
you select will be used in the TransferText.

http://www.mvps.org/access/api/api0001.htm

"Graveyardblues" wrote:

Klatuu, Hope your still out there .....
Well, I used the Transfer Text under Macro ??? That works perfectly, I
set it to open my Test.Txt file, then added open Report to the Macro, works
pretty good, opens the report using my sample Test.txt.
Now, I want to modify the Macro to Prompt me for the Data file, that way I
can choose different Data files without going into the design of the MAcro
every time I would like to open a differant data file. Additionally, I want
to modify it so that after it opens the specific Data File, it opens the
report & automatically changes the Record Source to whatever Data file I
choose to import ?? Can this be Done ?? I would think so, but I cannot
figure it out !!!
Thanks For Your Help !!!!!!!!!

Aaron



"Klatuu" wrote:

Okay, post back if you get stuck in the mud.

"Graveyardblues" wrote:

THANKS,
I like the idea, not exactly sure how to do it, But I think that sounds
really good, I will figure it out !!!!
Thanks for the help

"Klatuu" wrote:

I would create a table with the correct fields, field names, and data types.
I would create an import specification for the import to pull the data in
correclty.
Then to import the file, I would use the TransferText method in VBA (a Macro
will do if you don't know VBA) and transfer into the existing table rather
than creating a new table. This way, you will have the correct data types.

"Graveyardblues" wrote:

Thanks for your help.....
I am importing by Hand ?? I think....I right click on the table, go to
Import & create a new table using the .TXT file, the only option I check is
"First Row Contains Headers". The program that I am exporting from only
exports records in .TXT format. I always export the same fields, but the
data is different for every export...Might there be a better way to
accomplish this ? I have different people in my office each using this
method, so, I would love it if I could simplify it.....

"Klatuu" wrote:

How are you doing the import? by hand, in VBA, in a Macro?
Are you importing to a new table or an existing table?

"Graveyardblues" wrote:

The format property of the Text Box only seems to work if I change the
field's in the table from .TXT to Number....
Is there any other way to change the format in the report if the field is a
.TXT ????

Thanks for helping Klatuu

"Klatuu" wrote:

Use the format property of the text box where you want to display your
currency to format the display.

"Graveyardblues" wrote:

I have created a Report based on Data that is exported out of another program
in .TXT format.
After I import the data as a table I set the report control source to match
& print out report using imported data.
All works great, except, when the data is imported it set's all fields to
txt fields in Table. In my report there are 3 currency fields, but I cannot
choose a format type of currency unless I change each field in the table to
be a Number field....
Is there an easier way to display $1,000.00 instead of 1000 ??? Using my
field as a .TXT ????
PLEASE HELP
.

Relevant Pages

  • RE: Importing Data for use in report
    ... A report record source is based on the name ... you now have a Macro (or will with the additional info ... I am importing .TXT files....and for this particular database they will ... opens the report using my sample Test.txt. ...
    (microsoft.public.access.reports)
  • RE: Importing Data for use in report
    ... I put in the Run SQL as the first action in my macro, ... I removed it from my current report, But, I did not think about ... TransferText action is a table name. ... I am importing .TXT files....and for this particular database they will ...
    (microsoft.public.access.reports)
  • RE: Importing Data for use in report
    ... Anyway, so far I have only run my Test File, & the report control source is ... you now have a Macro (or will with the additional info ... TransferText action is a table name. ... I am importing .TXT files....and for this particular database they will ...
    (microsoft.public.access.reports)
  • RE: Differences between two tables....
    ... "I have a macro set up to import the text file to Excel, removes headers, ... The original report is a text file with headers and other garbage on ... exporting it from Access to Excel, making changes, then importing back in to ... You are wanting today's totals, Month To Date, and Year to date? ...
    (microsoft.public.access.reports)
  • RE: Importing Data for use in report
    ... Is the report, perhaps, looking for something named ID. ... you now have a Macro (or will with the additional info ... TransferText action is a table name. ... I am importing .TXT files....and for this particular database they will ...
    (microsoft.public.access.reports)