Macro Possibility

[foxygrandma]

I have been assigned a tedious, long project of manually taking data from Excel and inputting it into an Access table. It is not a difficult process, but it is time consuming and long and I believe it could be automated with a macro. The process is pretty simple.

In Excel, I go one line at a time, getting the shipment number.

I go into Access, search for that shipment number, and input the cost that corresponds to that shipment number in the cost column on the same row.

I then return to Excel, write "Complete" in the comments column on the same row as the said shipping number and cost.

I then go down to the next line and get the next shipment number, etc.

The process continues as such.

I have no experience writing macros so I was wondering:

Is it possible to automate this?

What steps do I need to take to do it?

Where can I learn more about what I need to do?

Any help or advice would be greatly appreciated. Thanks.

[Stewart Ross]

Hi. Yours is a task that would really benefit from automation in Visual Basic for Applications (VBA). The basics of application automation are laid out in this HowTo article by NeoPa, but as you have no experience of writing VBA code I would recommend a very different approach.

Assuming that the Excel sheet is tabular in format, you could instead link the Excel sheet as a table in Access (File, Get External Data, Link Tables), then devise an Access query to do the look ups/update the status cells in the Excel sheet.

There isn't enough detail of what you want to achieve to give a more specific answer at present. We would need to see a sample of the spreadsheet rows, and what it is you are looking up in Access, to be able to guide you in more detail.

-Stewart

[foxygrandma]

Hi. Yours is a task that would really benefit from automation in Visual Basic for Applications (VBA). The basics of application automation are laid out in this HowTo article by NeoPa, but as you have no experience of writing VBA code I would recommend a very different approach.

Assuming that the Excel sheet is tabular in format, you could instead link the Excel sheet as a table in Access (File, Get External Data, Link Tables), then devise an Access query to do the look ups/update the status cells in the Excel sheet.

There isn't enough detail of what you want to achieve to give a more specific answer at present. We would need to see a sample of the spreadsheet rows, and what it is you are looking up in Access, to be able to guide you in more detail.

-Stewart

Thank you, I agree that this is a much more logical approach, however I have stumbled upon an issue with importing that seems to be a regular problem with Access.

When I try to import the Excel file, it throws an error "The Microsoft Jet database engine could not find the object "xyz." Make sure the object exists and that you spell its name and path correctly." What can I do to solve this?

[foxygrandma]

Thank you, I agree that this is a much more logical approach, however I have stumbled upon an issue with importing that seems to be a regular problem with Access.

When I try to import the Excel file, it throws an error "The Microsoft Jet database engine could not find the object "xyz." Make sure the object exists and that you spell its name and path correctly." What can I do to solve this?

I just copy and pasted it into a new excel sheet. Unless there is something wrong with this solution, I think that solves that problem.

[ADezii]

Stewart ==> Assuming that the Excel sheet is tabular in format, you could instead link the Excel sheet as a table in Access (File, Get External Data, Link Tables), then devise an Access query to do the look ups/update the status cells in the Excel sheet.

If you are interested, as soon as I get the chance, I can show you in code how to implement the logic Stewart has suggested in Post #2.

[foxygrandma]

Can I get some guidance on doing this query? I simply need to pull values from one table and put them in another based on if two fields match. Being a beginner, I need some pretty basic instructions. I'm not sure how basic of a procedure this is though. Any advice would be appreciated. I can send you samples or screenshots if needed.

[ADezii]

Can I get some guidance on doing this query? I simply need to pull values from one table and put them in another based on if two fields match. Being a beginner, I need some pretty basic instructions. I'm not sure how basic of a procedure this is though. Any advice would be appreciated. I can send you samples or screenshots if needed.

Sorry foxygrandma, but you are starting to confuse me. Let me know if my assumptions are correct:

1. You have an Excel Spreadsheet consisting of 3 Columns, namely:
   1. Shipment Number
   2. Cost
   3. Comments
2. For each Shipment Number in the Excel Spreadsheet:
   1. See if this Number exists in an Access Lookup Table.
   2. If this Number does exist in the Access Lookup Table, retrieve the associated Cost Factor from this Table and populate the Excel Sheet with this value for the specified Shipment Number.
   3. Write the word 'Complete' in the Comments Column of the Worksheet for the specified Shipment Number.
   4. If no Shipment Number exists in the Lookup Table, take no action.
3. If all this is correct, you will not find your solution in a simple Update Query.
4. In any event, get back to us on this, and we'll see what we can do.

[foxygrandma]

Sorry foxygrandma, but you are starting to confuse me. Let me know if my assumptions are correct:

1. You have an Excel Spreadsheet consisting of 3 Columns, namely:
   1. Shipment Number
   2. Cost
   3. Comments
2. For each Shipment Number in the Excel Spreadsheet:
   1. See if this Number exists in an Access Lookup Table.
   2. If this Number does exist in the Access Lookup Table, retrieve the associated Cost Factor from this Table and populate the Excel Sheet with this value for the specified Shipment Number.
   3. Write the word 'Complete' in the Comments Column of the Worksheet for the specified Shipment Number.
   4. If no Shipment Number exists in the Lookup Table, take no action.
3. If all this is correct, you will not find your solution in a simple Update Query.
4. In any event, get back to us on this, and we'll see what we can do.

That is precisely what I'm trying to do. Step C may be omitted if it makes things easier. But the other steps are exactly what I'm trying to do.

[ADezii]

That is precisely what I'm trying to do. Step C may be omitted if it makes things easier. But the other steps are exactly what I'm trying to do.

Will get back to you as soon as I get the chance to work on this.

[foxygrandma]

No need. I have figured it out. I created a relationship between the two fields and then ran an update query. It worked exactly as planned. Thank you for your help and patience.