Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

ActiveWorkbook.SaveAs in /TMP?

Posted on 2016-09-21
3
Medium Priority
?
56 Views
Last Modified: 2016-10-13
Please forgive what may be astonishing ignorance.

On a PC, it works, but I can't get it to work on a Mac - Goal is to save a temporary Excel with macros file (so it will be deleted thereafter) with a standardized name for emailing. I'd like to do so in the TEMP directory. Here it is for the PC:

ActiveWorkbook.SaveAs Filename:=Environ("temp") & "\" & Range("J112").Text & " - MCR " & Range("D112").Text, _
         FileFormat:=52, CreateBackup:=False

I've tested various combinations without success. What to do?
Thanks!
0
Comment
Question by:Dave Evans
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
3 Comments
 
LVL 9

Expert Comment

by:Koen
ID: 41809796
i don't know how the save command needs to work on a mac...but good practice for debugging is creating a variable for your filename and save on the next line.
then when you step through your code, you can inspect the actual value of your MyFilename variable before the error hits...

Also when adding cell content to a filename you should consider error handling for illegal characters
I always use a function for that (that I found here on EE)

Function strLegalFileName(strFileNameIn As String)

'this function removes all illegal characters from a String used as sheetname and filename

    Dim i As Integer
    
    Const strIllegals = "\/|?*<>"":"
    strLegalFileName = strFileNameIn
    For i = 1 To Len(strIllegals)
        strLegalFileName = Replace(strLegalFileName, Mid$(strIllegals, i, 1), "_")
    Next i
End Function

Open in new window

0
 
LVL 14

Accepted Solution

by:
Zack Barresse earned 2000 total points (awarded by participants)
ID: 41809818
It won't work on a Mac, because there is no folder like that. Think about utilizing another folder. Here is a good place to find code to get special folders on a Mac:

http://rondebruin.nl/mac/mac007.htm

HTH

Regards,
Zack Barresse
0
 
LVL 14

Expert Comment

by:Zack Barresse
ID: 41841647
Without additional information from the OP, this is the most succinct answer available.
0

Featured Post

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article describes how to use a set of graphical playing cards to create a Draw Poker game in Excel or VB6.
We live in a world of interfaces like the one in the title picture. VBA also allows to use interfaces which offers a lot of possibilities. This article describes how to use interfaces in VBA and how to work around their bugs.
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.

721 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question