Convert CSV to XLS in VB

[Paul Wagner]

(Scroll to the bottom post for final answer & code)

I'm trying to piece together other techniques and here's what I have so far, but it's stuck on Excel reference in Shell (No Active X component). There's got to be a cleaner way to do this! HELP.

Public Sub ConvertCSVtoXL(strCSVPath As String)

Dim appExcel As Excel.Application
Dim Pathfile As String


'If Excel isn't running, start and activate it
If Err Then
Shell "c:\Program Files\Microsoft Office\Office11\Excel.exe"
AppActivate "Microsoft Excel"
End If
On Error GoTo 0

'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

 

[rolaaus]

This is ironic, I actually have been doing work where I am taking XLS files and converting them to CSV.

I would recommend not refering the Excel.exe file directly - if someone does a custom install and has had to change the installation directory because of having to keep multiple copies of Office on their computer, then this might cause a failure, though I doubt it would freeze your code like you describe, rather it would probably throw an error (though since your trapping errors to goto 0, maybe this is why).

Here is the code I use, and I know it doesn't check to see if there is an instance of Excel opened, but I don't "show" the instance I call, and my code releases the instance, so I figure if the end-user already has Excel opened, I would rather not force them to close it, and all my code runs behind the scenes.

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

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

 

[Paul Wagner]

OK, but when I take your code and 'rewrite' mine with Excel open I get the error "object variable or With Block variable not set" (I'm sure it's just green ignorance)


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"
With appExcel
.ActiveWorkbook.SaveAs FileName:=Pathfile, FileFormat:=xlNormal
End With


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

End Sub

 

[Paul Wagner]

Well I found that I don't need any of the With logic. And it is creating the file perfectly with the code below.

However, when I go to delete the file Windows balks that it is locked by another process. So, after I do what I need to do with the file (reading/process rows) how do I close it?

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

 

[rolaaus]

closing Excel and releasing file

Here is my closing/clean-up code that should allow you to delete the file when finished ... my code actually takes an XLS file, cycles through Cols and Rows and generates a comma seperated file (in the Windows Temp directory) that I run a docmd.importtext on, then I delete the temp file and this is how I do it.

objExcel.ActiveWorkbook.Close SaveChanges:=False/True
objExcel.Quit
Set objExcel = Nothing
Set objXsheet = Nothing
Set rsRecord = Nothing

 

[Paul Wagner]

This is great. One last question:

I was thinking I don't really need to save the file, I'm only using excel to properly format the CSV (since I was unable to Import the csv file fields using the Split function delimited by commas...all numerics get split too, sigh)

Anyway, how do you loop the rows and columns in the excel file?

Thanks for all of your help!!!!!!!

 

[rolaaus]

I'll post what I have for the code, the variables may need to be renamed and if you need help deciphering it, go ahead and post a reply and I'll help you through it.

Since I have the code for it, I will also include what I have to place a 'progress bar' on the bottom 'status' bar of Access. There should be a way to switch this to a progress bar contorol placed on the form, but since I am the one that is mainly going to use what I am building, I just kept in the status bar of Access.

FYI, #intFileHandler is a reference to the CSV text file that I am creating, so you might just ignore those lines and replace with something appropriate to what you're doing.

Call SysCmd(acSysCmdInitMeter, "Importing " & inFileType & " File", objXsheet.UsedRange.Rows.Count)

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 'this line checks to see if the current cell is the last column in the worksheet
Print #intFileHandler,
Else
Print #intFileHandler, ",";
End If
Next xCols

Call SysCmd(acSysCmdUpdateMeter, xRows)
Next xRows

Call SysCmd(acSysCmdRemoveMeter)
Close #intFileHandler

 

[Paul Wagner]

I'm getting an error at the objXsheet.UsedRange.Rows.Count "Object variable not set": How do I set it?

And do I need to Save the file in order to access it in the ActiveWorkbook.SaveAs FileName?



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 objXsheet As Excel.Worksheet

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


Pathfile = Left(strCSVPath, Len(strCSVPath) - 3) & "xls"
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 'this line checks to see if the current cell is the last column in the worksheet
MsgBox ("count")
End If

Next xCols
Next xRows

 

[rolaaus]

I might be misunderstanding the problem here, but since you are trying to open a CSV and save as XLS, I am wondering if the problem you are experience with "Object variable not set" might be because your worksheet has nothing in it? I might think it has to do with your opening a CSV and not a XLS but I don't think that would be right.

