My name is David Lee. I am an information technology professional with more than 30 years of experience in the field. My experience covers almost every aspect of IT from programming (I had to punch my own cards when I started) to management. It’s more than a profession for me though. It’s a calling. I am passionate about technology and its use. Since 2007 Microsoft has rewarded that passion by selecting me as an Outlook MVP (Most Valuable Professional). My other passions include coffee, I’d hang an IV drip if I could, and college basketball. Go Blue Devils!
-
Subscribe
Subscribed
Already have a WordPress.com account? Log in now.
TechnicLee 
Hi David,
I have (perhaps) an easy question for you. I found this code that works and does everything i need, except I’m unable to change the folder where I would like to save the file.
On a previous code, what works is “ChDir “C:\Users\David\Desktop\Folder_Name”
How can I incorporate that line into the below VBA rather than saving it to the root director which seems to be my desktop?
Thanks,
Chris
____________________________________________________
Sub saveRangeToCSV()
Dim myCSVFileName As String
Dim myWB As Workbook
Dim tempWB As Workbook
Dim rngToSave As Range
Application.DisplayAlerts = False
On Error GoTo err
Set myWB = ThisWorkbook
(**DAVID**) myCSVFileName = myWB.Path & “\” & “CSV-Exported-File-” & VBA.Format(VBA.Now, “dd-MMM-yyyy hh-mm”) & “.csv”
Set rngToSave = Range(“C3:H50”)
rngToSave.Copy
Set tempWB = Application.Workbooks.Add(1)
With tempWB
.Sheets(1).Range(“A1”).PasteSpecial xlPasteValues
.SaveAs Filename:=myCSVFileName, FileFormat:=xlCSV, CreateBackup:=False
.Close
End With
err:
Application.DisplayAlerts = True
End Sub
Hi, Chris.
That line is getting the path of the workbook. Change it to this
Hey David, You wrote a macro for me that was terrific. It recently stopped working. When I go to the debug page it highlights this line of code
Set olkRes = olkLst.Restrict(“@SQL=” & Chr(34) & “http://schemas.microsoft.com/mapi/proptag/0x0037001E” & Chr(34) & ” ci_phrasematch ” & “‘” & strFlt & “‘”)
Can you help again?
Hi, Phil.
What changed just before the solutions topped working? Also, what’s the error message?
Nothing I know changed. I am sure there have been updates, but I have been using it on this PC with this software for a long time.
When I enter this URL, it gives me an error message the the resource has been moved. Can I load the resource locally?
http://schemas.microsoft.com/mapi/proptag/0x0037001E
Hi, Phil.
That’s not a clickable URL. It defines an Outlook property and it has not changed. I just ran the code here on my computer. It worked perfectly. What error are you getting when the code fails?
I can send you a screen grab of the error message, here is the section of code that needs to be updated. I think the only issue is the URL
‘Write Excel Column Headers
With excWks
.Cells(1, 1) = “Subject”
.Cells(1, 2) = “Location”
.Cells(1, 3) = “Start Date”
.Cells(1, 4) = “End Date”
.Cells(1, 5) = “Start Time”
.Cells(1, 6) = “End Time”
.Cells(1, 7) = “Duration (Minutes)”
End With
lngRow = 2
Set olkLst = olkFld.Items
olkLst.Sort “[Start]”
olkLst.IncludeRecurrences = True
Set olkRes = olkLst.Restrict(“@SQL=” & Chr(34) & “http://schemas.microsoft.com/mapi/proptag/0x0037001E” & Chr(34) & ” ci_phrasematch ” & “‘” & strFlt & “‘”)
‘Write appointments to spreadsheet
For Each olkApt In olkRes
‘Only export appointments
If olkApt.Class = olAppointment Then
Hi, Phil.
Yes, a screen shot or the exact error number and message you’re getting would help. Also, what version of Outlook are you currently using?
Hi David,
Have tried sending you several mails but can’t seem to get them through. Could you send me an email (to the email address used for creating this post). Hoping you will receive this message.
Best regards David
Tech2
Hi, Tech2.
If your messages can’t get through, then what good would it do for me to email you? If my provider is blocking your messages, then your replies wouldn’t get through to me any more than your original messages. What is it that you want to talk about?