That is probably because you have loaded the listbox with the constant
names, but it is the constant value that is used by the VBA statement.
Create a 2 column list, one with the names, one with the value, like so,
xlAddin 18
xlCSV 6
etc.
and set the listbox to reference this list, then in the code use
ActiveWorkbook.SaveAs _
Filename:=Left(myfiles(i), Len(myfiles(i)) - 4) & "_" _
& wks.Name, _
FileFormat:=lbxExcelFileClass.List(lbxExcelFileCla ss.Listindex,1)
--
HTH
Bob Phillips
(replace somewhere in email address with gmail if mailing direct)
"socrtwo" wrote in message
ups.com...
Hi,
I'm a newbie to VBA. This is only the second script I've worked with
and I still don't know some basic things.
I found an excellent Dave Peterson script he
http://tinyurl.com/yd9dp3. I modified it slightly with a folder path
input box, and stopped Excel from asking whether to update links by
adding "UpdateLinks:=False" to the Workbooks.Open statement. At any
rate, it extracts all the worksheets from the Excel files in a folder
and converts them to CSV files. I would like to change the script so
that it can convert the Excel files to any file type in the Excel
arsenal.
I found the XlFileFormat class list he
http://msdn2.microsoft.com/en-us/lib...ileformat.aspx.
In addition to the folder path InputBox, I would like to display a
list box where I can choose the file format to covert the files to.
I got as far as creating a form which is populated by values from my
second sheet, which are simply taken from the XlFileFormat class list
which is 43 rows by 2 columns. I tried to use
FileFormat:=lbxExcelFileClass.Value to both display the form and feed
the chosen value into the script. I get an error "Compile Error:
Variable not defined.". I have no code in the list box itself, which
is right now just as below:
Private Sub lbxExcelFileClass_Click()
End Sub
Here is the working script right now where xlCSV stands in where I
would like the value from the list box to be fed in:
Option Explicit
Private Sub CommandButton1_Click()
Dim myfiles() As String
Dim i As Integer
Dim myfile As String
Dim myfolder As String
Dim strpath As String
Dim strfilename As String
Dim wks As Worksheet
myfolder = InputBox("Enter complete path to the Excel files you wish to
convert to CSV format. Put an \ on the end of the path.", "Excel File
Folder Path")
With Application.FileSearch
.NewSearch
.LookIn = myfolder
.SearchSubFolders = True
.Filename = "*.xls"
If .Execute() 0 Then
ReDim Preserve myfiles(1 To .FoundFiles.Count)
Application.StatusBar = "Found Files: " &
.FoundFiles.Count
For i = 1 To .FoundFiles.Count
myfiles(i) = .FoundFiles(i)
Next i
Else
MsgBox "There were no files found."
Exit Sub
End If
End With
For i = LBound(myfiles) To UBound(myfiles)
Application.StatusBar = "Processing #" & i & ": " & myfiles(i)
Workbooks.Open Filename:=myfiles(i), ReadOnly:=True,
UpdateLinks:=False
For Each wks In ActiveWorkbook.Worksheets
wks.Activate
Application.DisplayAlerts = False
ActiveWorkbook.SaveAs _
Filename:=Left(myfiles(i), Len(myfiles(i)) - 4) & "_" _
& wks.Name, _
FileFormat:=xlCSV
Application.DisplayAlerts = True
Next wks
ActiveWorkbook.Close savechanges:=False
Next i
Application.StatusBar = False
End Sub