PDA

View Full Version : sheet and workbook called "ii????.tmp"



CyberGuyJ
02-26-2009, 06:42 AM
Hello People out there!

I new at this, but I would like to see if some one can help me with this issue I have:think:
A program I use has the option to export data to Excel, when I do so
it doesn't ask for a name for the file, but opens a NEW Excel window (so if Excel is open with an other workbook you won't see the two workbooks under the window menu) and shows the data I want in an Excel workboot named "ii????.tmp" with the same sheet name without the .tmp, each time the ???? marks have other characters/numbers (5CAF or 07A3 etc etc).

I made some kind of work around to get it saved to a nomal Excel worbook, but it has a flaw that if an other Excel workbook is open 't grabs that one. (cuz I used workbooks(1).sheet(1).copy.. and that looks at the first workbook that's open.. :doh: )

So what I'm trying to do, if the program opens a NEW Excel and exports the tmp workbook (which is not saved), I'll open an excisting workbook with a code in 't that it looks for a open workbook named "ii????.tmp" and copies the first (and only sheet) to it self and renames the sheet and saves itself (the last part allready works, renaming and saving, it's just that he takes any 1st open workbook now and I want Excel to look for the .tmp workbook)..

hope I explained it well and my English (writing) is still good enough to read.. :thumb

looking forward to the (probably simple) solution to my problem..:clap:

check you guys later

Charlize
02-26-2009, 03:29 PM
What about using 'ActiveWorkbook'
Activeworkbook.Worksheets(1).Copy
Charlize

CyberGuyJ
02-26-2009, 05:30 PM
That's what I'm doing now, but the problem is if I use this and some other Excel document is open, Excel copies that first sheet instead of
grabbing the "ii????" sheet from the (not saved) "ii????.tmp" Workbook

here's what I do:
I let the program puts up the results, and send it to Excel, wich opens a NEW Excel session
and shows the unsaved "ii????.tmp" Workbook with the same sheet name without the .tmp

now I open an Excel Workbook (named: Save Results.xls)
wich has the code:


Private Sub Workbook_Open() 'activate's code on open
On Error Resume Next ' continue even with errors
Application.DisplayAlerts = False 'don't display Alerts
Workbooks(1).Activate 'activate ANY first workbook <- this is the problem
'cuz I want to Activate Workbook("ii????.tmp")
Workbooks(1).Sheets(1).Move Before:=Workbooks(2).Sheets(1) 'this moves the first and only sheet
'from the .tmp workbook and makes it close automaticly cuz there needs to be ONE sheet in a workbook
'and I just moved it, so workbook isn't usefull anymore and closes
Sheets(2).Delete 'this delete's the sheet with the old results that still was in the Excel workbook
'(Save Results.xls) so only one sheet is left
Sheets(1).Name = "Results" 'and here I rename the sheet to "results" again
'so I have one sheet with the new results
Workbooks(1).SaveAs Filename:= _
"c:\temp\Save Results.xls" _
, FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False ' workbook one is now the Save Results.xls workbook
'and saves it now with the new results
Workbooks(1).Close SaveChanges:=False 'and then closes it too
End Sub


So I have what I want, but only if I don't forget to close all workbooks before I get the new results..
hope this turns out readable and understandable..:help :)

again.. thanks for the quick reply.. and hope to see more..:thumb
checking later..

Charlize
02-27-2009, 01:30 AM
You need to change your workflow.

--- Store your code in personal.xls (you probably have to alter something).

1. A certain program exports something to excel (this one becomes the active one)
2. Run the routine that's stored in personal.xls by pushing the info in the active workbook (the one that the program generated) to the workbook you want (either an open one or one that needs to be opened, checked by your code if a file is open or not.)
3. Close everything afterwards if you want.

Charlize

CyberGuyJ
02-27-2009, 05:00 AM
Thnx again Charlize,

although the same problem shows up, cuz when the program exports the results to Excel it's the Active Workbook, but when I run the code/routine I have to open/activate that Workbook and then that's the active one, wich brings me back to my problem cuz I want Excel to look for a None Saved Workbook that starts with ii (ii****.tmp) and move the first and only sheet to the personal or what ever Workbook I start the code/routine from..

but thanks for thinking :think: with me and helping me out so far :thumb

checking later :hi:

Charlize
02-27-2009, 12:04 PM
Thnx again Charlize,

although the same problem shows up, cuz when the program exports the results to Excel it's the Active Workbook, but when I run the code/routine I have to open/activate that Workbook and then that's the active one, wich brings me back to my problem cuz I want Excel to look for a None Saved Workbook that starts with ii (ii****.tmp) and move the first and only sheet to the personal or what ever Workbook I start the code/routine from..

but thanks for thinking :think: with me and helping me out so far :thumb

checking later :hi:
1. Store your subroutine with code in personal.xls
2. This code does the copying and opening of a workbook (no MANUAL opening)
3. Doing so, you can declare a workbook variable which holds the destination workbook and/or sheet.

Couple of questions.

You remove everything from results.xls exept first sheet ? Why not saving the .tmp workbook as result.xls ?

Charlize

CyberGuyJ
02-28-2009, 11:35 AM
I think I don't get you'r idea..

can you explain a bit more? What do you mean with subroutine and code?


The reason why I'm not jut renaming the .tmp to .xls is that excel complains about putting it in a different file format if that's oke and sinds I don't want the people who have to put the results into the results.xls put up with to much work every time the want the results (or have mis typed filenames or don't know what to do by overwriting a file yes or no etc.) I want to make 't work by just opening the file where they want the results in.. the second reason is that, there's a code in Results.xls and if I just rename the .tmp there's no code in it..

The code and workflow I use right now works quite well, except for the fact that there can't be any other Excel Workbook open, cuz it takes that one..

anyways, have a nice weekend Y'all..
SeeYa

Charlize
03-02-2009, 07:54 AM
What about exporting your data (if possible) to a csv file ? Since you can display them in excel.

You save the file somewhere on the network. If the person who wants to use results.xls the file checks if something is found in a certain directory.

If yes, do the import of the csv file to have the latest info. When the import is finished, save results.xls and delete the csv file.

What do you think about that ?

Charlize

CyberGuyJ
03-05-2009, 02:00 PM
Thanks again Charlize,

I'm sorry that I'm not getting your way of handeling routines and codes, or maybe I'm not clear enough.. anyways it's still working as it was and I guess I have to stick to that sollution I have cuz csv isn't avilable for export..

Greetzzz..