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.
9 1526
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
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?
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 8,834
Recognized Expert Expert
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.
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 8,834
Recognized Expert Expert
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: - You have an Excel Spreadsheet consisting of 3 Columns, namely:
- Shipment Number
- Cost
- Comments
- For each Shipment Number in the Excel Spreadsheet:
- See if this Number exists in an Access Lookup Table.
- 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.
- Write the word 'Complete' in the Comments Column of the Worksheet for the specified Shipment Number.
- If no Shipment Number exists in the Lookup Table, take no action.
- If all this is correct, you will not find your solution in a simple Update Query.
- In any event, get back to us on this, and we'll see what we can do.
Sorry foxygrandma, but you are starting to confuse me. Let me know if my assumptions are correct:- You have an Excel Spreadsheet consisting of 3 Columns, namely:
- Shipment Number
- Cost
- Comments
- For each Shipment Number in the Excel Spreadsheet:
- See if this Number exists in an Access Lookup Table.
- 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.
- Write the word 'Complete' in the Comments Column of the Worksheet for the specified Shipment Number.
- If no Shipment Number exists in the Lookup Table, take no action.
- If all this is correct, you will not find your solution in a simple Update Query.
- 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 8,834
Recognized Expert Expert
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.
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.
Sign in to post your reply or Sign up for a free account.
Similar topics |
by: juriler |
last post by:
Can i work with macro's in vb 6 sp 4
for instance
dim sformule as string
dim new as string
' .fields("b_value") = " 5"
sformule = "Trim(.fields(" & chr(34) & "b_Value" & chr(34) & "))"...
|
by: qwweeeit |
last post by:
Hi all,
when I moved from Windows to Linux I choosed Python as my language of
reference and as GUI, Qt (not much investigated up to now).
Till now I didn't regret but one thing:
Python can't act...
|
by: asd987 |
last post by:
Hi,
I'm now working with Access 2000 (English) and I'm searching for an option
which I can't find. I used to work with Acces 97 (Dutch) and there was a
possibility to record a macro. I used this...
|
by: WindAndWaves |
last post by:
Hi there,
Can anyone tell me how I can run code/macro in a different database???
Below are the two situations where this may be applicable.
1. run a macro/code in another database that sends a...
|
by: geronimo_me |
last post by:
Hi,
I have the following code in an access module:
Sub Run_Excel_Macro()
Dim xls, xlWB As Object
Dim strFile, strMacro As String
| |
by: ethan |
last post by:
Hi All,
I'd like ask some question about macro.
If I define a variable set, such as {5, 2, 10} or {1,3}.
#define X {5,2,10}
1) Is it possible to write a macro to get the number of items...
|
by: Yogesh |
last post by:
Hi List,
I am overloading new and delete globally for memory leak detection.
void* operator new(std::size_t s, const char * file , int inLine) ;
->case1
void* operator new(std::size_t s, const...
|
by: aaragon |
last post by:
Hi everyone. A very simple question. I would like to know what is
better in terms of performance. I want to use a simple function to
obtain the minimum of two values. One way could be using a...
|
by: eskara |
last post by:
I want to set a macro in a database to run when someone tries to Paste Append information into my Description table. Item # is set as the primary key in this database. The macro should look at the...
|
by: artemetis |
last post by:
I have a macro that is exporting records for a particular employee to an xls.
The default output filename is qryEmpItems.xls
Is there a way to have the macro append the employee name to the actual...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...
| |
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven...
|
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing,...
|
by: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and...
|
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The...
| |
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
|
by: 6302768590 |
last post by:
Hai team
i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated ...
| |