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:
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
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.Code
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?
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.Code
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,
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:Code
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.
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:Code
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?
Re: Rogue formatting: "Normal" changing from general to time
Re: Calculation of Breach
If 8 am to 6 pm:
If 8 am to 7 pm:
I changed the hour marker in two if statements from 10 to 11 because 11 am is now eight hours prior to closing time.
Add the following to stop weekend calculation:
All that does is add two days if the ticket is priority 3 and opened on a Friday.
You're still going to have trouble with holidays in that scenario. In the past, I've solved for holidays by having a table of holidays, and using a VLOOKUP function to see how many days the office would be closed (a table of dates and how many extra days to add). If the open date is before a three day weekend, I add 1. If it's the day before Thanksgiving, add 2.
Re: Calculation of Breach
1) It changes the formula, yes. If you were opening at 8 am instead of 6 am, you'd need to add 14 hours instead of 12. The part where you add 0.5 (twelve hours) would need to change to 14/24 (14 hours) if you open at 8 am, or 15/24 if you open at 9 am. The numerator is driven by how many hours from when you close to when you open.
2) Priority 4 would complicate in two ways. First, the IF statements at the beginning of the formula would need another layer to account for the amount of time being added. Second, if you're saying the SLA is greater than the amount of business hours in the day, you're adding additional complexity. Consider the request that comes in at 5 p.m., it's not going to be due until the day after tomorrow. If you plan to go this route, I would simplify the whole thing by creating a more robust table with priorities and logic built in to determine how much extra pad you have to add to the new priority. You can then use VLOOKUPs to bring in the extra time by priority. Your formula would be much simpler; something like this:
VLOOKUP 1: the amount of hours each priority has
VLOOKUP 2: if the ticket is after the cut-off, add 12 hours so we know we can finish it the next day
VLOOKUP 3: if the ticket is at a different time of day, we add another 12 hours (the 5:00 pm example with a 16 hour service level)