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

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

Bob Phillips wrote:
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)


Bob, thanks for replying. I see I may be asking for a value when it's
really a string I'm looking for. I looked up the table of the class
values he
http://msdn.microsoft.com/library/de...HV01049962.asp
and replaced my table with that one.
I put in you code to replace mine and still got the error.

I looked up the error and it seems that since I started my code with
"Options Explicit", I needed to Dim the variable. So my question is,
can I keep my list which explains what each xl[file type] in the second
column, if I just Dim lbxExcelFileClass as a string?
Also, how do I make sure the form gets displayed?

If I put back my table the way it was originally and Dim
lbxExcelFileClass as a string and make
FileFormat:=lbxExcelFileClass.String. I get another compile error:
"invalid qualifier.
Any ideas?

"socrtwo" wrote in message
s.com...

Bob Phillips wrote:
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)


Bob, thanks for replying. I see I may be asking for a value when it's
really a string I'm looking for. I looked up the table of the class
values he

http://msdn.microsoft.com/library/de...HV01049962.asp
and replaced my table with that one.
I put in you code to replace mine and still got the error.


Shouldn't have if you did it correctly.


I looked up the error and it seems that since I started my code with
"Options Explicit", I needed to Dim the variable. So my question is,
can I keep my list which explains what each xl[file type] in the second
column, if I just Dim lbxExcelFileClass as a string?
Also, how do I make sure the form gets displayed?


What variable? lbxExccelClassFile is the name of your listb os I assume, so
it is already defined.



If I put back my table the way it was originally and Dim
lbxExcelFileClass as a string and make
FileFormat:=lbxExcelFileClass.String. I get another compile error:
"invalid qualifier.
Any ideas?


Don't Dim lbxExcleClassFile, it is a listbox. I just ran another test and

ActiveWorkbook.SaveAs "C:\GetRidof",
FileFormat:=lbxExcelFileClass.List(lbxExcelFileCla ss.ListIndex, 1)

worked fine.

Thanks for staying with this. BTW, I'm using Excel 2003.

Shouldn't have if you did it correctly.

The table should be two columns right? And the the list box should
have two column listed in its properties?

I looked up the error and it seems that since I started my code with
"Options Explicit", I needed to Dim the variable. So my question is,
can I keep my list which explains what each xl[file type] in the second
column, if I just Dim lbxExcelFileClass as a string?
Also, how do I make sure the form gets displayed?


What variable? lbxExccelClassFile is the name of your listb os I assume, so
it is already defined.

The form itself has a different name. It's named lbForm. Does that
matter? Should it appear on its own or do I have to make it appear
with a line of code?

If I put back my table the way it was originally and Dim
lbxExcelFileClass as a string and make
FileFormat:=lbxExcelFileClass.String. I get another compile error:
"invalid qualifier.
Any ideas?


Don't Dim lbxExcleClassFile, it is a listbox. I just ran another test and

ActiveWorkbook.SaveAs "C:\GetRidof",
FileFormat:=lbxExcelFileClass.List(lbxExcelFileCla ss.ListIndex, 1)

worked fine.

"socrtwo" wrote in message
ups.com...
Thanks for staying with this. BTW, I'm using Excel 2003.


Shouldn't be material.

The form itself has a different name. It's named lbForm. Does that
matter? Should it appear on its own or do I have to make it appear
with a line of code?


Shouldn';t be material.

I have posted an example at http://cjoint.com/?kxoJLUfCWS

Bob Phillips wrote:
"socrtwo" wrote in message
ups.com...
Thanks for staying with this. BTW, I'm using Excel 2003.


Shouldn't be material.

The form itself has a different name. It's named lbForm. Does that
matter? Should it appear on its own or do I have to make it appear
with a line of code?


Shouldn';t be material.

I have posted an example at http://cjoint.com/?kxoJLUfCWS

Nice form. Thanks for the help. I've got a new problem, so I'm starting
a new thread. It appears that if I choose any of the Excel formats and
not csv, the script simply produces multiple copies of the original
file instead of exporting each worksheet separately.