Results 1 to 9 of 9
  1. #1
    Join Date
    Dec 2002
    Location
    Sunny Florida
    Posts
    121

    Unanswered: I need to kick out .XLS files but do not want DTS

    Greetings,

    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.

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    You can use DTS and assign file names from a variable.

    Or you could use MS Access as an interface to your SQL Server data and kick out Excel files using VB for Applications.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  3. #3
    Join Date
    Feb 2003
    Location
    India
    Posts
    216
    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

  4. #4
    Join Date
    Dec 2002
    Location
    Sunny Florida
    Posts
    121
    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.

  5. #5
    Join Date
    Feb 2003
    Location
    India
    Posts
    216
    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.

  6. #6
    Join Date
    Dec 2002
    Location
    Sunny Florida
    Posts
    121
    I would very much appreciate that macro code.

  7. #7
    Join Date
    Dec 2002
    Location
    Sunny Florida
    Posts
    121
    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.

  8. #8
    Join Date
    Feb 2003
    Location
    India
    Posts
    216
    Ok here is the macro

    Code:
    Sub Macro1()
    
    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:="", _
                ReadOnlyRecommended:=False, CreateBackup:=False
        End If
    Next
    
    End Sub
    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.

  9. #9
    Join Date
    Apr 2006
    Posts
    47
    I was just reading your post, the sql you have sounds cool, i am good at vb,vba,c# but just started SQL, can i see the code your using? Would appreciate it allot,


    Thanks

    ~M

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •