I have a need to import 1600 Lotus files in .123 format (not WK*) into Excel. Either, where can I get the Excel drivers to import .123 files, or where can I get the object model objects, properties, methods, and the scripting syntax used for Lotus's version of VBA, so I can automate the conversion of .123 files to .wk3 files within Lotus.
To automate the Lotus part, I have the 1600 file names (the files all being in the same folder) in a column in a .123 file. I want to:
IF the active cell is not blank
Read the file name
Import the file as .123 from an input folder
Do a saveas to .wk3 format to an output folder
Close the file
Move down a cell
OTHERWISE done
Already tried the Lotus macro recorder. It uses hard cell addresses. I need soft references to the value in the current cell so I can process the list. In Excel, it would be simple for me, as I know the object model and scripting syntax. I just burned two hours proving that Lotus has a model and syntax sufficiently different from VBA that I need a programming reference.
I have 4000 lines with car make, model, year as the column headings in MS Excel. The years are formatted as 1995 - 1997 (for example). But my website CMS requires the years to be formatted as 1995, 1996, 1997 in order to properly upload. Each of the lines has a different year period.
Is there a function that I can apply to these 4000 lines to change the format? I have a feeling concatenate is involved but can't figure out how to create a formula that knows when to stop after it has reached the final year.
I'm attempting to import around 200 (and growing!) separate text files into Excel. I am using the formula below to import the text file and then using a separate macro to select the information I need, copy it into another spreadsheet, and then run the import macro again.
However, I have a problem in that my import macro gives me 'Run-time error '1004:
Application defined or user defined error''. At first this wasn't a problem as the information is pasted into the spreadsheet despite the error anyway. However, now that I am looping the macro it is obviously causing more problems as it prevents the loop. I would really appreciate it if anyone knows of a work-around or can spot an error in the coding to resolve this!
The code below shows is for the import macro only:
Sub ImportTextFile(FName As String, Sep As String)
Dim RowNdx As Long Dim ColNdx As Integer Dim TempVal As Variant Dim WholeLine As String Dim Pos As Integer Dim NextPos As Integer Dim SaveColNdx As Integer
>I am trying to convert a Lotus file over to Excel, and am having some trouble >converting an error handling dget function. > >=IF(ISERR(DGET(Databaseread,"Name","GROUP >ID"=GroupNumber)),VLOOKUP(GroupNumber,Databaseread,4,FALSE),DGET(Databaseread,"NAME","GROUP ID"=GroupNumber)) > >This is the function that was used in Lotus; it returns the name of a >company by looking at the ID number. I need to keep it as pure as possible to >the Lotus file. ....
Lotus 123's @DGET (and other database functions) are much more sophisticated than Excel's counterpart functions. 123's can use criteria expressions in the function calls. Excel's require criteria ranges.
In this particular case, there's no need to use DGET at all. There's a single criterion term, so VLOOKUP is sufficient. If the "Name" column were the 4th column in Databaseread, then try
=VLOOKUP(GroupNumber,Databaseread,4,0)
Explanation: it appears you're just trying to find a particular group number. DGET (and @DGET in 123) returns an error if there's more than one entry. VLOOKUP returns the first matching entry. You're formula makes it clear you want either the only matching entry or the first matching entry. However, when there's only one matching entry it's also the first matching entry, so VLOOKUP alone would have returned the desired result.
I suspect you have other formulas that are more complicated, but you believed the formula above would be a reasonable sample to provide. Not so. If you have more complicated D-function calls, show them, not the simple ones.
I have an excel file that will be downloaded by multiple users and saved locally. I was wondering if there was a way to build code into the file so that everytime it is saved an email would be created in lotus notes from the user to me with a copy of the file attached. Is this possible?
I have developed many macros that generate automatic emails from excel using Outlook, with the excellent help I've found here. Now due to recent buyouts, we have changed our email from Outlook to Notes. I'm looking for the simplest method of changing my macros so they will work with Notes. Below is the typical code I'm currently using. Is there an easy way to change it for Notes?
Public Sub SendEmail()
Dim OutApp As Object Dim OutMail As Object Dim Dist As String Dim supplierinfo As Range
I have problems with passwords in excel 97. The perfect Script below wheel in excel 2000 and 2003, but presents problems in excel 97.
Function EnviaBancoALTEND(Para As String, Doc As NotesDocument, DB As NotesDatabase, Password As String)
Dim ws As New NotesUIWorkspace Dim s As New NotesSession Dim Doc_Mail As NotesDocument Dim RTItem As NotesRichTextItem Dim object As NotesEmbeddedObject
Dim xlApp As Variant Dim xlsheet As Variant
'cria o excel Set xlApp = CreateObject("Excel.application") xlApp.Visible = False xlApp.Workbooks.Add Set xlsheet = xlApp.Workbooks(1).Worksheets(1) XlApp.Windows(1).DisplayGridlines=False
I am developing a VBA program that will take rows of data from a worksheet and create Lotus Notes Calendar entries.
My code works fine until it wants to add a calendar entry dated in the past... at this time I get a pop up message on Notes saying "This entry is being created in the past.
Create anyway?" and an option to click yes or no..
how to either suppress the pop up or programatically supply a "YES".
I'm in the middle of creating an auto-mailer via Excel VBA (and got most of it working) but I've run across a small(ish) snag.
To save me about 3 hours work, I need to get the subject of the stationery, the "To" list, the "CC" list and the "BCC" list for every stationery (currently around 150).
Is there any way of getting VBA to read all the stationeries on our inbox (it's shared) and giving me the info I need?
I have yet to find ANY information online relating specifically to emailing any given range in Excel via Lotus Notes. The codes that do exist out there either fail to fully copy the range or relate to attaching an excel file into a Lotus Notes Memo.
Below I have copied the code the I am working with, but I keep getting the following error: Error # 438, Object doesn't support this property or method. After making some minor tweaks, it might then give me another error, Error 424 Object Required, but the range then doesn't copy into the Lotus Notes memo at that point.
I have highlighted the area that I think the code is failing at, that being the actual act of copying/pasting the range selected into the Lotus Notes Memo. With the current code, the selected range does get fully copied into a new memo in Lotus notes, but these errors populate in Excel.
Can anyone offer any suggestions to resolve this issue? I have spent hours on this and I'm sure there are many others out there that might be able to benefit from a solution as well.
I'was given a piece of code to read through stationeries in Lotues Notes so I could quickly retrieve all the Sendto, CC and BCC information.
I'm now trying to modify that so I can check two specific folders in a mailbox and retrieve all the dates that e-mails have been received on. Code is here:-
Sub new_reader() Dim Maildb As Object, view As Object, Session As Object, entry As Object, entries As Object
Sheets("Sheet4").Select counter = 2
Set Session = CreateObject("Notes.NotesSession")
Set Maildb = Session.GETDATABASE("servername", "boxname") If Maildb.IsOpen = False Then Maildb.OPENMAIL End If
I received an excel spreadsheet through lotus notes e-mail. I opened it from the e-mail and worked several hours on it, sporadically hitting "save" along the way. However, I do not recall having ever done "Save As" and specifying a location to save it in. I later closed excel. Now I cannot locate the spreadsheet (I have searched). Do you know where it might be located or how I might recover it? When you open spreadsheets directly from e-mails and just hit save (not save As),
I am looking to build a macro that can automatically email a recipient upon their name being selected from a drop down list. It designed to be for a warehouse, so when new packages come in we can scan them in and have excel send an email out once our receiving team determines who it is for. With their being multiple recipients, I would like the code to have an IF type function that choosing the correct recipient. the Column titles "Recipient/Dept" is where the dropdown list is located.
It would be ideal for the code to just utilize an open Lotus Notes Session and use that to send the email.
Attached is the document with what I have thus far : Warehouse barcode test.xlsm
I have a macro that sends a custom email through lotus notes. It works, but will only generate 1 email instead of looping through my list. If I have "next r" at end I get email of 1st record. If I put "next r" before "On Error GoTo Audi"
I get last recorded emailed but not 1st 2. I don't know how to get it to send all. It seems the for next loop is jacked up.
For you to see macro work you need to have lotus notes and put email address in column B.
I am wanting to send an automatic email notification to several lotus notes accounts once a specific cell in my excel spreadsheet has been entered in (It does not matter what is entered into the cell).
I have a networked spreadsheet that users access, process data, and then e-mail the results to a common recipient.
Until recently we have all been on Lotus Notes - and my e-mail VBA worked OK.
However, some users are now using Outlook (the new Corporate standard) while some remain on Lotus Notes. The Lotus Notes users now say the e-mail part of the process no longer works for them.
Can VBA detect whether users are on Outlook or on Lotus Notes?
Should I give them a prompt - "Which e-mail do you use? Enter L for Lotus Notes or O for Outlook" - and if they answer L then use specific Lotus Notes VBA for the e-mail process or if they answer O use Outlook-specific VBA?
I have found the code that I need to send one spreadsheet or the entire workbook via lotus notes through VBA, but I cannot seem to find the way to email 2 specific spreadsheets anywhere..
I have tried both of these, but I get an error when running:
Code: With Worksheets("Diario") And Worksheets("Periodo") .Copy stFileName = Worksheets("BD").Range("A1").Value & "- NDG " & Worksheets("Code").Range("K22").Value Debug.Print stFileName End With
Code: With Sheets(Array("Diario", "Periodo")) .Select Selection.Copy stFileName = Worksheets("BD").Range("A1").Value & " - NDG " & Worksheets("Code").Range("K19").Value Debug.Print stFileName End With
I would like the 2 sheets (Diario & Periodo) to be copied both to the same temporary workbook...
Any ways to automatically send emails using Lotus Notes.
On Lotus Notes, I automatically have my signature to append to an email, however when I am running Johns script at the bottom it is replaced with the file path of my signature (i.e. C:Program Fileslotus otesdatasig.htm), and when I comment out the part where strSignature is mentioned there is no signature.
My code is below:
Sub mySub() Dim x As Integer Dim UserName As String Dim MailDbName As String Dim Recipient As Variant Dim Maildb As Object Dim MailDoc As Object
I managed to create the attached macro with help from stuff I've found in the internet. With this code, I am able to save the file and then send an email cia Lotus Notes. I need two things:
(1) The code to attach the file saved into the email that it sends
(2) The code so that the file is protected so that changes are not made by the recipient.
HERE IS THE ActiveSheet.Copy ActiveWorkbook.SaveAs filename:="V:TFMCO8 ReportingHistory" & [c22].Value & " " & Format$([c20], "- (YYYY-MM-DD)") & ".xls" ActiveWorkbook.Close False
Dim Maildb As Object, UserName As String, MailDbName As String Dim MailDoc As Object, Session As Object
Set Session = CreateObject("Notes.NotesSession") UserName = Session.UserName MailDbName = Left$(UserName, 1) & Right$(UserName, _ (Len(UserName) - InStr(1, UserName, " "))) & ".nsf" Set Maildb = Session.GetDatabase("", MailDbName) If Maildb.IsOpen Then Else: Maildb.OpenMail...........
I have Excel 2003 and Lotus Notes Version 6 and I need to send out the active workbook from Excel. I have searched through the existing threads, but all the code that I run results in errors -
I'm trying to replicate the same functionality using Excel Analyzer that Lotus 123 had with it's function called Backsolver.
Below is a simple matirx of expenses, that shows a sum total of 3297. I want to change the number 3297 to 5000 and I want the rows and columns to add up to that total based on the same % relationship that existed when this matrix added to 3297
This technique is useful when you are trying to spread changes in a plan and you need to develop placeholders that add up and accross to the new target i.e, 5000 based on the previous targets % relationships that existed when the row and columns added up to 3297
I can use the analyzer, but when it finds its solution it returns negative values as part of the solution. I'm missing something in trying to set the parameters so that it won't return me negative #'s.
I wrote this code years ago to Auto Send Emails from Outlook...
Sub Emailfile()
Dim OutlookApp As Object Const olMailItem = 0 Set OutlookApp = CreateObject("Outlook.Application") Dim EmailContent As String With OutlookApp.CreateItem(olMailItem) EmailContent = Range("A1").Value .To = "Test" .Subject = "LSG Data File" .Body = EmailContent .Attachments.Add ("P:MI TeamICCE LEADS EXTRACTSLSGLSG Data File")
End With
End Sub
I can't figure out how to get it working for Lotus notes... So 2 things I guess really... Firstly is it possible? Second: If it is, how can I get it to send say 16 emails each with a specific attachment but the same commentary?
The code is this: Dim finder As Object Private Sub ComboBox1_Change() Set finder = Sheets("Sheet1").Columns("A:A").Find(what:=ComboBox1.Text, lookat:=xlWhole) Main_recipients.Value = finder.Offset(0, 1) End Sub Private Sub Send_email_Click() With Application .ScreenUpdating = False .DisplayAlerts = False End With
Now, everything works fine until it gets to this line:- MailDoc.SEND 0, Recipients
when I get an error message that states "Unable to send mail, no match found in Name & Address Book(s)"
In Lotus, I created a list of recipients (including any additions after the name) and copied that into the cell that feeds the text-box "Main_recipients".
The odd thing is, if I just email myself without any additions after my name it's fine. It's when I try to e-mail multiple people (with and without additions) that it falls over.
Finally I have the code all okay for Sending Reports, and other extra comments etc...
And now... Is there code available for picking up images ( Screen snaps) and inserting into an email in Notes after the Body of the Message?
Either a reference to a link or some code would be greatly appreciated, cos I cannot find any info at this point in the archives ( Presuming I have searched using proper parameters ).