One other thing right off the top of my head to check is - if your file has more than 32,768 lines (I don't remember the exact number) then you need to Dim xRows as LONG and not Integer -- this actually is my first suspicion. (You don't have to worry about the Cols variable, because I don't believe Excel goes that far over.

Let me know if I'm off and we'll see what more troubleshooting can be done.

 

[Paul Wagner]

There is definitely information in the XLS file, I can open it laden with data (of course it says locked for read only as I am terminating debug prematurely

No, the csv is, and always will, be about 600 rows).

And I get the message "the file exists, do you want to replace it?"

So I'm at a loss.

 

[Pat Hartman]

Try a simpler approach that does not involve automating Excel.

Link to the .csv file
Use TransferSpreadsheet to export the linked .csv file as an .xls file

 

[Paul Wagner]

Thanks Pat:

Doesn't Transferspreadsheet load the xls into a table? I can't do that because the csv has rows 1-18 of header information that must be eliminated prior to looping the actual data.

 

[rolaaus]

missing line of code

Now that I re-looked at the code, I found that - at least for the coding that you have posted, which I'm going to assum is mostly complete, it is not setting the objXSheet variable

Set objXsheet = objExcel.Worksheets(1)

I am not sure, since this has always worked for me, but you might possible be able to make sure you are referencing the correct worksheet by referencing the name, as long as it is the same name each time, or if this is an issue (i.e. needing to reference different sheets in each individual file, or multiple sheets in the same file) then we can address this if need be, but you can possible change the line to

Set objXsheet = objExcel.Worksheets("SheetName")

 

[Paul Wagner]

Yes, when I use

Set objExcel = New Excel.Application
Set objXsheet = objExcel.Worksheets(1)

I get the message "Method 'Worksheets object' _application failed" and the msg box "Application defined or Object defined failure"

I even changed it to Set objXsheet = objExcel.Worksheets("Sheet1") and same error.

Is it misplaced in the code?

 

[rolaaus]

yes, it is misplaced. you need to have the worksheet open in the Excel/App reference before you can fill the objSheet with the activeworksheet.

Place the most recent line of code I gave you after
objExcel.Workbooks.Open FileName:=strCSVPath

 

[Paul Wagner]

Great news! It's working. Here is the successful code for all to use. I think this thread will hang around awhile. Many Thanks rolaaus and Pat!

'-----------------------------------------------------------------------
' CONVERT CSV FILE TO XLS AND PROCESS XLS ROWS AND COLUMNS
' Opens Excel, imports .cvs file into xls format 
' Iterates xRows and xCols from xls format
' strCSVPath = Me.Filename comes from user screen as unbound text
' The hidden Excel is dumped (existing open Excel file remains unharmed)
' The Pathfile xls is NOT saved
'-----------------------------------------------------------------------
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 to get rid of .xls file if not needed
Kill Pathfile

 

[Paul Wagner]

Here's a footnote to the above code.

Instead of processing the columns separately, evertime I read the row, I load the values I want locally, thus:

Emplid = objXsheet.Cells(xRows, 1).Value
Name = objXsheet.Cells(xRows, 2).Value
PayrollCycle = objXsheet.Cells(xRows, 3).Value
etc.

This made it so much easier to handle local procedures within the For xRows.

I probably should have used a With, just don't know how:

Anyone want to finish it up any further?

 

[rolaaus]

What are you doing with the values in your variables? If you are taking them out of the CSV and importing them into an Access table then you can do that directly without the added variables.

rsTable("FieldName") = objXsheet.Cells(xRows, CoL#).Value

FYI, a "with" just lets you reference an object's properties multiple times without directly referencing it. It's sort of like the difference between pointing at something on your desk every time you talk about it, as opposed to picking up the object and holding it in your hand as you talk about it.

I believe there is a way to use multiple "with" references but you would most likely have to keep track of each one and only be able to reference your most recent "with" until you close it out with an End With. In other words, it might be possible to write somethign like;

Beyond adding the With (which I haven't debugged to see if it works), is there anything else you mean when you say "finish up"?

'-----------------------------------------------------------------------
' CONVERT CSV FILE TO XLS AND PROCESS XLS ROWS AND COLUMNS
' Opens Excel, imports .cvs file into xls format 
' Iterates xRows and xCols from xls format
' strCSVPath = Me.Filename comes from user screen as unbound text
' The hidden Excel is dumped (existing open Excel file remains unharmed)
' The Pathfile xls is NOT saved
'-----------------------------------------------------------------------
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

WITH objExcel
	.Workbooks.Open FileName:=strCSVPath

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

	with objXsheet
		For xRows = 18 To objXsheet.UsedRange.Rows.Count 'rows 1-17 contain headers to be ignored
		    For xCols = 1 To objXsheet.UsedRange.Columns.Count
			Emplid = objXsheet.Cells(xRows, 1).Value
			Name = objXsheet.Cells(xRows, 2).Value
			PayrollCycle = objXsheet.Cells(xRows, 3).Value
		    Next xCols
		Next xRows
	end with
.ActiveWorkbook.Close SaveChanges:=False
.Quit
end with

Set objExcel = Nothing
Set objXsheet = Nothing
' Optional to get rid of .xls file if not needed
Kill Pathfile

 

[Paul Wagner]

Thanks rolaaus. Yes, there was some other intermediate processing of the variables (like removing "-" from an account number, trimming some fields, parsing others, etc.)

At this point it is nearly finished. I'm going to try to put the progress bar in as it does take "some" time and that always makes the user nervous. As Programmers we really need to think of their experience first and our shortcuts second.

Bill are you listening?