Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel,microsoft.public.excel.programming,microsoft.public.excel.misc
|
|||
|
|||
How to create a .XLS file instead of a .CSV file
I have a COBOL program that generates a .CSV file regularly but I have
a problem in the .csv file my users want the file to be in .XLS instead of .CSV is there a simple way I can turn my .csv file into a ..XLS file please advise. Can my COBOL program put some structure to turn the .CSV file into a ..XLS file. Also, can somebody share a VB Script to turn a .CSV file into a .XLS file. Thanks Karen |
#2
Posted to microsoft.public.excel,microsoft.public.excel.programming,microsoft.public.excel.misc
|
|||
|
|||
How to create a .XLS file instead of a .CSV file
An easy way is to open it in Excel and then save it as a workbook.
You might just write a CSV file and name it with an XLS extension if the main gripe is having to change the fileopen dialog to look for a CSV. -- Regards, Tom Ogilvy "Karen Middleton" wrote in message om... I have a COBOL program that generates a .CSV file regularly but I have a problem in the .csv file my users want the file to be in .XLS instead of .CSV is there a simple way I can turn my .csv file into a .XLS file please advise. Can my COBOL program put some structure to turn the .CSV file into a .XLS file. Also, can somebody share a VB Script to turn a .CSV file into a .XLS file. Thanks Karen |
#3
Posted to microsoft.public.excel,microsoft.public.excel.programming,microsoft.public.excel.misc
|
|||
|
|||
How to create a .XLS file instead of a .CSV file
following vb script generates an excel file from a comma-delimited (CSV) text file WITHOUT using excel. it relies on ADO to do the work and if scripting is enabled you can just put it into a vbs file. (you can also put the code into a vba procedure) be careful with the syntax... SELECT INTO statement has several "syntaxis" (see KB 295646), but this is the only way I got it to work with both CSV and XLS in a "one-liner" note the the source file name cannot contain spaces! copy following into notepad then save with the extention .vbs cut here = Dim csvPath Dim csvFile Dim xlsFile csvPath = "d:\ado test" csvFile = "myExport.csv" xlsFile = "d:\a new workbook.xls" With CreateObject("Scripting.FileSystemObject") If .FileExists(xlsFile) Then .DeleteFile (xlsFile) End With With CreateObject("adodb.connection") .ConnectionString = _ "Provider=Microsoft.Jet.OLEDB.4.0;" & _ "Extended Properties=""text;HDR=Yes;FMT=Delimited;"";" & _ "Data Source='" & csvPath & "';" .CursorLocation = 3 'adUseClient .Open .Execute " SELECT * " & _ " INTO [Import] " & _ " IN '' [Excel 8.0;Database=" & xlsFile & "]" & _ " FROM " & csvFile .Close End With <==cut here keepITcool < email : keepitcool chello nl (with @ and .) < homepage: http://members.chello.nl/keepitcool (Karen Middleton) wrote: I have a COBOL program that generates a .CSV file regularly but I have a problem in the .csv file my users want the file to be in .XLS instead of .CSV is there a simple way I can turn my .csv file into a .XLS file please advise. Can my COBOL program put some structure to turn the .CSV file into a .XLS file. Also, can somebody share a VB Script to turn a .CSV file into a .XLS file. Thanks Karen |
#4
Posted to microsoft.public.excel,microsoft.public.excel.programming,microsoft.public.excel.misc
|
|||
|
|||
How to create a .XLS file instead of a .CSV file
keepITcool wrote ..
be careful with the syntax... SELECT INTO statement has several "syntaxis" (see KB 295646), but this is the only way I got it to work with both CSV and XLS in a "one-liner" I prefer the alternative syntax e.g. SELECT MyCol FROM [Excel 8.0;HDR=YES;Database=C:\Tempo\db.xls].MyTable The sequence <<database.<<table is more logical, it avoids those empty quotes and it better on the eye IMO <g. note the the source file name cannot contain spaces! This is incorrect (perhaps you were referring to a specific syntax rather than generally?). You merely surround the file name (table name) with e.g. brackets: SELECT MyCol FROM [Text;Database=C:\Tempo\;].[Gappy Gap#csv]; I think your code is connecting to the wrong object i.e. the ADO connection should be to Excel. As you said, because you connected to the source and the source is Text you had syntax problems (a limitation or bug in the provider?) You can open a connection to a non-existent workbook; remember, the provider creates the workbook, worksheet and Excel table on the fly if required. Also, with the provider you are using (OLE DB for Jet 4.0), FMT=Delimited in the connection string has no effect; you must use a scheme.ini file to specify format, however comma delimited is default. Thus: .ConnectionString = _ "Provider=Microsoft.Jet.OLEDB.4.0;" & _ "Data Source=" & xlsFile & ";" & _ "Extended Properties='Excel 8.0;HDR=Yes'" .Execute _ "SELECT * " & _ " INTO Import " & _ " FROM [Text;HDR=Yes;Database=" & csvPath & ";].[" & csvFile & "];" Jamie. -- |
#6
Posted to microsoft.public.excel,microsoft.public.excel.programming,microsoft.public.excel.misc
|
|||
|
|||
How to create a .XLS file instead of a .CSV file
of course the "trick" in this case was simple in hindsight:
you set the Source of the connectstring to the (non existing) target file, which solved a lot of the syntax hassle. This trick (i.e. opening a connection to a non-existent .xls) can also be used when you need a Jet connection which doesn't involve Excel at all e.g. to create a text file using SQL Server data: SELECT fname, minit, lname INTO [Text;FMT=Delimited;HDR=Yes;DATABASE=C:\TEMPO\;].[Export#txt] FROM [ODBC;Driver={SQL Server};SERVER=(local);DATABASE=pubs;UID=***;Pwd=* **;].employee; i'm learning (and struggling).. but i wouldn't mind a kickstart. or can you recommend some definitive guide/book/site with the down and dirty on all these ado settings? (i currently use www.able-consulting.com/ADO_Conn.htm as 'bible' Do you mean the Jet syntax where the connection string is in the sql text? I agree there are few details out there. I don't even know what this syntax 'is' - ODBCDirect? I've gleaned details only through hours of experimentation (plus a little Excel app that archives my sql queries) but there are still holes in my knowledge e.g. is it possible to specify the path to the .mdw used to secure an .mdb? There is no Jet newsgroup and most in the MS Access ngs cannot tell Jet from MS Access anyhow. I also agree able-consulting is a good source for general ADO: invaluable connection string collection and interesting faq. have you published all that knowledge of yours somewhere? I'm gradually publishing all my knowledge in the Usenet archive <g. I guess I could scrape together a few html pages that could be a useful reference source (for me if no one else) but I don't have the time to maintain a domain. Others have kindly hosted some stuff I can link to e.g. http://www.dicks-blog.com/excel/2004...al_data_m.html You gotta hand it to people like Dick Kusleika who can write daily on an interesting and relevant topic. My blog about Excel+sql would dry up after the first week <g. Jamie. -- |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
create card file from csv file | Excel Discussion (Misc queries) | |||
Code to create dbase file from text file? | Excel Discussion (Misc queries) | |||
Possible to create XL file then run XL VBA (to format & filter XL file) from Access VBA? | Excel Discussion (Misc queries) | |||
Create a single xls file with each import csv file having its own tab | Excel Discussion (Misc queries) | |||
Create a batch file from a number of Excel File | Excel Programming |