Extracting data from a hidden Excel or Word File

[patgenn123]

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

[Cory]

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.

[patgenn123]

Hey Joe!

 

When is it coming out???

 

-P

[balithag]

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.

[patgenn123]

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

[Cory]

#1- there are more than 99 columns

I'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!?

[MrBiggz]

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!

[patgenn123]

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

[MrBiggz]

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. :/

[patgenn123]

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. :/

[patgenn123]

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

[MrBiggz]

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

[patgenn123]

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

[MrBiggz]

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!