Hi Excel Gurus,
I'm back with VBA challenges working between Mac and Windows machines. Earlier I encountered simple formatting errors such as an angled apostrophe and one that goes vertical that made my code unresponsive. Now I have some new challenges that are baffling me. All code is created on a Windows machine.
The code asks the user to select a target workbook. All sheets from target are copied to master macro workbook for processing. The target workbook name is captured along with a date.
On Macs running Excel v 15.29 I get a run time error 1004 after the target workbook is selected in the Open Dialog. Here's the error and why I'm concerned there is a format problem. The cancel portion works and the message box appears as it should saying 'Operation Cancelled'.
Are there different formats of quotation marks that might cause this error?
But on a Mac with Excel v 15.24 the code operates as it should and the target workbook's sheets are successfully copied to the master workbook and the user can enter the date.
Run-time error 1004
" could not be found. Check the spelling of the file names, and verify that the file location is correct.
If you are trying to open the file from your list of most recently used files on the File menu, make sure that the file has not been renamed, moved, or deleted.
Here's the code.
Any thoughts on this are greatly appreciated.
Thanks in advance,
Matt
I'm back with VBA challenges working between Mac and Windows machines. Earlier I encountered simple formatting errors such as an angled apostrophe and one that goes vertical that made my code unresponsive. Now I have some new challenges that are baffling me. All code is created on a Windows machine.
The code asks the user to select a target workbook. All sheets from target are copied to master macro workbook for processing. The target workbook name is captured along with a date.
On Macs running Excel v 15.29 I get a run time error 1004 after the target workbook is selected in the Open Dialog. Here's the error and why I'm concerned there is a format problem. The cancel portion works and the message box appears as it should saying 'Operation Cancelled'.
Are there different formats of quotation marks that might cause this error?
But on a Mac with Excel v 15.24 the code operates as it should and the target workbook's sheets are successfully copied to the master workbook and the user can enter the date.
Run-time error 1004
" could not be found. Check the spelling of the file names, and verify that the file location is correct.
If you are trying to open the file from your list of most recently used files on the File menu, make sure that the file has not been renamed, moved, or deleted.
Here's the code.
Code:
Sub OpenTarget()
' I created an If/Then statement in order to determine the variable subQuit value used elsewhere.
Dim FileToConsolodate As Boolean
FileToConsolodate = Application.Dialogs(xlDialogOpen).Show
If FileToConsolodate Then
'MsgBox "File Opened"
subQuit = 0
Else
MsgBox "Operation Cancelled"
subQuit = 1
Exit Sub
End If
Application.ScreenUpdating = False
Application.DisplayAlerts = False
'Now opened workbook name is captured here as public string. Short book name is also captured- losing last 14 characters
GetTargetName = ActiveWorkbook.Name
'shortening the name by removing the dates for version control- this will allow of auto-populate the Save As dialog.
ShortTargetName = Left(GetTargetName, Len(GetTargetName) - 14)
newdate = InputBox("Enter Today's Date")
file_name = ShortTargetName & "_" & newdate
'copy each sheet to MasterScript
Dim wb1 As Workbook
Set wb1 = Workbooks(GetTargetName)
Dim wb2 As Workbook
Set wb2 = Workbooks("MasterScript.xlsm")
Dim i As Long
For i = 1 To wb1.Sheets.Count
wb1.Sheets(i).Copy After:=wb2.Sheets(wb2.Sheets.Count)
Next
' create an Index page. Count sheet starting at sheet 2 and placing Index at sheet 2 to keep 1 the home page with buttons
wb2.Activate
Application.DisplayAlerts = False
If ActiveSheet.Name = "Index" Then
ActiveSheet.Delete
End If
Application.DisplayAlerts = True
Dim myCount As Long, writeRow As Long
writeRow = 2
' new sheet is created call Index
Worksheets.Add Before:=Sheets(2)
Sheets(2).Name = "Index"
'adding column header in A1.
Range("A1").Select
Selection.Value = "Tab Index"
For myCount = 3 To Sheets.Count
Sheets("Index").Hyperlinks.Add Anchor:=Range("A" & writeRow), Address:="", SubAddress:="'" & Sheets(myCount).Name & "'!A1", TextToDisplay:=Sheets(myCount).Name
writeRow = writeRow + 1
Next myCount
Workbooks("MasterScript.xlsm").Activate
Worksheets.Add Before:=Sheets(2)
Sheets(2).Name = "RecordingScript"
Range("A1").Select
ActiveCell.Value = "File Path"
Range("B1").Select
ActiveCell.Value = "File Name"
Range("C1").Select
ActiveCell.Value = "Recording Prompts"
Range("D1").Select
ActiveCell.Value = "Notes"
Call anotherModule
Call anotherModule2
Workbooks("MasterScript.xlsm").Activate
Sheets(3).Activate
Application.DisplayAlerts = True
Application.ScreenUpdating = True
End Sub
Any thoughts on this are greatly appreciated.
Thanks in advance,
Matt