Creating Macros

Hi everyone,

I'm new to macros and would like your assistance. I have created a database
in access 2003. Did the tables, queries, and reports for current data. My
question is how can I overwrite old data when importing new one from an Excel
file? I need to update the information each week and need to figure out a way
to automatically update thru creation of macro since I need to do reports
weekly.

I reviewed a few macros and set them up but it doesnt seem to work. Can you
lead me from the start how to complete this overwrite? Is there any training
on How-to learn from macros?

Also, is there an existing macro where the system recognizes/accepts blank
spaces as acceptable data?

In advance, thanks for your help.


Wanagiawolf

You don't have to write the macro to import Excel to Access. You could link
the table to Excel file. When you or someone update Excel, the report and
queries should update itself.

However, you could write a little macro to update if you want, but it is a
little lenghty to do. I am done with text import, but Excel should be similar.


Try the link first. It must easier. Good luck.

wanagiawolf wrote:
Hi everyone,

I'm new to macros and would like your assistance. I have created a database
in access 2003. Did the tables, queries, and reports for current data. My
question is how can I overwrite old data when importing new one from an Excel
file? I need to update the information each week and need to figure out a way
to automatically update thru creation of macro since I need to do reports
weekly.

I reviewed a few macros and set them up but it doesnt seem to work. Can you
lead me from the start how to complete this overwrite? Is there any training
on How-to learn from macros?

Also, is there an existing macro where the system recognizes/accepts blank
spaces as acceptable data?

In advance, thanks for your help.

Wanagiawolf

--
Message posted via http://www.accessmonster.com

If you didn't want to link to the excel sheet you could write an update
query. Are they the same fields each week?

"wanagiawolf" wrote:

Hi everyone,

I'm new to macros and would like your assistance. I have created a database
in access 2003. Did the tables, queries, and reports for current data. My
question is how can I overwrite old data when importing new one from an Excel
file? I need to update the information each week and need to figure out a way
to automatically update thru creation of macro since I need to do reports
weekly.

I reviewed a few macros and set them up but it doesnt seem to work. Can you
lead me from the start how to complete this overwrite? Is there any training
on How-to learn from macros?

Also, is there an existing macro where the system recognizes/accepts blank
spaces as acceptable data?

In advance, thanks for your help.


Wanagiawolf

To do the macro, you need to write down the access tablename, excel filename,
and excel file directory name on the paper. I don't know why, but some macro
command does not have pull down menu to choose the tablename or directory.

I am sure you need a macro name, on the action column you need to do these:

1. Open the table first (OpenTable), select table from the pull down menu.
2. Select all records. To do this command, you need to select RunCommand, and
SelectAllRecords.
3. Delete all records. Again, select RunCommand, and DeleteRecord.
4. Need to close the table before Access could import data. Select Close, and
provide the table name.
5. Select TransfterSpreadsheet. Here you select Import for transfter type,
Spreadsheet type is Excel 8-10, type in tablename in table name field, File
name is Excel file name (Access required the full directory here), Has Field
Names (No, because you might already has field name in table), and leave
bland for Range.

Save the macro, and give it a try.

Accessorise wrote:
If you didn't want to link to the excel sheet you could write an update
query. Are they the same fields each week?

Hi everyone,

[quoted text clipped - 15 lines]

Wanagiawolf

--
Message posted via http://www.accessmonster.com

Thanks Philt,
I figured it out by doing a link and it is working now. I've read so many
messages about macros and overwrites it's exhausting..lol. Once again, thanks.

"PhilT via AccessMonster.com" wrote:

You don't have to write the macro to import Excel to Access. You could link
the table to Excel file. When you or someone update Excel, the report and
queries should update itself.

However, you could write a little macro to update if you want, but it is a
little lenghty to do. I am done with text import, but Excel should be similar.


Try the link first. It must easier. Good luck.

wanagiawolf wrote:
Hi everyone,

I'm new to macros and would like your assistance. I have created a database
in access 2003. Did the tables, queries, and reports for current data. My
question is how can I overwrite old data when importing new one from an Excel
file? I need to update the information each week and need to figure out a way
to automatically update thru creation of macro since I need to do reports
weekly.

I reviewed a few macros and set them up but it doesnt seem to work. Can you
lead me from the start how to complete this overwrite? Is there any training
on How-to learn from macros?

Also, is there an existing macro where the system recognizes/accepts blank
spaces as acceptable data?

In advance, thanks for your help.

Wanagiawolf

--
Message posted via http://www.accessmonster.com

Yes, they are same fields each update. I have created a link to update and it
works. thanks. I appreciate your time in solving this.


"Accessorise" wrote:

If you didn't want to link to the excel sheet you could write an update
query. Are they the same fields each week?

"wanagiawolf" wrote:

Hi everyone,

I'm new to macros and would like your assistance. I have created a database
in access 2003. Did the tables, queries, and reports for current data. My
question is how can I overwrite old data when importing new one from an Excel
file? I need to update the information each week and need to figure out a way
to automatically update thru creation of macro since I need to do reports
weekly.

I reviewed a few macros and set them up but it doesnt seem to work. Can you
lead me from the start how to complete this overwrite? Is there any training
on How-to learn from macros?

Also, is there an existing macro where the system recognizes/accepts blank
spaces as acceptable data?

In advance, thanks for your help.


Wanagiawolf

Thanks PhilT.
Also, before I imported data from excel to access I had to include a number
in the blank space in order to correct the errors when it transferred. Is
there any command to tell the system to accept a blank space in excel or
should I even worry about it now that it transfered?

"PhilT via AccessMonster.com" wrote:

To do the macro, you need to write down the access tablename, excel filename,
and excel file directory name on the paper. I don't know why, but some macro
command does not have pull down menu to choose the tablename or directory.

I am sure you need a macro name, on the action column you need to do these:

1. Open the table first (OpenTable), select table from the pull down menu.
2. Select all records. To do this command, you need to select RunCommand, and
SelectAllRecords.
3. Delete all records. Again, select RunCommand, and DeleteRecord.
4. Need to close the table before Access could import data. Select Close, and
provide the table name.
5. Select TransfterSpreadsheet. Here you select Import for transfter type,
Spreadsheet type is Excel 8-10, type in tablename in table name field, File
name is Excel file name (Access required the full directory here), Has Field
Names (No, because you might already has field name in table), and leave
bland for Range.

Save the macro, and give it a try.

Accessorise wrote:
If you didn't want to link to the excel sheet you could write an update
query. Are they the same fields each week?

Hi everyone,

[quoted text clipped - 15 lines]

Wanagiawolf

--
Message posted via http://www.accessmonster.com