VB: Convert cvs to xls

[swmagic]

The function is passed a string path/file. The call is supposed to convert a cvs document into an xls. It works except for saving the file. I don't see the saved file anywhere. Thoughts?

Public Sub ConvertCSVtoXL(strCSVPath As String)

Dim appExcel As Excel.Application
Dim Pathfile As String


'Switch to Microsoft Excel so it won't go away when you finish.
On Error Resume Next
AppActivate "Microsoft Excel"

'Get an Application object so you can automate Excel.
Set appExcel = GetObject(, "Excel.Application")
Pathfile = Left(strCSVPath, Len(strCSVPath) - 3) & "xls"
With appExcel
.Workbooks.Open FileName:=strCSVPath
ActiveWorkbook.SaveAs FileName:=Pathfile, FileFormat:=xlNormal
End With

appExcel.Quit
Set appExcel = Nothing

MsgBox "File '" & strCSVPath & "' has been converted to excel under the same " & _
"filename with an XLS extension"

End Sub

 

[Swi]

Try:

[red].[/red]ActiveWorkbook.SaveAs FileName:=Pathfile, FileFormat:=xlNormal

Swi

 

[JoeAtWork]

If you had not put the "On Error Resume Next" you would have discovered exactly which line the problem is occurring in. Why would you not want to be alerted about errors?

 

[BobRodes]

Don't put On Error Resume Next unless you're using inline error handling. In that case, you will specifically test the value of Err.Number to see if an error has occurred. This is particularly useful when you know what sort of error might occur in a given line of code, for example when opening a file.

If you have error handling built into your code, you can often have trouble spotting where a runtime error is occurring. In this case, you can go to Tools/Options/General and select "Break on all errors" there. Essentially, this will disable your error handlers.

 

[swmagic]

Thanks for the error handling tips.

OK, the code below works pretty darn good. The only problem now is that when I go to delete the xls file Windows has it locked. Anyone know how to close and delete it when I'm done?,,,oh and by the way, if an excel app is running does this code open a worksheet and if so, I just want to close that worksheet right? (I don't know how)


Public Sub ConvertCSVtoXL(strCSVPath As String)

Dim appExcel As Excel.Application
Dim Pathfile As String

Dim objExcel As Excel.Application
Dim objXsheet As Excel.Worksheet

Set objExcel = New Excel.Application
objExcel.Workbooks.Open FileName:=strCSVPath

Pathfile = Left(strCSVPath, Len(strCSVPath) - 3) & "xls"
ActiveWorkbook.SaveAs FileName:=Pathfile, FileFormat:=xlNormal

MsgBox "File '" & strCSVPath & "' has been converted to excel under the same " & _
"filename with an XLS extension"

End Sub

 

[JoeAtWork]

I think you mean "delete the CSV"?

It's probably locked because it's still open in Excel. I'm not sure the exact syntax to close just the workbook, probably something like

objExcel.WorkBooks(CSV_FileName).Close

 

[HughLerwill]

Just now your code will;

1. Leave an invisible instance of Excel running unless you supply an;

objExcel.Quit

2. Probably only work on the first call/ run (and leave a 'stranded' instance of Excel in memory) unless you qualify your;

ActiveWorkbook.SaveAs FileName:=Pathfile, FileFormat:=xlNormal

with;

objExcel.ActiveWorkbook.SaveAs FileName:=Pathfile, FileFormat:=xlNormal

Additionally/ optionally you may consider changing your;

Dim appExcel As Excel.Application

to;

Dim appExcel As Object

and make your VB6 code compatible with more than one version of Excel.

Your appExcel, objXsheet variables/ objects do not appear to be required.

 

[swmagic]

You are quite right, it does leave Excel running in the background (my bad, for now).

But with the removal of objXsheet variable, the "For XRows" line gets an error "object required". What do I use in it's place:

'Dim appExcel As Excel.Application
Dim Pathfile As String
Dim strCSVPath As String
Dim xRows As Integer
Dim xCols As Integer


'Dim objExcel As Excel.Application
Dim appExcel As Object
'Dim objXsheet As Excel.Worksheet

Set objExcel = New Excel.Application
strCSVPath = Me.FileName
objExcel.Workbooks.Open FileName:=strCSVPath


Pathfile = Left(strCSVPath, Len(strCSVPath) - 3) & "xls"
objExcel.ActiveWorkbook.SaveAs FileName:=Pathfile, FileFormat:=xlNormal

For xRows = 1 To objXsheet.UsedRange.Rows.Count
For xCols = 1 To objXsheet.UsedRange.Columns.Count
'--- Place your processing code here
If xCols = objXsheet.UsedRange.Columns.Count Then
MsgBox ("count")
End If

Next xCols
Next xRows

 

[HughLerwill]

You did not mention the 'For xRows ...' code before so I could not anticipate the need for the objXsheet variable/ object; you will need to Dim it of course.

 

[swmagic]

Duh. Yes, sorry 'bout that.

So when I add that Dim back in, I get the message "Object variable or With block variable not set".

Any ideas why?

 

[swmagic]

Done and it works great!


'-----------------------------------------------------------------------
' CONVERT CSV FILE TO XLS AND PROCESS XLS ROWS AND COLUMNS
' Opens and closes Excel and iterates xRows and xCols
' The Pathfile xls is NOT saved
' The hidden Excel is dumped (existing open Excel file remains unharmed)
' strCSVPath = Me.Filename comes from user screen as unbound text
'-----------------------------------------------------------------------
Dim Pathfile As String
Dim strCSVPath As String
Dim xRows As Integer
Dim xCols As Integer

Dim appExcel As Object
Dim objXsheet As Excel.Worksheet

Set objExcel = New Excel.Application

strCSVPath = Me.FileName
objExcel.Workbooks.Open FileName:=strCSVPath

Pathfile = Left(strCSVPath, Len(strCSVPath) - 3) & "xls"
objExcel.ActiveWorkbook.SaveAs FileName:=Pathfile, FileFormat:=xlNormal
Set objXsheet = objExcel.Worksheets(1)

For xRows = 18 To objXsheet.UsedRange.Rows.Count 'rows 1-17 contain headers to be ignored
For xCols = 1 To objXsheet.UsedRange.Columns.Count
'--- Place your processing code here
'If xCols = objXsheet.UsedRange.Columns.Count Then 'this line checks to see if the current cell is the last column in the worksheet
' MsgBox ("count=" & xCols)
'End If
Next xCols
Next xRows

objExcel.ActiveWorkbook.Close SaveChanges:=False
objExcel.Quit
Set objExcel = Nothing
Set objXsheet = Nothing
' Optional
Kill Pathfile

 

[HughLerwill]

I see you opted to Dim appExcel As Object which is fine, however (and sorry I did not mention it before) to make the appExcel object truly 'late bound' you also need to change;

Set objExcel = New Excel.Application

to;

Set objExcel = CreateObject("Excel.Application")

and change;

Dim objXsheet As Excel.Worksheet

to;

Dim objXsheet As Object