Specify folder to save Excel file

Question

Every day I receive about 100 emails in Outlook with similar subjects "Dealer Sales Cockpit - XXXXXXXX", being XXXXXXXX a variable number.

I need to get the numbers at the end of the email subject and write them in a column in an Excel spreadsheet (or in a .txt file, if it's not possible in Excel).

This is code I've found. Problem is, it never writes in "test.xlsx". I can't solve it having never really worked with VBA.

Sub ExportMessagesToExcel()
Dim olkMsg As Object, _
    excApp As Object, _
    excWkb As Object, _
    excWks As Object, _
    lngRow As Long, _
    lngCnt As Long, _
    intPos As Integer, _
    strFil As String
strFil = InputBox("D:\LocalData\xl02926\Documents\Samuel\AutoCockpit\test.xlsx", "Export Messages to Excel")
If strFil <> "" Then
    Set excApp = CreateObject("Excel.Application")
    Set excWkb = excApp.Workbooks.Add()
    Set excWks = excWkb.ActiveSheet
    'Write Excel Column Headers
    With excWks
        .Cells(1, 1) = "Computer"
    End With
    lngRow = 2
    'Write messages to spreadsheet
    For Each olkMsg In Application.ActiveExplorer.CurrentFolder.Items
        'Only export messages, not receipts or appointment requests, etc.
        If olkMsg.Class = olMail Then
            'Add a row for each field in the message you want to export
            intPos = InStrRev(olkMsg.Subject, " ")
            excWks.Cells(lngRow, 1) = Mid(olkMsg.Subject, intPos + 1)
            lngRow = lngRow + 1
            lngCnt = lngCnt + 1
        End If
    Next
    Set olkMsg = Nothing
    excWkb.SaveAs strFil
    excWkb.Close
End If
Set excWks = Nothing
Set excWkb = Nothing
Set excApp = Nothing
MsgBox "Process complete.  A total of " & lngCnt & " messages were 
exported.", vbInformation + vbOKOnly, "Export messages to Excel"
End Sub

When I run it, a pop-up appears and I think it asks for a name for the spreadsheet, but any name I choose seems to already exist, so nothing is created. It still scans my Inbox, as it counts my emails and says "A total of X messages were exported". X varies with the number of emails in my Inbox.

at which line does the popup appear, and what does it actually ask you for/say? — David Zemens
Unless you're providing a full path to the InputBox, you're just giving it a filename and the SaveAs will put it in the current directory. So it absolutely is saving the file, it's just not saving it to the D:\... path that you think it is. — David Zemens
The popup appears after strFil = InputBox("D:\LocalData\xl02926\Documents\Samuel\AutoCockpit\test.xlsx", "Export Messages to Excel") — SamRosignoli
It doesn't ask anything, it just shows the path I've specified in the code — SamRosignoli
I'm using InputBox because it already came with the code, I didn't write anything, I've never used VBA before. So, if you say it's definitely saving somewhere, how can I save in the right folder? — SamRosignoli

David Zemens David Zemens · Accepted Answer · 2017-08-09T20:53:58

Unless you're providing a full path to the InputBox, you're just giving it a filename and the SaveAs will put it in the current directory.

The InputBox above is displaying the Prompt which you gave it, not a Default or initial value. Then, when you get to the SaveAs, when you save a file without a full path, it will be saved in the current directory, which probably differs from the path you have hard-coded in the InputBox prompt.

So it absolutely is saving the file, it's just not saving it to the D:\... path that you think it is.

Your inputbox function should probably be like:

strFile = InputBox(Prompt:="Specify file location for export.", _
    Default:="D:\LocalData\xl02926\Documents\Samuel\AutoCockpit\test.xlsx", _
    Title:="Export Messages to Excel")

Above, we've given a Prompt, a Default value, and a Title. The Default value fills the text-entry field of the InputBox, and you can change it if you'd like.

NB: of course you should ensure that user provided a valid path, and that the file doesn't already exist, and if either of those potential error situations arise, you should write code that manages those errors, but that's outside the scope of this question.

Specify folder to save Excel file

1 Answers