Posts by jronaldlee

Ha! Just enough to be dangerous. A bit of my education hanging on by a thread.

This feels like a weird gap on Microsoft's part... they offer the NPER function, but the majority of banks these days accrue interest more frequently than the gap between payments. Shouldn't be hard to build out, IMO... but I don't feel like going through building a custom formula so here we are.

I can use that for total compounding interest accrued, but it would assume no payments made. I could do a table to build out the loan with number of days, compound the interest, and reduce the amount of principal for each period (and get the number of periods that way), but I am hoping to find the equivalent of NPER that uses daily compounding when considering how many payments will be needed. If NPER is doing this, I'm getting some funny results and need to reconsider my inputs...

Howdy, folks. I'm familiar with NPER, but I've come to realize that it's compounding interest based on the number of periods, whereas my bank is compounding interest daily. Is there an alternative formula that will calculate the number of periods remaining on a loan that will allow for interest to compound daily, and recognize that payments are made at a different time scale (e.g., biweekly or monthly)?

Thanks for the ideas - will give these a try. Much appreciated.

I've inherited some spreadsheets that are a formatting nightmare. I tried to script away the fills, fonts, colors, borders, and such, but sometimes there are so many different things on the sheet that Excel balks.

Next I manually copied and pasted the data as values into a new workbook, I can reduce the size by 90%. The problem is there's a lot of data and it's not all continuous (multiple tables on each sheet, different size ranges on each version of the workbook, etc).

I have a script that creates a new worksheet and brings EVERYTHING over like this:

Sheets(WSn2).Range("a1:cz100000").Value2 = Sheets(WSn).Range("a1:cz100000").Value2

But if I use that hammer to do it, the workbook is referencing all these cells as having values even though many of them are, in fact, blank. I'm not getting as much size reduction as I would like.

Is there a better way?

I have a large cache of legacy documents that were stored with someone using the insert date function of Word. Now, whenever one opens these documents, the field reverts to today's date. I understand the value of said functionality, but these documents should represent with accuracy the create date, not today's date. I know how to update the field to show this manually, but I'm wondering if there is a way to force the issue.

In other words, If date field is present, change value from Today to Create Date.

Is this possible? Automatic would be preferred, but a button tied to a macro stored in personal would work.

So nice when a single word does the trick. Thank you Kenneth!

I have a macro that generates TXT files, but the encoding is not useable by my downstream systems. This code generates documents which Notepad++ identifies as UCS-2. Only ANSI can be read by my downstream system.

Here's what's giving me the wrong format:

[VBA]Set fso = CreateObject("Scripting.FileSystemObject")
Set FileOut = fso.CreateTextFile(FileName, True, True)
FileOut.Write MetaData 'Metadata is a previously established text string variable
FileOut.Close[/VBA]

Thanks in advance...

Re: Open workbook, collect data AND Sheet name from every sheet

I think that's what I need. Thanks very much, Alan! I'm off to play with it and see what I can do.

I can wrap my ahead around opening another document, grabbing data from it, and bringing it back, but I've only done that where I've been specific as to which sheet to get the information from. Now I need to open another workbook, and loop through every sheet, collect the data in A1:E30, and bring it back to the spreadsheet from which I'm running the data and add it to a table.

• I don't quite have a handle on how to set up the loop
• I also need to grab the sheet name and bring that over.

The data I'm inheriting is spread across 100 sheets instead of one table, and the sheet name is part of the data - yuck. Any suggestions? Thanks in advance!

Re: Mail Merge output to named individual files

Figured this out. Here's the code I'm using, which is a little sloppy and inelegant in particular when it comes to the total count of records for the for/next loop. I'm manually entering that in the mail merge data file. If anyone has a suggestion on how to count the number of records and just use that, I'd love to incorporate it.

Dim FileLocation As String 'Field must be derived from data source
Dim DocName As String 'Field must be derived from data source
Dim Count As Integer 'Field must be derived from data source




Count = ActiveDocument.MailMerge.DataSource.DataFields("Count").Value 'how many docs in merge?
ActiveDocument.MailMerge.DataSource.ActiveRecord = wdFirstRecord 'Make sure you start at the beginning!




For i = 1 To Count




    With ActiveDocument.MailMerge
        .Destination = wdSendToNewDocument
        .SuppressBlankLines = True
        With .DataSource
            .FirstRecord = ActiveDocument.MailMerge.DataSource.ActiveRecord
            .LastRecord = ActiveDocument.MailMerge.DataSource.ActiveRecord
            'Fill in variables:
            FileLocation = .DataFields("FileLocation").Value 'added code
            DocName = .DataFields("DocName").Value 'added code
        End With
        'Merge the active record:
        .Execute Pause:=False
    End With
    
    'Save the resulting document. Note modified Filename!
    ActiveDocument.ExportAsFixedFormat OutputFileName:= _
        FileLocation + DocName + ".pdf", _
        ExportFormat:=wdExportFormatPDF, OpenAfterExport:=False, OptimizeFor:= _
        wdExportOptimizeForPrint, Range:=wdExportAllDocument, From:=1, To:=1, _
        Item:=wdExportDocumentContent, IncludeDocProps:=True, KeepIRM:=True, _
        CreateBookmarks:=wdExportCreateNoBookmarks, DocStructureTags:=True, _
        BitmapMissingFonts:=True, UseISO19005_1:=False
    
    'Close the resulting merge document, don't save changes
    ActiveWindow.Close savechanges:=False
    
    'Back to the original template document, advance to next record before doing it all again
    ActiveDocument.MailMerge.DataSource.ActiveRecord = wdNextRecord
    
