Posts by Jumbo Cactuar

And change xlExcel12 to xlOpenXMLWorkbook

And add: application.displayalerts = false to the beginning,or the message will pop up about saving macro free for every file. Set to true at end of code

Updated with cross-post link

Quote from ????706

its works but after that the problem is the files can not open any more.
we saved these files in xlsb format and now we want to saved them in xlsx format
i think we should write a vba to open each file and save it as xlsb formats.
[ATTACH=JSON]{"alt":"Click image for larger version Name:\terorr.jpg Views:\t1 Size:\t15.3 KB ID:\t1206184","data-align":"none","data-attachmentid":"1206184","data-size":"full","title":"erorr.jpg"}[/ATTACH]

this works for me:

Sub LoopAllExcelFilesInFolder()
'PURPOSE: To loop through all Excel files in a user specified folder and perform a set task on them
'SOURCE: www.TheSpreadsheetGuru.com


Dim wb As Workbook
Dim myPath As String
Dim myFile As String
Dim myExtension As String
Dim FldrPicker As FileDialog
Dim fpath As String
Dim wname As String
   


'Optimize Macro Speed
  Application.ScreenUpdating = False
  Application.EnableEvents = False
  Application.Calculation = xlCalculationManual


'Retrieve Target Folder Path From User
  Set FldrPicker = Application.FileDialog(msoFileDialogFolderPicker)


    With FldrPicker
      .Title = "Select A Target Folder"
      .AllowMultiSelect = False
        If .Show <> -1 Then GoTo NextCode
        myPath = .SelectedItems(1) & "\"
    End With


'In Case of Cancel
NextCode:
  myPath = myPath
  If myPath = "" Then GoTo ResetSettings


'Target File Extension (must include wildcard "*")
  myExtension = "*.xls*"


'Target Path with Ending Extention
  myFile = Dir(myPath & myExtension)


'Loop through each Excel file in folder
  Do While myFile <> ""
    'Set variable equal to opened workbook
      Set wb = Workbooks.Open(Filename:=myPath & myFile)
      wname = Left(wb.Name, InStr(wb.Name, ".") - 1)
      fpath = wb.Path & "\" & wname


    
    'Ensure Workbook has opened before moving on to next line of code
      DoEvents
    
    'Save and Close Workbook
    wb.SaveAs Filename:=fpath & ".xlsb", FileFormat:= _
        xlExcel12, CreateBackup:=False
      wb.Close SaveChanges:=True
      
    'Ensure Workbook has closed before moving on to next line of code
      DoEvents


    'Get next file name
      myFile = Dir
  Loop


'Message Box when tasks are completed
  MsgBox "Task Complete!"


ResetSettings:
  'Reset Macro Optimization Settings
    Application.EnableEvents = True
    Application.Calculation = xlCalculationAutomatic
    Application.ScreenUpdating = True


End Sub

place in a different folder to the files you want to change
run it then select folder

[Blocked Image: https://puu.sh/B56tg/f44bb9fdfa.png]

Shouldn't be too difficult, not in atm but have a look at this

https://www.thespreadsheetguru.com/t...a-given-folder

Will just need to modify the save/close part

Edit: ignore this and see above, even better

Quote from AlanSidman

Your explanation is confusing. Suggest you upload a sample worksheet, showing the data above and the expected result (mock it up manually.)

example attached

totals formula formatted as [h]:mm

=SUM(B1:B40)-SUM(COUNTA(B1:B40)*25569)

because your source data contains the date 01/01/1970 which has a numeric value of 25569 which needs to be deducted from each time

Hi,
im unsure how to go about this but it should be possible with for loops / if statements i think.

i have a list of data on sheet("data") in columns A:C in the following format:

I need to copy everything with CountryCode 01 into range "label01" on Sheet("Labels") and repeat for each country code. I usually do this manually with copy/paste values but its very repetitive.

Though if there are more than 10 with same country code, rest will go on to the following label.
(label ranges are 3col x 10row)

example:

CountryCode: 01 - 6 rows (goes into range "label01")
CountryCode: 02 - 14 rows (10 goes into "label02" and 4 goes into "label03"
CountryCode: 03 - 8 rows (goes into "label04")

Any help would be appreciated.

Also posted at:

https://www.mrexcel.com/forum/…into-ranges-criteria.html

Hi, i have data like the following below

[Blocked Image: https://puu.sh/tpBPq/20c57df912.png]

how do i grab the last row of each user? (one with the latest time) im not sure the easiest way to do this

wanting from the above data as example:

[Blocked Image: https://puu.sh/tpBUK/d8a1e63bb4.png]

any help appreciated