Late Night VBA Help Needed

garak0410

I coined a phrase...Unreasonable FU's (Follow Up's) from Management...especially those who don't get IT. Tonight is no different.

By tomorrow morning, I've been asked to add some VBA code to an existing code block/module that saves a PDF in a file location...

When we chose a certain Macrom, it will save the Excel file as a PDF via a Macro...it creates the proper directory based upon Job Number and saves a PDF there through an aging (but functional) PDF creator program...(code below)...

Dim BackToSheet As Worksheet
Const sDefaultCOPath As String = "z:\Change_Orders"

Sub email_Selected()
'
' Email_Selected Macro
' Macro recorded 1/14/2015 by Brian 
'
' Keyboard Shortcut: Ctrl+Shift+E
'
'   Application.CommandBars("Stop Recording").Visible = False
'   Application.Goto Reference:="email_Selected"
'-- Do NOT Save Workbook because if they are in the emplate it can cause problems.   ActiveWorkbook.Save
    Dim spdfname As String
    Dim sPDFNameDir As String
    Dim spdfpath As String
    Dim pos As Integer
    Set BackToSheet = ActiveWorkbook.ActiveSheet ' set current sheet so we can come back to it
'-- First lets get the PDF File Name
    Sheets("Form").Select
    spdfname = Trim$(Cells(4, 3).Value)
    If Len(spdfname) = 0 Then Exit Sub ' nothing to print
    pos = InStr(spdfname, ".") 'test for an extension and remove if there
    If pos > 0 Then
        spdfname = Left$(spdfname, pos - 1)
    End If
    pos = InStr(spdfname, "-") 'test for an extension and remove if there
    If pos > 0 Then
        sPDFNameDir = Left$(spdfname, pos - 1)
    Else
        sPDFNameDir = spdfname
    End If
    Dim sMessageSubject As String, sMessageBody As String
    
            sMessageSubject = "Change Order - " & spdfname
            
            sMessageBody = "Change Order - " & spdfname & " has been attached for your review."
            
    spdfpath = sDefaultCOPath & Application.PathSeparator & sPDFNameDir & Application.PathSeparator
   
 'Look for Directory and create it if it does not exist
        MakeDir (spdfpath)
    
    Call PrintToPDFandEMAIL(True, True, BackToSheet.Name, spdfname, spdfpath, , sMessageSubject, sMessageBody)
'   Reactivate original sheet
    BackToSheet.Activate
    
End Sub

I can get around VBA but often struggle in creating new code...how can I make it also save the file as an excel file in same directory? Is there just an easy line of code I can put in to prompt (or silently) save in the directory by this variable? sDefaultCOPath As String = "z:\Change_Orders"

Thanks!

Danp

Not really my forte, but I would think something like this would work --

    ActiveWorkbook.SaveAs _
        Filename:=spdfpath & spdfname & '.xlsx'

JasGot

@garak0410
Is the original document already an excel spreadsheet? and in this function that e-mails the document you also want to "save As" the document?

JasGot

@JasGot said in Late Night VBA Help Needed:

@garak0410
Is the original document already an excel spreadsheet? and in this function that e-mails the document you also want to "save As" the document?

This is what we use in one of our vba apps, it saves the excel to a new name in another folder.

SaveAsFileName = "s:\Sales Docs\Quotes\Sales\" & Forms!Call_Ticket!Combo101 & "-" & Forms!Call_Ticket![Project Name] & ".xls"
xlApp.ActiveWorkbook.SaveAs (SaveAsFileName)


Set xlSheet = Nothing
Set xlBook = Nothing
Set xlApp = Nothing