Unanswered: I need to kick out .XLS files but do not want DTS
I have a procedure that gathers a data set for a given date range. I then loop through the temp table and build comma delimited text files for each "group" There could be 10 or there could be 1000 files and i will never know the file names until they are actually created. These files gets placed in a zip file and mailed to the specified recipients. This all works just fine.
However, this data is intended to be imported to another application that expects an Excel format and it will not settle for calling the files *.XLS. It knows that they are just comma delimited text files and freaks out. I'm looking for some easy way to create these files the proper way so this application will accept them. If it was just a single file in question I wouldn't care but since there can be so many it could be very time consuming for a user to open Excel, manipulate the file (remember, there may be 10 and there may be 1000), save it, etc. Why use a computer at all if so much manual intervention is required?
Anyone have any suggestions? I've looked in to DTS but I'm not sure it will work because as I've said, I won't knwo the names of the files until all the data is gathered and they are built blah blah blah.
though I dont find why the same logic that is working for current export will not work for excel export and assuming your users are not using too old an excel version - just wanted to information you (1) excel can open a csv file directly. So if you are generating a csv it is already excel ready. (2) a file containing html table and renamed as .xls can be opened directly in excel
Yes, all of this I know. However, The files must have an .xls extension or this third party app will not allow the user to import the file. It goes a lot deeper than this though. If I kick out the files with a .xls extension the app pukes on import. The issue is that the columns have to be formatted in a certain way for this thing to like them. Example, one field is a date but in Excel it has to be set as MDY. This isn't a big deal to do in the procedure but the app knows that the file hasn't been touched by Excel. If I open a file I produce in say notepad it looks very different than a file that has been manipulated in Excel and saved properly.
The issue is not that Excel can't use the files, it is that this third party app rejects the file unless it is first massaged in Excel.
Ok. then you can create a DTS that will convert each csv file of a folder into xls as suggested by blindman by dymanically passing file name. or can write a excel macro that will do it for you. i have one such macro written. let me know if you are thinking the macro way and need the code.
Just to clear up the DTS question, our application can use either Oracle or SQL Server as the back end. This particular project is something our clients in Maine use. We have Oracle and SQL Server clients there. Because of this, I can't write something that is so SQL Server-centric (if that is a real word) and I can't write something for Oracle like that, either. The true desire is to have all the control in the stored procedure for either DB but I'm guessing that is wishful thinking.
Dim fs As Scripting.FileSystemObject '---need to add a ref of this before hand
Dim fold As Folder
Dim file As Files
Set fs = New Scripting.FileSystemObject
Set fold = fs.GetFolder("d:\myfolder\")
For Each obj In fold.Files
If LCase(Right(obj.Name, 3)) = "csv" Then
Workbooks.Open Filename:="d:\myfolder\" & obj.Name
ActiveWorkbook.SaveAs Filename:="d:\myfolder\" & Left(obj.Name, Len(obj.Name) - 4) & ".xls", _
FileFormat:=xlExcel5, Password:="", WriteResPassword:="", _
can call this from "Private Sub Workbook_Open()" so that it runs automatically. or can execute it manually. also i have not added the code to close files after saving.
Note : there are other complications (like security level should be low, enable macro etc) with macro that you will have to deal with.