Next i

Hi, all, I need to run a mail merge that results in individual documents being saved. Ideally, when the merge takes place the name of each document could come from a field in the data set. Any suggestions for how to achieve this?

Thanks!

Re: Send email from Excel, set 'Direct Replies To' via script

Way simpler... and it works great, too! Thank you so much.

I need to send emails and have the "Direct Replies To" field updated to a different address.

This is my code. I've remarked what I tried, which is NOT working.

Dim OutApp As Object        
        Dim OutMail As Object
        Dim EmailBody As String
    
        Set OutApp = CreateObject("Outlook.Application")
        Set OutMail = OutApp.CreateItem(0)
    
        'Send email
        On Error Resume Next
        With OutMail
            .to = Sheet2.Range("b3").Value
            .ReplyRecipients = """Test Reply"" <[email protected]>" 'This isn't working with Outlook 2010. The Direct Replies To field does not update.
            .Subject = Sheet2.Range("b5").Value
            .HTMLBody = EmailBody
            .Send
        End With
        On Error GoTo 0
    
        Set OutMail = Nothing
        Set OutApp = Nothing

Re: Combine text string with clipboard contents

Works like a charm. Thanks very much! :smile:

I'm working on a macro that uses the contents of the clipboard. The text string has many fields in quotations, and Excel is stripping the first two quotes when I paste the data into the spreadsheet.

For example, if the text string in the clipboard is:
"Sample One" or "Sample Two" or "Sample Three"

The user starts with this text string in their clipboard, then fires the macro. When the text string hits the spreadsheet (via ActiveSheet.Paste), the value changes to this:
Sample One or "Sample Two" or "Sample Three" - the first two sets of quotes are stripped.

My macro uses the quotes to convert this into a table of the three text strings in quotes. I'm wondering if there's a way to get at the contents of the clipboard without pasting it onto the spreadsheet? If so, I could put an apostrophe in front of the text string and make this thing work.

Thanks in advance,

James

Re: Find value w/VB creates error, ignoring "On Error Resume Next"

Your code is working flawlessly. Much cleaner, too - thanks very much. I learned something new today!

(Re: two o's, I somehow deleted the hard return from one line to the next when I pasted the code. I edited the original post to display it as it was in my code)

I have a bit of VBA that I've used on multiple occasions. I look for a row of data in a table, and if it's not there, I add it. If it is, I overwrite the data. Here's how I do it:

DataFound = 0
On Error Resume Next
DataFound = Columns(1).Find(What:=DateTime, After:=Cells(1, 1), LookIn:=xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False).Row
On Error GoTo 0
'If data is not in the table, add the data 
If DataFound = 0 Then
    InputRow = Sheet7.Range("a" & Rows.Count).End(xlUp).Row
Else
'If data is in the table, overwrite the data
    InputRow = DataFound - 1
End If

I've used similar code before with great success, but for some reason I'm now getting "Run-time error '91': Object variable or With block variable not set" and it's the DataFound = Columns(1).Find... line that is causing the error. I expect it to error... the data isn't there, right? But that's why I have the line of code "On Error Resume Next" there. Very confused as to why that isn't happening.

Any ideas?

Re: Excel to Outlook email with formatting

Thanks very much - just what I needed.

I use a series of formulas and user inputs to generate a series of text entries which will then comprise the body of an email. This is the code that is working for me at present:

Dim OutApp As Object
    Dim OutMail As Object
    Dim EmailBody As String




    Set OutApp = CreateObject("Outlook.Application")
    Set OutMail = OutApp.CreateItem(0)




    EmailBody = Sheet6.Range("b3").Value & vbNewLine & Sheet6.Range("b4").Value & vbNewLine & vbNewLine
    EmailBody = EmailBody & Sheet6.Range("b6").Value & vbNewLine & Sheet6.Range("b7").Value & vbNewLine & vbNewLine
    
    
    For i = 1 To 60
        If Sheet6.Range("b8").Offset(i, 0).Value = "" And Sheet6.Range("b8").Offset(i + 1, 0).Value = "" Then GoTo ExitiLoop
        EmailBody = EmailBody & Sheet6.Range("b8").Offset(i, 0).Value & vbNewLine


    Next i




ExitiLoop:




    On Error Resume Next
    With OutMail
        .to = Sheet6.Range("e6").Value
        .Subject = Sheet6.Range("b1").Value
        .Body = EmailBody
        .display   'Use .Display, .Save, or .Send
    End With
    On Error GoTo 0




    Set OutMail = Nothing
    Set OutApp = Nothing

In the for loop, there are some lines that I would like to appear as bold text in the email. I can detect those lines easily enough; it's formatting them in Outlook that is the challenge. Any suggestions?