Solved: how do I save an xls as an xlsx after changes are made in VBA?
Hi everyone. I am trying to make changes and then save my ss as an xlsx, not an xls. The reason for that is I need to have all my ss's in that format for a macro I am running. If the ss's are saved as an xls, my macron fails when I try to read through 300-400 of them and pull out information from each to the main workbook. I think I am doing this right, but is does not work. I am forced to save all my ss's by hand from xls to xlsx one by one.
Thanks in advance for any help on this one.
[VBA]Sub FindText()
Dim wb As Workbook, wbF As Workbook
Dim s As Worksheet, wsF As Worksheet
Dim r As Range
Dim str_File As String
Dim str_Path As String
Dim i As Long
Dim lastrow As Long
Dim LastRowCol As Long
Dim totaltext As Long
Dim totalcount As Long
i = 1
Set wbF = ThisWorkbook
'Name of sheet where results will go
Set wsF = wbF.Sheets("Results")
'Directory where files are stored
str_Path = "P:\data\test code\test files\"
str_File = Dir(str_Path & "*.xlsx")
Do While str_File <> ""
'Set wb = Workbooks.Open(str_Path & str_File, False, True)!!!!Use for no updates of wb's
Set wb = Workbooks.Open(str_Path & str_File, True, False)
For Each s In wb.Sheets
Columns("C:C").ColumnWidth = 50
Columns("D:D").ColumnWidth = 25
Columns("E:E").ColumnWidth = 25
Columns("F:F").ColumnWidth = 25
Columns("G:G").WrapText = True
lastrow = ActiveSheet.UsedRange.Row - 1 + ActiveSheet.UsedRange.Rows.Count
totaltext = lastrow + 2
totalcount = totaltext - 1
Range("G" & totaltext).Value = "Total Elements"
Range("H" & totaltext).Formula = "=COUNT(H1:H" & totalcount & ")"
'Application.End(x1down).Offset(1, 0).Select
i = i + 1
Next s
wb.Save
wb.Close True
'Selection.End(xlToLeft).Select
'Selection.Offset(1, 0).Select
str_File = Dir
Loop
End Sub[/VBA]