Excel 2011 Mac - VBA - GetData (path)

[superlbc]

Hi,

I have a macro that imports information from closed wordbooks that are stored in a subfolder where the main file is. That folder may vary depending on the user.

I have got a macro to do so. It works ok on windows. However, I have to port it to Mac now. And part of the code does not work:

Sub GetDataFromClosedTimesheets()

On Error Resume Next

Dim wb As Workbook
  Msg = "Would you like to import the latest data from the timesheets?"
  Ans = MsgBox(Msg, vbYesNo + vbQuestion)
  If Ans = vbNo Then Exit Sub
  If Ans = vbYes Then Application.ScreenUpdating = False ' turn off the screen updating
    Set wb = Workbooks.Open(ActiveWorkbook.Path & "\Market1\Supplier1.xlsx", True, True)
    ' open the source workbook, read only
    With ThisWorkbook.Worksheets("Supplier1")
        ' read data from the source workbook
        .Range("e6:bd17").Value = wb.Worksheets("SUMMARY").Range("f28:be39").Value
        .Range("e23:p34").Value = wb.Worksheets("SUMMARY").Range("f45:q56").Value
        .Range("e40:h51").Value = wb.Worksheets("SUMMARY").Range("f62:i73").Value
    End With
    wb.Close False ' close the source workbook without saving any changes
    Set wb = Nothing ' free memory
      
      ' next language
        Set wb = Workbooks.Open(ActiveWorkbook.Path & "\Market1\Supplier2.xlsx", True, True)
    ' open the source workbook, read only
    With ThisWorkbook.Worksheets("Supplier2")
        ' read data from the source workbook
        .Range("e6:bd17").Value = wb.Worksheets("SUMMARY").Range("f28:be39").Value
        .Range("e23:p34").Value = wb.Worksheets("SUMMARY").Range("f45:q56").Value
        .Range("e40:h51").Value = wb.Worksheets("SUMMARY").Range("f62:i73").Value
    End With
    
    wb.Close False ' close the source workbook without saving any changes
    Set wb = Nothing ' free memory
        Application.ScreenUpdating = True ' turn on the screen updating
    
    MsgBox "Timesheet information has been updated", vbInformation
End Sub

Private Function GetData(Path, File, Sheet, Address)
      Dim Data$
      Data = "'" & Path & "[" & File & "]" & Sheet & "'!" & _
            Range(Address).Range("A1").Address(, , xlR1C1)
      GetData = ExecuteExcel4Macro(Data)
End Function

I am assuming that it fails at getting the path.

I have to say my VBA knowledge is very limited and I have managed to build a few macros following examples from here and there.

Can you please helping me on making this work on Excel 2011 for Mac?

Is it possible have code that detects if the file is being opened on Windows or Mac and it chooses the right code?

The macro is triggered when the the file is opened by the following code:

Private Sub Workbook_Open()
Run "GetDataFromClosedTimesheets"
End Sub

Thank you,

Luis

 

[Pti Guily]

Hi Luis,

I have exactly the same problem and ended up with the same conclusion.
Did you solve it ?
Tanguy

 

[superlbc]

Hi,

No, unfortunately I didn't manage and I haven't had the time to explore it any further.

Please let me know if you find the way to do it.

Thank you.

 

[Pti Guily]

Some idea but since this is my fist afternoon in VBA I doubt I my solve it promptly.

I found another post explaining about a potential approach :
http://www.mrexcel.com/forum/showthread.php?t=520938

If Application.PathSeparator = ":"
Then strPath = "Macintosh HD:"
Else strPath = "C:\"
End If

In my case I try to call a macro stored in an excel file in a sub directory but the principle of the problem is the same as yours

My sub directory is "End" so combining this approach with
Application.Run ActiveWorkbook.Path might do something like

If Application.PathSeparator = ":"
Then Application.Run ActiveWorkbook.Path & ":End:" & "MyExcelFile.xls
Else Application.Run ActiveWorkbook.Path & "/End/" & "MyExcelFile.xls
End If

This is not working but I think it has something to do with my poor knowledge and understanding on VBA.

What do you think ?
Any hint ?

Tanguy

 

[superlbc]

Hi Tanguy,

I have finally managed to do it with the following method:

If Application.OperatingSystem Like "*Mac*" Then
Set wb = Workbooks.Open(ActiveWorkbook.Path & ":Old versions:Market1:Supplier1.xlsx", True, True)
Else
Set wb = Workbooks.Open(ActiveWorkbook.Path & "/Old versions/Market1/Supplier1.xlsx", True, True)
End If

Hope it helps you out if you didn't find a way around it yet.

Luis

 

[Pti Guily]

I'll look at it in some days from now and will post a reply after.

Many thanks for your feed back as it might help me in my approach.

Take care
Tanguy

 

[Jon Peltier]

I know it's an old thread, but here's a slight improvement.

If Application.OperatingSystem Like "*Mac*" Then
Set wb = Workbooks.Open(ActiveWorkbook.Path & ":Old versions:Market1:Supplier1.xlsx", True, True)
Else
Set wb = Workbooks.Open(ActiveWorkbook.Path & "/Old versions/Market1/Supplier1.xlsx", True, True)
End If

or more simply

Set wb = Workbooks.Open(ActiveWorkbook.Path & Application.PathSeparator & "Old versions" & Application.PathSeparator & _
    "Market1" & Application.PathSeparator & "Supplier1.xlsx", True, True)