Programatically saving an excel .csv file to .xls file...

[Pai]

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

 

[Colo]

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/colo/CellMastersLink.htm
mailto:[email protected]

/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/