I extended the macro to always save an xlsx version first, then save the csv. Thanks, as always, for your help, (all of you!)Įxcel is 16.47.1 from a Microsoft 365 subscription My macros are in my PERSONAL.XLSX workbook so I presume they are not affected by this setting because they run. In Excel Preferences - Security - Macro Security option "Disable all macros with notification" is set. In System Preferences - Security - Privacy, Excel has full disk access. I have tried removing the ".csv" suffix so Excel can add it - no dice. I can manually save the new one-sheet workbook as a CSV to the path above. The command succeeds and the file is created (and then I remove it.) Touch /Users/russ/daynecorp/TaxReturns/2021/BAS2021Q3/QIF/Post-Expenditure-2021Q3-IMP.csv I can copy the path from the MsgBox and paste it into a touch command in a terminal window: I have checked that the file it is trying to create does not already exist. The macro opens a new workbook with the expected data in it, then throws up a box with: ' Display path so can nconfirm path is validĪctiveWorkbook.Sheets(ActiveSheet.Name).CopyĪctiveWorkbook.SaveAs FileName:=sheetPath, FileFormat:=xlcsv, CreateBackup:=False ' sheetPath = "/users/russ/Desktop/" & sheetName & "-" & Range("FY").Value & "Q" & Range("QTR").Value & "-AE.csv" SheetPath = Range("CSVPATH").Value & "/" & sheetName & "-" & Range("FY").Value & "Q" & Range("QTR").Value & "-IMP.csv" ' Make a path using the sheet name, with "-IMP" at the end to indicate it is the import version. SheetName = Replace(ActiveSheet.Name, " ", "-") ' Replace all spaces in sheet's name with dash. ' CSVPATH, FY and QTR are named cells in the CheckList sheet. ' Save the active sheet to a file in this quarter's CSV directory If I save a sheet of workbook AAA.xlsx as SheetB.xlsx, it does exactly what you expect - AAA.xlsx is unchanged and a new workbook is created named SheetB.xlsxīut! If I save a sheet of workbook AAA.xlsx as SheetC.csv, I get a csv file as expected, but it also renames AAA.xlsx to SheetC.csv, pops up a box offering to save that too, and gives the warning about possible data loss - so the original sheet has its name changed ~and~ wants to save itself as a CSV. Update after that update - Nope - still not working properly. Update - I think I have a solution, although every time I think that, I try it again the next day and it is broken again. It fails with error 1004: Cannot access read-only document. I have a macro that wants to write a sheet as a VBA script. Wb.Sheets("Sheet1").Range("A1").Select 'No error.Įven better is using references and trying to avoid using the Selection and Active* objects entirely.This problem is as old as VBA on Mac OS, it seems, but I cannot find a solution that applies in my case. The simple solution is to Activate the object before you Select within it: Private Sub NoOneOhOhFour() Set ws = ThisWorkbook.Worksheets("Sheet2") 'Starting on anywhere but Sheet2 gives an error. Same thing with Worksheets: Private Sub OneOhOhFourVTwo() 'Outputs 'Book1.xlsm' to Immediate window. Set wb = Application.Workbooks("Book2.xlsx") 'Executing with Book1.xlsm active and Book2.xlsx open. Consider this code, which exhibits the same error: Private Sub OneOhOhFour() You simply can't Select a Range in an object that isn't active. It doesn't have anything to do with the reference to ThisWorkbook at all.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |