If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
Creating Macros
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 |
#3
|
|||
|
|||
Creating Macros
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 |
#4
|
|||
|
|||
Creating Macros
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 |
#5
|
|||
|
|||
Creating Macros
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 |
#6
|
|||
|
|||
Creating Macros
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 |
#7
|
|||
|
|||
Creating Macros
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 |
Thread Tools | |
Display Modes | |
|
|