Hi there, I have started this code (more or less following Norrain's code about listing files from a folder...) but I see difficulties to meet all the conditions as stated in the subject working as I want. If someone wants to help with this. I have successfully worked out some code in this workbook for my initial need now I find it interesting to expand this code to answer more needs like saving sizes from some large files by saving them using xlsb extension, i realized that a 16Mb .xlsx workbook would be 4Mb using xlsb extension.
Other than that, I find also useful to change files with different
extensions to the same extension so when the saveas dialog box pops up,
another file can be used as the base name without investigating into
multiple file extensions mostly between xls and xlsx... I think some here
might understand the last point...
However for large files, I'm not sure about the consequences of saving as
xlsb for a file that relies on xml code like for the ribbon or else...
I just feel like posting the part of the code I need most help with, i'll
attach the file for the rest of the code if you need. Last, if you find it
easy (as I get to learn more from reading posts than I can contribute,
would it possible to turn some of the code in this workbook into classes. I
mean would it run faster with some dll so there is no need to open each
files but instead have this done in the background?
fldPath = r.Value
lastr = ws.Cells(Rows.Count, 1).End(xlUp).Row
For i = 2 To lastr
Set r = ws.Cells(i, 1)
Workbooks.Open Filename:=fldPath & r.Value
Set w = Workbooks(r.Value)
If r.Offset(0, 2).Value = "xlsx" And r.Offset(0, 1).Value > 8000000 Then
r.Offset(i, 5).Value = "condi1" 'to xlsb
w.SaveAs Filename:=fldPath & r.Value, FileFormat:=50,
CreateBackup:=False '50 is for XLSB ... NOT WORKING
w.Close
ElseIf r.Offset(0, 2).Value = "xlsm" And r.Offset(0, 1).Value > 8000000
Then
r.Offset(i, 5).Value = "condi2" 'xlsb with m mention
ElseIf r.Offset(0, 2).Value = "xls" And r.Offset(0, 1).Value > 8000000
Then
'r.Offset(i, 5).Value = "condi3" 'to xlsb
ElseIf r.Offset(0, 2).Value = "xls" And r.Offset(0, 1).Value < 8000000
Then
'r.Offset(i, 5).Value = "condi4" 'to xlsx
ElseIf r.Offset(0, 2).Value = "xls" And r.Offset(0, 1).Value > 8000000
Then
'r.Offset(i, 5).Value = "condi3" 'to xlsb if macro -m suffix mention
ElseIf r.Offset(0, 2).Value = "xls" And r.Offset(0, 1).Value < 8000000
Then
'r.Offset(i, 5).Value = "condi4" 'to xlsm if macro
End If
'w.Close savechanges:=False
'Set w = Nothing
Next
Function HasProject(wb As Workbook) As Boolean
Dim wbProjComp As String
On Error Resume Next
wbProjComp = wb.VBProject.Name
If Len(wbProjComp) > 0 Then HasProject = True
End Function
Pascal Baro
bpascal...@gmail.com
--
FORUM RULES (986+ members already BANNED for violation)
1) Use concise, accurate thread titles. Poor thread titles, like Please Help,
Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get
quick attention or may not be answered.
2) Don't post a question in the thread of another member.
3) Don't post questions regarding breaking or bypassing any security measure.
4) Acknowledge the responses you receive, good or bad.
5) Cross-promotion of, or links to, forums competitive to this forum in
signatures are prohibited.
NOTE : Don't ever post personal or confidential data in a workbook. Forum
owners and members are not responsible for any loss.
------------------------------------------------------------------------------------------------------
To post to this group, send email to excel-macros@googlegroups.com
To unsubscribe, send a blank email to excel-macros+unsubscr...@googlegroups.com
Convert all excel workbook if relevant-2.xlsm
Description: application/vnd.ms-excel.sheet.macroenabled.12
