Shared Excel macro enabled workbook - Excel cannot access the file issue

Question

What I want to do.

Send my Macro enabled Excel document to my colleagues with a working macro that opens a save file dialog and then generates this csv.

What have I done?

I have made a VBA-macro in Excel 2013 and it works fine on my machine. However, when I send this Macro enabled excel-sheet to my colleague he gets:

Microsoft Office Excel cannot access the file 'Path to the document on MY computer'. There are several possible reasons:

The file name or path does not exist.
The file is being used by another program.
The workbook you are trying to save has the same name as a currently open workbook

My Source:

Sub Convert2CSV()
    Dim fileName As String
    fileName = "OrderSedel_" & Format(Now, "yyyy-mm-dd hh mm") & ".csv"

    With Application.FileDialog(msoFileDialogSaveAs)
            .Title = "xxx"
            .AllowMultiSelect = False
            .InitialFileName = fileName
            .FilterIndex = 15
            result = .Show
            If (result <> 0) Then
                ' create file
                fileName = Trim(.SelectedItems.Item(1))
                fnum = FreeFile
                Open fileName For Output As fnum


                ' Write the csv data from form record set
                For i = 7 To ActiveSheet.UsedRange.Rows.Count
                    If ( _
                        (Not Trim(Cells(i, 1).Value & vbNullString) = vbNullString Or _
                        Not Trim(Cells(i, 3).Value & vbNullString) = vbNullString) And _
                        Not Trim(Cells(i, 9).Value & vbNullString) = vbNullString) Then
                            'Check EAN (Trim). If No EAN get Bolist ArtNum
                            If (Trim(Cells(i, 3).Value & vbNullString) = vbNullString) Then
                                Print #fnum, Cells(i, 1).Value & ";" & Cells(i, 9).Value
                                Else
                                Print #fnum, Cells(i, 3).Value & ";" & Cells(i, 9).Value
                                End If
                    End If
                Next i



            ' close file
            Close #fnum
            End If
      End With

End Sub

Screenshot of file xlsm

I don't have 2013 to test on, but can you browse the properties of .FileDialog to see if there's a Default Directory property that you could set? It could be that the default directory is somehow 'stuck' on a path on your machine, and if you set it to something like %My Documents% in your code, it may resolve that at run-time and pick up your coworker's MyDocuments instead of trying to access yours. — FreeMan
In re: my comment above, try setting .InitialFileName "Set or returns a String representing the path or file name that is initially displayed in a file dialog box.". Took me a minute to find it in the MS Docs — FreeMan
This error can be non-macro issue. Do you have some data links (normal Excel feature, Data - connections) in the workbook? Some formulas using specified paths, some other connections to tables? Ilustration of what I mean — kolcinx
@BranislavKollár The correct name should be Excel macro-enabled workbook (see my attached image). Sorry about that. — Joakim M

rgo rgo · Accepted Answer · 2015-04-03T03:34:54

Send the code for them to insert into a macro to run it and see if they get the same error. It ran fine for me (except that I define my variables).

It could be that their copy of Excel is not set up to trust VBA. (Under Settings.)

Shared Excel macro enabled workbook - Excel cannot access the file issue

1 Answers