S
socrtwo
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 here:
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 here:
http://msdn2.microsoft.com/en-us/library/microsoft.office.interop.excel.xlfileformat.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
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 here:
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 here:
http://msdn2.microsoft.com/en-us/library/microsoft.office.interop.excel.xlfileformat.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