PLEASE HELP! Macro Help in Access 2000

[deevaetodin]

I have put together a macro that at first opens a table in the database, using RunCommand I select all records, using the RunCommand again I delete all of the records.

Next I use the TransferSpreadheet macro to import data from an existing excel worksheet into the table I have just deleted all records from .

The first time I do this it works, any time after that the table deletes all records but when the TransferSpreadsheet macro is executed it somehow imports the data previous.

The data that I want to import has previously been calculated based on data that has been exported from access to that excel worksheet from another macro in the same database.

I check the excel workbook and it is fine. For some reason it is not updating the data.

What is going on?

Help.

Thanx in advance.
I have noticed that

 

[deevaetodin]

I have noticed that having the related excel workbook open makes everything ok.

Please advise so that I have functionality without opening the excel table.

Thanx

 

[Dreamboat]

Why do you have to delete it? Can't you just link the Excel file as a linked table?
techsupportgirl@home.com

http://www.PiersonTech.com

Brainbench MVP for Microsoft Word

 

[deevaetodin]

I have a report in access that is based on the imported table.

I only one a one page report printing the most current data.

Even if I do not delete the table, if the excel file is not open the most recent data is not imported into the table.

Does this have anything to do with read only? As far as I know both files are not read only.

Thanks for anything you can come up with.

 

[Dreamboat]

Excel file must be closed.

From your open database, hit File-Get External Data, choose Link Tables, choose your Excel file (change to Excel under files of type).

Everytime your database opens, the Excel file, with any data that's been changed in it, should open. If it doesn't, then you've got something else wrong.
techsupportgirl@home.com

http://www.PiersonTech.com

Brainbench MVP for Microsoft Word

 

[deevaetodin]

I am out of the office.

I will try this and let you know.

Thanks a lot.

 

[deevaetodin]

I have linked the table.

However when the database is started it does not launch the Excel workbook.

Should I be only working with the linked tables when resetting(deleting)and importing now?

Thanks.

 

[Dreamboat]

Excel doesn't start. Here's how it works:

You cannot change the design of an Excel table.
You CAN edit the table, but it must be done either through Access or through Excel (you can't view the actual excel file via Access). I have tested this myself (Office 2000).

If you don't understand, just send me an email asking for a sample.
techsupportgirl@home.com

http://www.PiersonTech.com

Brainbench MVP for Microsoft Word

 

[deevaetodin]

I am not sure I understand what you are saying.

In Access I have no problem exporting data to an excel table.

I only have a problem when I try to import data using the TransferSpreadsheet macro when excel is currently not open.

If the specific excel file is open I have no problems importing the data.

I just want to find out a way so that I do not need the excel table open or to determine a way whereby if that database is open the excel file is automatically launched as well.

I can write a popup when the database is open to prompt the user to ensure that the related excel file but I would prefer to have it open automatically if it needs to be open in order to import the correct data.

Thanks for all your help.

 

[chotawala]

I NEED HELP...
IM RUNNING A GIANT DATABASE THROUGH ACCESS...AFTER COMPLETIGN MY INVENTORY LISTS...I HAVE CREATED A FORM THAT CONTAINS FIELDS THAT ARE ALSO IN VARIOUS CHILD TABLES...HOW CAN I MAKE IT, SO WHEN MY FORM OPENS AND I TYPE IN ALL THE FIELDS. THE CHILD TABLES SHOULD GET THE NEW DATA AND INCREMENT THEIR RECORDS...HOW CAN I DO THIS???

I KNOW A MACRO WOULD B DO IT..BUT I DONT KNOW HOW TO WORK WITH MACROS...PLEASE HELP

chotawala@aol.com

 

[deevaetodin]

In order for me to solve my problem.

I needed to place code in the database so that my excel file was immediately launched when the database was launced.

This way all information entered in the form was dynamically updated in the related excel worksheets.

Here is the link to that discussion.

thread68-136575

 

[mymou]

Hi deevaetodin

Might be best to explain what you are trying to do: You seem to be jumping in at the deep end (with macros and stuff) where careful design might simplify the process.

I find that using a combination of Access and Excel is best to avoid if possible - although linking does create the dynamic updates that you think you require.

Stew "Even a stopped clock tells the right time twice a day."

 

[deevaetodin]

Stew;

I was faced with the challenge of using an existing Excel costing sheet that could not be altered.

The request was made to have Access forms linked to the sheet for data entry, whereas before the user would have to go to several area (very many) within the workbook to enter information.

The Excel sheet is very involved with a lot of complex calcualtions. Could we accomodate these calcualtions in Access without using Excel?

How would you accomodate the complex calculations in Access stand alone, the request was to have it in Access and not forms created in Excel?

D.

 

[mymou]

Hi D

I have no doubt that the complex calculations can be accommodated in Access. All the Office application have their own 'Object Model' - but through Automation any of these applications can use the Object Model of any other Office program.

Having said that, Access has a powerful set of facilities of its own - so using Excel's object model from within Access is probably unnecessary. If you explore the 'Expression Builder'- it will give you an indication of these facilities.

I would recommend that in the short term use the links - as stated by Dreamboat. This removes the need of importing (which is an unnecessary step) and having Excel open.

With the linked table, the table is dynamically linked to excel: Change in the Access linked table will affect the Excel Spreadsheet. This means that importing isn't necessary and you just need a form that uses the linked table (and Excel doesn't need to be open). I think that the form needs to be refreshed to show (Excel's) calulated fields new values.

Do this.
1. Set up a link table.

2. Create a form based on this table.

3. Add, edit and delete information through the form - and see what happens to the Excel spreadsheet.

4. Any problems - do send an email to Dreamboat - he does know everything.


Enjoy.


Stew

"Even a stopped clock tells the right time twice a day."

 

[deevaetodin]

Thanks for the information.

I am very interested in exploring the capabilities of the expression builder.

Any advice as to where I might gather some information online?

Thanks again.

D.