Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Programatically saving an excel .csv file to .xls file...
Hello there,
I have been trying to programatically save and excel test.csv to and test.xls. I use the following code: Dim excelapp As New Excel.Application Set excelapp = New Excel.Application excelapp.DisplayAlerts = False Dim c As New Excel.Workbook Dim f As New Excel.Worksheet Set c = excelapp.Workbooks.Open(fileName) Set f = c.Worksheets(1) saved_Excel_WorkSheet_Name = f.Name excelapp.SaveWorkspace (saved_Excel_File) c.Close Set f = Nothing Set c = Nothing excelapp.Quit Set excelapp = Nothing I realise that the .xls file is still linked to the .csv file. As If i delete the .csv file I am unable to open the file. I tried using the WorkBook.SaveCopyAs method but the format of the data is lost... whcih is the best way to programatically save a .csv file to .xls. Thanks in advance, Srikanth Pai |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Programatically saving an excel .csv file to .xls file...
Hello Srikanth,
As for me, always open csv file with using OpenText method. I have no idea which way is the BEST, but the following code worked on my XL2000. Sub OpenCSVFast() Dim buf(1 To 256) As Variant Dim i As Long Const strFilePath As String = "C:\Test.CSV" Dim strRenamedPath As String strRenamedPath = Split(strFilePath, ".")(0) & "txt" With Application .ScreenUpdating = False .DisplayAlerts = False End With 'Setting an array for FieldInfo to open CSV For i = 1 To 256 buf(i) = Array(i, 2) Next Name strFilePath As strRenamedPath Workbooks.OpenText Filename:=strRenamedPath, DataType:=xlDelimited, _ Comma:=True, FieldInfo:=buf Erase buf ActiveSheet.UsedRange.Copy ThisWorkbook.Sheets(1).Range("A1") ActiveWorkbook.Close False Kill strRenamedPath 'DELETE CSV FILE(renamed as a text file) With Application .ScreenUpdating = True .DisplayAlerts = True End With ActiveWorkbook.Save 'or use SaveAs End Sub -- Kind Regards Colo /_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/ Colo of 'The Road of The Cell Masters' :) URL:http://www.interq.or.jp/sun/puremis/...astersLink.htm /_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/ "Pai" wrote in message om... Hello there, I have been trying to programatically save and excel test.csv to and test.xls. I use the following code: Dim excelapp As New Excel.Application Set excelapp = New Excel.Application excelapp.DisplayAlerts = False Dim c As New Excel.Workbook Dim f As New Excel.Worksheet Set c = excelapp.Workbooks.Open(fileName) Set f = c.Worksheets(1) saved_Excel_WorkSheet_Name = f.Name excelapp.SaveWorkspace (saved_Excel_File) c.Close Set f = Nothing Set c = Nothing excelapp.Quit Set excelapp = Nothing I realise that the .xls file is still linked to the .csv file. As If i delete the .csv file I am unable to open the file. I tried using the WorkBook.SaveCopyAs method but the format of the data is lost... whcih is the best way to programatically save a .csv file to .xls. Thanks in advance, Srikanth Pai |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Saving Excel 2007 file in 2003 creates very large file | Excel Discussion (Misc queries) | |||
Excel should let me sort the file directory when saving a file | Excel Discussion (Misc queries) | |||
Saving an excel file without it amending the name of file name | Excel Discussion (Misc queries) | |||
How do I stop Excel 2000 from saving file history from file that . | Excel Discussion (Misc queries) | |||
Creating a PDF file programatically from Excel | Excel Discussion (Misc queries) |