PDA

View Full Version : Sleeper: Excel to CSV bug



JustinR
07-05-2005, 07:19 AM
Hi all.

I have written a routine to open all the .xls files in a directory, go to each sheet within them in turn, and save them with a unique filename as .csv files.

I see a mad effect that sometimes, in the saved file, I get the top half of one sheet and the bottom half of another, later, sheet! This is, obviously, fatal to my data.

Can anyone suggest a solution please?

Justin.

Here is the code (edited slightly for simplification, should still run!)


Global n as number

Sub OpenAllFilesInCurrentDirectory()
Application.DisplayAlerts = False
n = 0
' Loop through the current directory and open each
' file present
Dim strTempName As String
InputDirectory = "C:\Documents and Settings\jrowles\My Documents\xls\"
OutputDirectory = "C:\Documents and Settings\jrowles\My Documents\csv\"
Debug.Print "Clearing output directory"
' Kill (OutputDirectory & "*.*")
Debug.Print "Reading input directory"
' Make sure InputDirectory is a directory.
strTempName = Dir(InputDirectory, vbDirectory)
Do Until Len(strTempName) = 0
'Debug.Print "Considering opening " + strTempName
' Exclude ".", "..".
If (strTempName <> ".") And (strTempName <> "..") Then
If (GetAttr(InputDirectory & strTempName) _
And vbDirectory) <> vbDirectory Then
If UCase(Right$(strTempName, 4)) = ".XLS" Then
'must take out the 226 and change 5 back to 2 after basic testing
If OpenAndResave(strTempName) Then
Debug.Print "Done file " + strTempName
Else
Debug.Print "Problem with file " + strTempName
End If
Else
Debug.Print "Not opening " + strTempName + " as it is does not fit the format XV...XLS"
End If
Else
Debug.Print "Not opening " + strTempName + " as it is a directory"
End If
Else
Debug.Print "Not opening " + strTempName + " as it is a default directory"
End If
' Use the Dir function to find the next filename.
strTempName = Dir()
Loop
Debug.Print "Finished"
End Sub

Function OpenAndResave(FileName As String) As Boolean
Workbooks.Open FileName:=InputDirectory + FileName
Windows(FileName).Activate
OpenAndResave = SaveSheetsByType(FileName)
ActiveWorkbook.Close
End Function

' Find out what type of sheet this is:
Function SaveSheetsByType(FileName As String) As Boolean
Dim sh As Excel.Worksheet
Dim SheetName As String
Dim i As Integer
Dim count As Integer
SaveSheetsByType = True
For Each sh In ActiveWorkbook.Sheets
SheetName = sh.Name
Sheets(SheetName).Select
SaveSheet FileName, SheetName
Next
End Function

Sub SaveSheet( _
FileName As String, _
SheetName As String, _
extension1 As Integer, _
count As Integer)
Dim NewName As String
n = n + 1
NewName = OutputDirectory & "Sheet" & n & ".csv"
On Error GoTo fail
ActiveWorkbook.SaveAs NewName, _
FileFormat:=xlCSV, _
CreateBackup:=False, _
ConflictResolution:=xlLocalSessionChanges
Exit Sub
fail:
Debug.Print "Could not save current sheet as " + NewName
End Sub

BlueCactus
07-11-2005, 12:05 PM
Don't have a solution for you, but you might try refering to the workbooks by name, rather than ActiveWorkbook. ActiveStuff can be a little problematic sometimes.

JustinR
07-12-2005, 01:31 AM
Don't have a solution for you, but you might try refering to the workbooks by name, rather than ActiveWorkbook. ActiveStuff can be a little problematic sometimes.

Thanks for the help. Any solution may be a little more subtle than that though, because the saveas call applies to the whole workbook, and it is the sheet that is changing, so changing the way of referencing workbook shouldn't make any difference. It's also a slight pain because the workbook name changes when I save it out, although I expect I can hold the name in a var and rename it back after the save.

I'll start looking for myself in a sec, but do you know if it possible to specify the sheet to be saved (and how)? I have considered copying the sheet into a brand new workbook (with a unique - random - name) and saving that, which will prolly be my fallback plan.

Cheers,
Justin.

JustinR
07-12-2005, 03:03 AM
Don't have a solution for you, but you might try refering to the workbooks by name, rather than ActiveWorkbook. ActiveStuff can be a little problematic sometimes.

Sadly I can now confirm that using Application.ActiveWorkbook.Sheet(SheetName).saveas etc still produces the same issue, so I think I am stuck with my Application.wait workaround.

Thank god I don't usually have to work with Microsoft products! This would drive me mad.

Thanks all the same,
Justin.