I have a complex problem, but one I'm certain somebody out here can handle, as I am not yet a veteran to VBA world.
I am running a sub inside of Access that is executed when a command button is pushed. It opens Excel and runs a macro. It then proceeds to run a second macro inside of Access based on the results of that macro run inside of Excel.
Here's the issue. Excel opens and starts to run the macro. The macro takes approximately four-six seconds to run (depending on what else I'm doing). Then, before the macro is finished executing inside of Excel, the macro inside of Access starts running.
I will post my code below. Please remember, I'm slightly new, so am just getting familiar with pointing and calling to other functions or subs and such.
Feel free to change this code to make it more efficient, etc. However, if you change this code, please tell me exactly what you did and the reason behind doing it...don't say...make a function, then point to it. I'm not that brilliant yet.
The first two lines are pretty self-explanatory...the third line opens a macro inside of Excel called "Foamexupload" inside of the personal workbook. The fourth line returns to Access to run the macro, "mcrimportfoamex".
I appreciate your help. I am not yet capable of understanding what that little snippet of code does in the other thread (see below). When I place it into my sub, it does not give me the correct results. I just have not yet learned the language. There is hope for me yet...my company is sending me to school to learn programming this summer. Here's to higher education...
Do you have a minute to spare for an explanation of this code that was referenced in your previous post? I appreciate all of your help.
Dim o9 As Object
Set o9 = CreateObject("WScript.Shell")
o9.Run "c:\x1.xls", 1, True
I would and that is a great fix, save for the fact that the file that I'm calling from is a text file that is automatically importing from text upon running the macro, then it's saved as an Excel spreadsheet, so I'm back to square one, though I'm starting to see how that code works that you posted above.
The original text file is coming from a tab delimited file from a barcoding system that we are implementing.
So, I know that the automated macro thing can be done and I can run a macro that does that. The problem is, the macro starts an import of a text file...so now I don't know...hmmmm...frustrating this is, but I can truly see the wonderful merit of learning to program.
I have a text file that is uploaded from a barcoding system to a PC. Then, a macro in Excel performs two text to column changes to that file and adds a couple of fields, readying it for import into Access.
That's the macro that is run inside of Excel. It's currently stored in the Personal workbook because the flat file is replaced everytime the macro is run.
So, to follow your steps above...yes, I am importing the text file into Excel, massaging the data inside of Excel, then importing the results of said massage into Access.
Thank you again and again for your help. I wish you lived closer...I'd pay you bags of money to teach me what you know.
This WILL do what you want (I've added your filename to the code) as long as you can make that Excel macro and autoexec macro.
Dim oExcel As Object
On Error Resume Next
Kill "C:\my documents\inventory.xls"
On Error Goto 0
Set oExcel = CreateObject("WScript.Shell")
oExcel.Run "C:\my documents\PERSONAL.XLS", 1, True
Set oExcel = Nothing
I know you already have a lot invested in this process, but just to let you know, Access can do all of this without having to involve Excel. In other words, the whole process could be made seamless.
AAAAAHHHHHHHHHHYYYYYYEEEEEEEEEEEE....give me the ceremonial suicide sword right now. I would like to do that.
I was under the understanding that Access can only do one text to column function and not two when it imports a delimited file. This file is tab delimited, but the barcode field also needs to be split up seven different ways at the same time.
My theory is to import the file as a tab delimited and then somehow break out the barcode field.
If anyone has the time and patience to explain that process, the rewards will come back to you ten-fold.
P.S. Remember the schooling I referenced in an earlier post. I broke down and signed up for an online tutorial on VB, so that I won't be going into class blind. The hope remains....the legacy persists.
Thanks in advance for the help. My ancestors are cheering.
Why do you need Excell!? with the Access you can do the same that you do with Excell, even better.
Import all the data from Excell to Access, and then workarround in Access, its easyer.
I've done an inventory and Production control database, that calculates Economical Order Quantity, Lot sising, Time series exchange, Gantts Diagrams, etc. and lots of statistical calculations, and in the begining, i thought in use excell to, but it is easyer to do all in Access.