patgenn123 Posted April 10, 2008 Report Share Posted April 10, 2008 I was wondering if Macro Express can extract data from a line or cell(of Word or Excel) if the Excel or Word file is hidden? Worse case scenario, can macro Express extract data from a Word or Excel file that is minimized? I certainly do not want to see it even flash on the screen. Thank You! Pat Quote Link to comment Share on other sites More sharing options...
Cory Posted April 10, 2008 Report Share Posted April 10, 2008 Not to my knowledge. But you might check Joe's utility. But I'm not sure if it's ready for prime time. Barring that you can do it with a bit of VBScript. Quote Link to comment Share on other sites More sharing options...
patgenn123 Posted April 10, 2008 Author Report Share Posted April 10, 2008 Hey Joe! When is it coming out??? -P Quote Link to comment Share on other sites More sharing options...
balithag Posted April 10, 2008 Report Share Posted April 10, 2008 If you're hiding the window yourself to try to get it out of the user's face, I'd recommend using an ASCII text file as a source instead. I used to have ME3 harvest data from Excel cells to store in text variables and send to another program. That was good for awhile, but it was somewhat slow. I found that saving the Excel file as a tab-delimited or comma-separated text file and then using the ME3 command ASCII File Begin Process was quicker and less error-prone than jumping into an Excel window and back. The text file gets opened in the background by ME3--you never even see it on your taskbar. It can assign values to text variables for a whole row at a time, even if it has up to 99 columns. Just remember to put an ASCII File End Process at the point where you want it to advance to the next row. Hope that helps. Quote Link to comment Share on other sites More sharing options...
patgenn123 Posted April 10, 2008 Author Report Share Posted April 10, 2008 hello everyone! I have three problems with this: #1- there are more than 99 columns #2- I finally figured out how OLE DB works with a database. The information updates every 1 minute from the database to excel VIA OLE DB. Obviously, I could figure something out to transfer this to an ASCII file and I guess I could figure out using VBSCRIPT to tear out columns and/or rows. I know rows with VBscript, but I'll have to find the script to delete columns via VBscript to delete those unneeded columns. #3- I don't want Excel to be seen. So that's where I'm at. -P Quote Link to comment Share on other sites More sharing options...
Cory Posted April 15, 2008 Report Share Posted April 15, 2008 #1- there are more than 99 columnsI've already showed you how to handle this one a couple of times. Perhaps I didn't do a very good job of explaining? Is there some way I could make it easier to understand? Or maybe this is a different issue. Are you still coping stuff from Act!? Quote Link to comment Share on other sites More sharing options...
MrBiggz Posted May 5, 2010 Report Share Posted May 5, 2010 I'm still fairly new to ME (best program EVER) and what I'm trying to do I think is the same thing. I have an excel spreadsheet that has a list of locations (Store #/City/State). I want to be able to run a macro that prompts me to enter one of these pieces of information and it brings up the rest of the information. Such as if I enter the Store # 1234 it displays the City and State information. Or if I enter in the City, it displays the Store# and State information. Sometimes we close stores and open new one's so the spreadsheet is always changing. Using ME to search and display this would be much easier and quicker than having to find the spreadsheet, open it, search it, and hope you have the latest information. Thanks in advance for the help! Quote Link to comment Share on other sites More sharing options...
patgenn123 Posted May 5, 2010 Author Report Share Posted May 5, 2010 Hello Mr. Biggz, I still don't know how to use Macro Express exclusively to do this. The only other way is to use a VBscript OR use AutoIT/Autohotkey to make the spreadsheet transparent. You can also use VBA in Excel. One other way is to move your open Excel file(using ME to move it) to a corner of your screen and mine your information that way. Most of these ways will show the file minimized in your task bar though. Some people want the file hidden or closed. Pat Quote Link to comment Share on other sites More sharing options...
MrBiggz Posted May 5, 2010 Report Share Posted May 5, 2010 Hello Mr. Biggz, I still don't know how to use Macro Express exclusively to do this. The only other way is to use a VBscript OR use AutoIT/Autohotkey to make the spreadsheet transparent. You can also use VBA in Excel. One other way is to move your open Excel file(using ME to move it) to a corner of your screen and mine your information that way. Most of these ways will show the file minimized in your task bar though. Some people want the file hidden or closed. Pat Hi Pat, Well from balithag's post it sounds like he knows how to do what I'm trying to do, but I could be wrong. I would prefer this to file to be closed rather than open. Hopefully someone can help. :/ Quote Link to comment Share on other sites More sharing options...
patgenn123 Posted May 5, 2010 Author Report Share Posted May 5, 2010 Hi Pat, Well from balithag's post it sounds like he knows how to do what I'm trying to do, but I could be wrong. I would prefer this to file to be closed rather than open. Hopefully someone can help. :/ Quote Link to comment Share on other sites More sharing options...
patgenn123 Posted May 5, 2010 Author Report Share Posted May 5, 2010 Here you go: http://blogs.technet.com/heyscriptingguy/archive/2005/03/22/how-can-i-save-a-single-excel-worksheet-to-a-csv-file.aspx Const xlCSV = 6 Set objExcel = CreateObject("Excel.Application") Set objWorkbook = objExcel.Workbooks.Open("C:\Scripts\Testsheet.xls") objExcel.DisplayAlerts = FALSE objExcel.Visible = TRUE Set objWorksheet = objWorkbook.Worksheets("Sheet1") objWorksheet.SaveAs "c:\scripts\test.csv", xlCSV objExcel.Quit Just change obj.Excel.Visible to False and you could extract text from the text file and parse it with Macro Express. Just remember you need to save it as a .vbs extension and have the vbscript run however way you want it. Pat Quote Link to comment Share on other sites More sharing options...
MrBiggz Posted May 10, 2010 Report Share Posted May 10, 2010 Here you go: http://blogs.technet.com/heyscriptingguy/archive/2005/03/22/how-can-i-save-a-single-excel-worksheet-to-a-csv-file.aspx Const xlCSV = 6 Set objExcel = CreateObject("Excel.Application") Set objWorkbook = objExcel.Workbooks.Open("C:\Scripts\Testsheet.xls") objExcel.DisplayAlerts = FALSE objExcel.Visible = TRUE Set objWorksheet = objWorkbook.Worksheets("Sheet1") objWorksheet.SaveAs "c:\scripts\test.csv", xlCSV objExcel.Quit Just change obj.Excel.Visible to False and you could extract text from the text file and parse it with Macro Express. Just remember you need to save it as a .vbs extension and have the vbscript run however way you want it. Pat Thanks Pat. Unfortunately I don't know vbscripting to get this to work as you have entended. I've copied the contents to notepad and saved as .vbs file, however from there I'm not sure what else to do. I'm going to play with this for a bit and see what I can do. Thanks again for the help Quote Link to comment Share on other sites More sharing options...
patgenn123 Posted May 10, 2010 Author Report Share Posted May 10, 2010 Mr. Biggz, You're welcome! The only lines you need to manipulate are: Line #3- This is the name of the Excel file you want to extract info from. If you want it to be kept closed, then change line #5 to False. Find the name of your weeksheet within your Excel file(the tab name- for example, Sheet1)where all your info is. Plug it into ("Sheet1"). If the tab is Sheet1 already, you don't have to do anything. On Line 7, this is your output file in .csv form. Where do you want your output to go? Put it there. Save this text file as .vbs. Click on the .vbs file to test run it and you will see your Excel file converted to comma separated and then code Macro Express to find the information you want. Now you can run Macro Express to run this using whatever trigger you would like. It outputs to the .csv file overwriting the prior .csv file everytime. If you have anymore questions, please ask! Pat Quote Link to comment Share on other sites More sharing options...
MrBiggz Posted May 12, 2010 Report Share Posted May 12, 2010 Mr. Biggz, You're welcome! The only lines you need to manipulate are: Line #3- This is the name of the Excel file you want to extract info from. If you want it to be kept closed, then change line #5 to False. Find the name of your weeksheet within your Excel file(the tab name- for example, Sheet1)where all your info is. Plug it into ("Sheet1"). If the tab is Sheet1 already, you don't have to do anything. On Line 7, this is your output file in .csv form. Where do you want your output to go? Put it there. Save this text file as .vbs. Click on the .vbs file to test run it and you will see your Excel file converted to comma separated and then code Macro Express to find the information you want. Now you can run Macro Express to run this using whatever trigger you would like. It outputs to the .csv file overwriting the prior .csv file everytime. If you have anymore questions, please ask! Pat GOT IT! Thanks for the help Pat. Now on to figure out how to extract the data! Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.