Excel OLE

[patgenn123]

Hello all.

 

I was wondering if anyone could give me a serious answer on this. I might have to go to an excel forum, but maybe there is an answer here.

 

I am "importing external data" from a database to Excel. I set the "refresh" for every 60 seconds. Since I am new in programming, I don't know what to do next.

 

Here is/are my question(s):

 

First, I want to keep the excel file "hidden" and don't want this seen on the desktop at all. So my question is: How can I get a macro to "refresh this" with ME whenever the macro triggers it despite the fact that Excel refreshes itself every 60 seconds. I still might need it to refresh WHENEVER I need it to.

 

Also, if the Excel file is hidden, how can I get it to be "saved" as a tab-delimited/comma delimited without showing any action on the computer screen? How can I get it to be saved whenever I ask ME to save it even though the Excel file is hidden from view? The tab/comma delimited file can be saved in any folder.

 

Am I approaching this the wrong way? If anyone knows a better way(that is, without ME involved), let me know. However, I prefer to stick with ME for the answer.

 

If there are any questions about my foolhardy questions, let me know. I will try to answer by clarifying my questions!!!!!!!

 

Pat Gennarelli

[stevecasper]

Pat,

 

I think I can address part of your question.

 

I've been trying for years to find a way to convert .xls files to .csv files invisibly. I've finally concluded that Macro Express can't do it.

 

You can build a macro that can rename the file from an open Excel document, and that works, but it's not invisible, and since it relies on mouse-moves and/or tabs, there is a great deal of room for error. Generally, it's easier to have the macro pause while you manually save the document as a .csv, and then resume it... but that's not what you're trying to do.

 

In my experience, this is the easiest and most secure way to create a .csv from an .xls.

 

There may be other options, however. If there is a way to have the database information load directly to a .txt or .csv, that would be my first suggestion; though I wouldn't have a clue about how to do that.

[patgenn123]

Scasper,

 

Well there actually is a way, but it has to blend Macro Express with either AutoHotKey or AutoIt. There is a way of making the open program(such as Excel) go from invisible to visible(which ME can go), but the part that ME cannot do is when it becomes visible, it becomes transparent for a split second so that you could save it to a .tsv or .csv.

 

It's not super elegant, but then again, I am not a programmer so I have to find ways that are not elegant, but work just as well.

 

My thought was to have the Excel file invisible, when it becomes visible, it becomes immediately transparent in the left or right corner, a corner so tiny that no one would be typing there. ME can do the moving to the corner and then create a Microsoft hotkey to do an auto-refresh/save and then make it invisible again. All in a second.

 

So, it's a workaround, but it would involve AutoHotKey or Autoit.

 

Pat