VBA Forum RSS Feed

VBA Outlook app - keeping a sql connection open indefinetely

Fri, 03 Jul 2009 03:59:53 -0700

I'm doing a bit of work with an Outlook VBA application that a colleauge created. It's fairly basic, just connects to a sql server and runs some commands, depending on what the user is changing etc. The problem we're having is in trying to speed it up. It's not slow, but at the moment every time the user clicks to add some time (it's mostly a time-keeping thing), it opens a connection to the server, processes the stuff the user sends, and then closes the connection when it's done. When the user is simply updating a tiny bit of data, say a single field update in the database, it seems (to my boss) to be terribly wasteful to open a new connection, do some stuff, and then just close it. Therefore, I - with my very little experience of programming - have been tasked with trying to answer the question; would it be possible/quicker/safe to simply have the form open a connection when it's first opened for the day, and then keep it open until outlook is closed at the end of the day?

So, can you do this? There's only a handful of staff in the office, 50 at most, so the server won't fall over trying to keep 50 connections open (I assume!). Will it be alot quicker than opening a connection and closing it again? Is it secure to keep a connection open for such a long time? Am I asking too many questions?

Thanks for any replies,
Barns

attached

Wed, 01 Jul 2009 03:52:47 -0700

sorry it seems that my file has not been attached to my post.

Attachment: Book1.zip (27207 bytes | downloaded 3 times)

tagging or labeling variables

Wed, 01 Jul 2009 03:35:18 -0700

Hi.

I am writing a VBA (VBA in Excel) code for testing different combination of 24 variables and recording a result which corresponds to each combination of 24 variables. so actually I have an equation with 24 variables and one result.

so in excel I have one column which contains the 24 variables and the column next to it contains a figure which shows the result. what I need to do is to write loops to change the 24 variables between 1,2, and 3 and then record the result of the equation for each combination of 24 variables. I have written the loops (24 loops) and can record the result for each combination, what I have to do at this stage is to record the combinations which relates to each result. As there is only one result for each combination of 24 variables I was wondering whether I can tag each variable (in each cell) in a way that in the final sheet in the cell next to the result cell I can write for example 111123123111233311121232. This means that after each loop I am taking the values for each variable in 24 cells and put them all together in one cell in the final sheet. These variables are changing in each loop, the label should change as well after each loop so after the completion of the code I can find out which combination relates to which result.

I have tried to define an array or matrix but i couldn't fix the problem.

It would be great if you could give me a hint as I'm really short in time.

This is the input sheet (the code changes the values in the cells in A column after each operation)
A B
1 Result
2
1
1
1
3
2
...

I want the output sheet to look like this:

A B
1211132 Result

I want to put the values in the A column all together in one cell in A column in the output sheet. I should note that the values in column A input sheet are all variables. for example h1, h2, h3, h4 ... so I need a line in the code that for example: Range("A1").value=h1h2h3h4... i know that VBA does not understand it. How can I define this line so that after each operation the value in the cells in the A column (in the output sheet) changes automatically as the variables change.

I have attached an excel file with my macro in it.

How to Use API Commands in PHP?

Tue, 23 Jun 2009 21:27:48 -0700

hi,

I am creating an API using PHP and XML. I have been a API which is already in existence, but created for another client. But I want to use the same API commands in my new API that i am creating now. so, Please help me..........

find & copy value in different sheets

Tue, 23 Jun 2009 01:41:15 -0700

i have two sheets where i need to take in values in sheet2 to display in sheet1. instead of copy pasting, i want to create a macro since its easier.

Sheet1:

A B C
1 KPI Card Paper
2 Income
3 Losses

Sheet2:

A B C
1 KPI Card Paper
2 Income 12 13
3 Losses 9 5

i need to display the numbers in Sheet1 but based on the following:

if i click on the third cell in column B to get the value for income, the macro is supposed to read the value in col B row 1, find a match in sheet2's range
A1:C1, go back to sheet1 and read value in A3, find a match in sheet2's range A1:A3. if it satisfies both conditions, the value displayed in sheet1 will be retrieved from B2 in sheet2.

any idea?

Attachment: Sample.zip (1418 bytes | downloaded 5 times)

problem while input numbers to a field

Tue, 23 Jun 2009 00:03:01 -0700

Hi guys, i am new to this forum and new to Access 07 and VBA

i need to a custom msg if someone enters a dublicate number in a

field in the form but i cant make it to work with the code bellow i

wont allow you to enter any new record.

The form is Employee Table, Field N I N

thanks.

Private Sub NIN_BeforeUpdate(Cancel As Integer)

Dim SID As String
Dim stLinkCriteria As String
Dim rsc As DAO.Recordset

Set rsc = Me.RecordsetClone

SID = Me.[N I N]
stLinkCriteria = "[NIN]=" & "'" & SID & "'"

'Check Employee Table table for duplicate
If DCount("NIN", "Employee Table", stLinkCriteria) > 0 Then
'Undo duplicate entry
Me.Undo
'Message box warning of duplication
MsgBox "Warning NIN Number " _
& SID & " has already been entered." _
& vbCr & vbCr & "You will now been taken to the record.", _
vbInformation, "Duplicate Information"
'Go to record of original record
'rsc.FindFirst stLinkCriteria
'Me.Bookmark = rsc.Bookmark

End If

Set rsc = Nothing

End Sub

problem while input numbers to a field

Tue, 23 Jun 2009 00:00:30 -0700

Web query problem with java

Fri, 19 Jun 2009 04:20:52 -0700

Hi

I'm a little bit new to VBA. I've been working on it for only about 2 weeks.
This problem doesn't seem like a VBA problem.But i just think that maybe VBA could solve my problem.

The thing is, I wish to import data from this web -
http://www.tfex.co.th/tfex/dailyMarketReport.html
Your shouldn't be able to read it because it's in Thai.
But here's the deal, if you scroll to the last table of the page,
you should see something like "SET50 Index Call Options". At the
bottom of the table, there are some links which type in Thai ,but
I think you could see as something like this -- "ก.ย.52","ธ.ค.52"
,"มี.ค.53" (they're month and year in Thai). Now, this links actually
contain Java script, so if I open one of these links the table contain
data in each month and year appear leaving NOTHING CHANGE WITH THE URL
IN THE ADDRESS BAR. So, I want to get all those data (from every month
and year) but I can't get it done with "Web Query" in "Import external
data".

Do you have any suggestion on that??

Thanks in advance... ^o^

Delete Excel sheet without prompt

Thu, 18 Jun 2009 16:35:48 -0700

Hi,

I've written a procedure to delete a large number of sheets from an Excel workbook (Excel 2002) and am wondering if anyone can help me with code that will prevent the prompt comping up "Data may exist.. To permanently delete the Data, press Delete?"

I want to avoid the user having to sit there pressing Delete heaps of times.

Thanks!

Point to pixel conversion in Excel VBA

Thu, 18 Jun 2009 13:55:10 -0700

I'm using VBA 6.5 in Excel V11.8307 and am wondering if there are any functions/methods that can be invoked to do a point to pixel conversion (or vice versa) when a point is selected on a series on a sheet chart. I want to be able to draw a horizontal line through the user selected point and then after all desired lines have been added (and perhaps moved and/or deleted), retrieve the x,y coordinates of the points where the horizontal lines intersect the series (data) line, in the data coordinate system. Are these methods/functions available only in newer versions of Excel?

Thanks,
Paul Hudgens
Denver

How to use Crystal Reports 10.2 in VB 6 application

Thu, 11 Jun 2009 09:03:49 -0700

Hi I am a newbie to both VB 6 and CR 10.2
My task is to make changes in a report developed by earlier version of Crystal Reports. I edited the report using the Visual Studio 2005 IDE, but the edited report is not being recognised by the VB6 application . It throws an eror "Invalid TLV Record". Is there any way I can change my VB6 app to use CR 10.2. what dll's should I use to do this? Can any one please send me a sample code of how to use CR 10.2 report in the VB6 application?

PS: I cant upgrade the VB6 App to .net
I dont have exact version Crystal Reports Editor.

please help.

Thanks in advance,
Ramesh..

link a find button to the listbox

Fri, 05 Jun 2009 13:22:55 -0700

I am using excel 2003 vba.I want to complete a project only left link a
find button and a listbox, so when i type a name in the textbox1 the listbox
is populated with all the data to the respetive field so i can chose what
record of that person to updated.

any problem or question send me a email to josephpagan@hotmail.com or do a reply. Please help complete this project.this url is for download the project in ecxel:
http://www.4shared.com/file/110174178/b97258ed/forma_3.html

Find text then cut and paste in same worksheet

Tue, 02 Jun 2009 04:38:53 -0700

Hi, sorry but I am a newbie at this so I hope someone can help me!

I am trying to create an Excel macro that will do the following:

1. Search a column for cells that contain specific text.
2. Cut the entire row.
3. Paste the cut row into row 2 of worksheet.
4. Repeat steps 1 to 3 until all reocrds containing the specific text are at the top of my sheet.

I think I should be using a Do...Loop statement but for the life of me I can't work out exactly what to do.

Any guidance would be greatly appreciated.

Thanks.

EXCEL communicating with hyperterminal

Tue, 02 Jun 2009 01:59:48 -0700

hi,

i am trying to cause information gathered through the hyperterminal communication connection (COM1) at windows XP (office 2003), to flow into an Excel sheet.

is it possible to create using VBA code at the excel prog.?

does anyone have a similar vba code?

may the schwartz be with you,

itays

System.InvalidOperationException was unhandled

Sun, 31 May 2009 09:24:05 -0700

I'm stuck on this problem. Any assistance will be welcome. I've no problem sharing the code and/or project if needed.

System.InvalidOperationException was unhandled
Message="An error occurred creating the form. See Exception.InnerException for details. The error is: Value cannot be null.\r\nParameter name: path"
Source="Tin Can"
StackTrace:
at FreakishNoteGenerator.My.MyProject.MyForms.Create_ _Instance__[T](T Instance) in 17d14f5c-a337-4978-8281-53493378c1071.vb:line 190
at FreakishNoteGenerator.My.MyProject.MyForms.get_for m1()
at FreakishNoteGenerator.My.MyApplication.OnCreateMai nForm() in C:\Documents and Settings\403803\Desktop\Desktop Holder\Tin Can Sup 1.3 code\FreakishNoteGenerator\My Project\Application.Designer.vb:line 35
at Microsoft.VisualBasic.ApplicationServices.WindowsF ormsApplicationBase.OnRun()
at Microsoft.VisualBasic.ApplicationServices.WindowsF ormsApplicationBase.DoApplicationModel()
at Microsoft.VisualBasic.ApplicationServices.WindowsF ormsApplicationBase.Run(String[] commandLine)
at FreakishNoteGenerator.My.MyApplication.Main(String [] Args) in 17d14f5c-a337-4978-8281-53493378c1071.vb:line 81
at System.AppDomain._nExecuteAssembly(Assembly assembly, String[] args)
at System.AppDomain.nExecuteAssembly(Assembly assembly, String[] args)
at System.Runtime.Hosting.ManifestRunner.Run(Boolean checkAptModel)
at System.Runtime.Hosting.ManifestRunner.ExecuteAsAss embly()
at System.Runtime.Hosting.ApplicationActivator.Create Instance(ActivationContext activationContext, String[] activationCustomData)
at System.Runtime.Hosting.ApplicationActivator.Create Instance(ActivationContext activationContext)
at System.Activator.CreateInstance(ActivationContext activationContext)
at Microsoft.VisualStudio.HostingProcess.HostProc.Run UsersAssemblyDebugInZone()
at System.Threading.ThreadHelper.ThreadStart_Context( Object state)
at System.Threading.ExecutionContext.Run(ExecutionCon text executionContext, ContextCallback callback, Object state)
at System.Threading.ThreadHelper.ThreadStart()
InnerException: System.ArgumentNullException
Message="Value cannot be null.\r\nParameter name: path"
Source="mscorlib"
ParamName="path"
StackTrace:
at System.IO.StreamReader..ctor(String path, Encoding encoding, Boolean detectEncodingFromByteOrderMarks, Int32 bufferSize)
at System.IO.StreamReader..ctor(String path)
at FreakishNoteGenerator.Foundry.TBLoad() in C:\Documents and Settings\403803\Desktop\Desktop Holder\Tin Can Sup 1.3 code\FreakishNoteGenerator\Foundry.vb:line 143
at FreakishNoteGenerator.Foundry.MasterLoadF() in C:\Documents and Settings\403803\Desktop\Desktop Holder\Tin Can Sup 1.3 code\FreakishNoteGenerator\Foundry.vb:line 37
at FreakishNoteGenerator.Foundry.rbRep_CheckedChanged (Object sender, EventArgs e) in C:\Documents and Settings\403803\Desktop\Desktop Holder\Tin Can Sup 1.3 code\FreakishNoteGenerator\Foundry.vb:line 209
at System.Windows.Forms.RadioButton.OnCheckedChanged( EventArgs e)
at System.Windows.Forms.RadioButton.set_Checked(Boole an value)
at FreakishNoteGenerator.Foundry.rbRep_CheckedChanged (Object sender, EventArgs e) in C:\Documents and Settings\403803\Desktop\Desktop Holder\Tin Can Sup 1.3 code\FreakishNoteGenerator\Foundry.vb:line 207
at System.Windows.Forms.RadioButton.OnCheckedChanged( EventArgs e)
at System.Windows.Forms.RadioButton.set_Checked(Boole an value)
at FreakishNoteGenerator.Foundry.InitializeComponent( ) in C:\Documents and Settings\403803\Desktop\Desktop Holder\Tin Can Sup 1.3 code\FreakishNoteGenerator\Foundry.Designer.vb:lin e 113
at FreakishNoteGenerator.Foundry..ctor() in C:\Documents and Settings\403803\Desktop\Desktop Holder\Tin Can Sup 1.3 code\FreakishNoteGenerator\Foundry.vb:line 16
at FreakishNoteGenerator.LoginForm..ctor() in C:\Documents and Settings\403803\Desktop\Desktop Holder\Tin Can Sup 1.3 code\FreakishNoteGenerator\LoginForm.vb:line 5
at FreakishNoteGenerator.form1..ctor() in C:\Documents and Settings\403803\Desktop\Desktop Holder\Tin Can Sup 1.3 code\FreakishNoteGenerator\form1.vb:line 22
InnerException:

Please help me in EMU 8086 !!!!

Wed, 20 May 2009 09:23:02 -0700

Modify the program to add the following features:
A header with the name of the program and a brief instruction for the user (the person learning to type);
At the end of the sentence, after pressing “enter” (carriage return), a counter will indicate the number of errors made;
At the end of each sentence, after displaying the number of errors, the program will ask the user whether he/she would like to have more exercise. If yes, proceed to the next sentence (prepare at least 10 different sentences).

org 100h

; set video mode
mov ax, 3 ; text mode 80x25, 16 colors, 8 pages (ah=0, al=3)
int 10h

; main program body

start:
mov dx, offset msg
mov ah, 9
int 21h
mov ah, 0Eh
mov al, 0Ah
int 10h ; new line

mov bp, offset msg

next_char:
mov al, [bp]
cmp al, 24h ; '$'
jne skip
inc bp
mov al, [bp]
skip:
cmp al, 00h
je end_prog
mov bl, al
input:
mov ah, 0
int 16h
cmp bl, al
jne input
mov ah, 0Eh
int 10h
inc bp
jmp next_char

end_prog:
ret

msg db "The quick brown fox jumps right over the lazy dog.",0Dh,'$',00h

Access/Outlook automation error

Wed, 20 May 2009 07:54:17 -0700

Hi,
I have a database that downloads attachments from an outlook email folder; it's worked fine for about six months but now I get an automation error when I run it:

"Method 'Attachments' of object 'MailItem' failed -2147221233"

I have another database that does something similar and it has also started to receive the same error (code is below). I was wondering if a recent outlook update (or change to security settings?) might have caused this?

Thanks in advance for any help,
Gareth

Dim Ns As NameSpace
Dim myF As MAPIFolder
Dim miItem As Object
Dim Ol As New Outlook.Application
Dim Atmt As Attachment
Dim miProperty As Property
Dim FileName As String
Dim i As Integer
Dim x As Integer

On Error GoTo ErrDload
Set Ns = Ol.GetNamespace("MAPI")

Set myF = Ns.PickFolder
DownloadMail = False

If myF.Items.Count = 0 Then
Exit Function
End If

For Each miItem In myF.Items
If InStr(1, miItem.Subject, "Alert") Then
Debug.Print miItem.Subject
For Each Atmt In miItem.Attachments
FileName = IMPORTPATH & Atmt.FileName
Atmt.SaveAsFile FileName
i = i + 1
Next Atmt
End If
Next miItem

Excel VBA Send Email Error!

Tue, 19 May 2009 09:28:34 -0700

Hello.

I have a VBA program that is supposed to Send a Selection from an excel workbook to hard coded email addresses.It is set up to run every morning at 8am. It never errors when I just run it but once I put it into production every fourth day I recieve the following error:

'Application error:
The instruction at "0x6508cb2a referenced memory at "0x00000008." The memory could not be read.'

Below is my code:

Sub Selection()
Application.OnTime TimeValue("08:00:00"), "Selection"
Dim rng1 As Range
Dim dateToFind As Date
Dim foundDate As Range
Dim Source As Range
Dim OutApp As Object
Dim OutMail As Object
Dim StrBody As String

With Application
.EnableEvents = False
.ScreenUpdating = False
End With

dateToFind = CLng(Date)

With Sheets("Sheet1").Range("A:G")
Set foundDate = .Find(What:=dateToFind, _
After:=.Cells(.Cells.Count), _
LookIn:=xlFormulas, _
LookAt:=xlWhole, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False)
If Not foundDate Is Nothing Then
Application.Goto foundDate, True
Else
MsgBox "Nothing found"
End If
End With

ActiveCell.Select
Set TopCell = ActiveCell
Set BottomCell = Cells(ActiveCell.Row + 29, ActiveCell.Column)
Range(TopCell, BottomCell).Select

Set Source = Nothing
On Error Resume Next
Set Source = Range(TopCell, BottomCell)
On Error GoTo 0

If Source Is Nothing Then
MsgBox "The selection is not a range or the sheet is protected" & _
vbNewLine & "please correct and try again.", vbOKOnly
Exit Sub
End If

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

On Error Resume Next
StrBody = "Below is the Cross Functional Production schedule for today. Please note that if there is a problem with production, a notification will be sent." & "<br><br><br>"
With OutMail
.To = "ewheet2@uic.edu"
.CC = ""
.BCC = ""
.Subject = "Today's Production Calendar"
.HTMLBody = StrBody & RangetoHTML(Source)
.Send 'or .Display
End With
On Error GoTo 0

With Application
.EnableEvents = True
.ScreenUpdating = True
End With

Set OutMail = Nothing
Set OutApp = Nothing
End Sub

Function RangetoHTML(Source As Range)
Dim fso As Object
Dim ts As Object
Dim TempFile As String
Dim TempWB As Workbook

TempFile = Environ$("temp") & "/" & Format(Now, "dd-mm-yy;;;\d\d\-\m\m\-\y\y") & ".htm"

'Copy the range and create a new workbook to past the data in
Source.Copy
Set TempWB = Workbooks.Add(1)
With TempWB.Sheets(1)
.Cells(1).PasteSpecial Paste:=8
.Cells(1).PasteSpecial xlPasteValues, , False, False
.Cells(1).PasteSpecial xlPasteFormats, , False, False
.Cells(1).Select
Application.CutCopyMode = False
On Error Resume Next
.DrawingObjects.Visible = True
.DrawingObjects.Delete
On Error GoTo 0
End With

'Publish the sheet to a htm file
With TempWB.PublishObjects.Add( _
SourceType:=xlSourceRange, _
Filename:=TempFile, _
Sheet:=TempWB.Sheets(1).Name, _
Source:=TempWB.Sheets(1).UsedRange.Address, _
HtmlType:=xlHtmlStatic)
.Publish (True)
End With

'Read all data from the htm file into RangetoHTML
Set fso = CreateObject("Scripting.FileSystemObject")
Set ts = fso.GetFile(TempFile).OpenAsTextStream(1, -2)
RangetoHTML = ts.ReadAll
ts.Close
RangetoHTML = Replace(RangetoHTML, "align=center x:publishsource=", _
"align=left x:publishsource=")

'Close TempWB
TempWB.Close savechanges:=False

'Delete the htm file we used in this function
Kill TempFile

Set ts = Nothing
Set fso = Nothing
Set TempWB = Nothing
End Function

Please help!

Please Help Excel vba Loop question

Wed, 13 May 2009 16:00:10 -0700

I can't seem to figure out the better way to code a loop.
Please help Thank you, JB

Long n short of it, code I'm using is below:
from row 3 and below (col a is input area): b=a *(row1 coL b), c=b*(row1 coL c), d=c*(row1 coL d), e=d*(row1 coL e)

amount of Columns are fixed
amount of Rows are variable

a b c d e
1 . .03 .00 .03 .07
2 . 2008 2009 2010 2011 2012

3 . 1 1.03 1.03 1.06 1.14
4 . 3 3.09 3.09 3.18 3.41
5 . 2 2.06 2.06 2.12 2.27
6 . 5 5.15 5.15 5.30 5.68
so on....
Total 11.0 11.3 11.7 12.5

I activate the following by a 'Worksheet Change' in input column.

Sub calculationMFI()
Dim ciL As Range
Dim lr0 As Long
Dim lc1 As Long
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
On Error Resume Next
lr0 = 0
lc1 = 1
For Each ciL In Sheet1.Range("H5:AA5")
ciL.Offset(lr0, lc1).Value = ciL.Offset(lr0, 0) * ciL.Offset(-3, lc1) + ciL.Offset(lr0, 0)
ciL.Offset(lr0 + 1, lc1).Value = ciL.Offset(lr0 + 1, 0) * ciL.Offset(-3, lc1) + ciL.Offset(lr0 + 1, 0)
ciL.Offset(lr0 + 2, lc1).Value = ciL.Offset(lr0 + 2, 0) * ciL.Offset(-3, lc1) + ciL.Offset(lr0 + 2, 0)
ciL.Offset(lr0 + 3, lc1).Value = ciL.Offset(lr0 + 3, 0) * ciL.Offset(-3, lc1) + ciL.Offset(lr0 + 3, 0)
ciL.Offset(lr0 + 4, lc1).Value = ciL.Offset(lr0 + 4, 0) * ciL.Offset(-3, lc1) + ciL.Offset(lr0 + 4, 0)
ciL.Offset(lr0 + 5, lc1).Value = ciL.Offset(lr0 + 5, 0) * ciL.Offset(-3, lc1) + ciL.Offset(lr0 + 5, 0)
ciL.Offset(lr0 + 6, lc1).Value = ciL.Offset(lr0 + 6, 0) * ciL.Offset(-3, lc1) + ciL.Offset(lr0 + 6, 0)
ciL.Offset(lr0 + 7, lc1).Value = ciL.Offset(lr0 + 7, 0) * ciL.Offset(-3, lc1) + ciL.Offset(lr0 + 7, 0)
ciL.Offset(lr0 + 8, lc1).Value = ciL.Offset(lr0 + 8, 0) * ciL.Offset(-3, lc1) + ciL.Offset(lr0 + 8, 0)
ciL.Offset(lr0 + 9, lc1).Value = ciL.Offset(lr0 + 9, 0) * ciL.Offset(-3, lc1) + ciL.Offset(lr0 + 9, 0)
ciL.Offset(lr0 + 10, lc1).Value = ciL.Offset(lr0 + 10, 0) * ciL.Offset(-3, lc1) + ciL.Offset(lr0 + 10, 0)
ciL.Offset(lr0 + 11, lc1).Value = ciL.Offset(lr0 + 11, 0) * ciL.Offset(-3, lc1) + ciL.Offset(lr0 + 11, 0)
ciL.Offset(lr0 + 12, lc1).Value = ciL.Offset(lr0 + 12, 0) * ciL.Offset(-3, lc1) + ciL.Offset(lr0 + 12, 0)
ciL.Offset(lr0 + 13, lc1).Value = ciL.Offset(lr0 + 13, 0) * ciL.Offset(-3, lc1) + ciL.Offset(lr0 + 13, 0)
ciL.Offset(lr0 + 14, lc1).Value = ciL.Offset(lr0 + 14, 0) * ciL.Offset(-3, lc1) + ciL.Offset(lr0 + 14, 0)
ciL.Offset(lr0 + 15, lc1).Value = ciL.Offset(lr0 + 15, 0) * ciL.Offset(-3, lc1) + ciL.Offset(lr0 + 15, 0)
ciL.Offset(lr0 + 16, lc1).Value = ciL.Offset(lr0 + 16, 0) * ciL.Offset(-3, lc1) + ciL.Offset(lr0 + 16, 0)
ciL.Offset(lr0 + 17, lc1).Value = ciL.Offset(lr0 + 17, 0) * ciL.Offset(-3, lc1) + ciL.Offset(lr0 + 17, 0)
ciL.Offset(lr0 + 18, lc1).Value = ciL.Offset(lr0 + 18, 0) * ciL.Offset(-3, lc1) + ciL.Offset(lr0 + 18, 0)
ciL.Offset(lr0 + 19, lc1).Value = ciL.Offset(lr0 + 19, 0) * ciL.Offset(-3, lc1) + ciL.Offset(lr0 + 19, 0)
ciL.Offset(lr0 + 20, lc1).Value = ciL.Offset(lr0 + 20, 0) * ciL.Offset(-3, lc1) + ciL.Offset(lr0 + 20, 0)
ciL.Offset(lr0 + 21, lc1).Value = ciL.Offset(lr0 + 21, 0) * ciL.Offset(-3, lc1) + ciL.Offset(lr0 + 21, 0) ciL.Offset(lr0 + 22, lc1).Value = ciL.Offset(lr0 + 22, 0) * ciL.Offset(-3, lc1) + ciL.Offset(lr0 + 22, 0) ciL.Offset(lr0 + 23, lc1).Value = ciL.Offset(lr0 + 23, 0) * ciL.Offset(-3, lc1) + ciL.Offset(lr0 + 23, 0)
ciL.Offset(lr0 + 24, lc1).Value = ciL.Offset(lr0 + 24, 0) * ciL.Offset(-3, lc1) + ciL.Offset(lr0 + 24, 0)
ciL.Offset(lr0 + 25, lc1).Value = ciL.Offset(lr0 + 25, 0) * ciL.Offset(-3, lc1) + ciL.Offset(lr0 + 25, 0)
Next
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
End Sub

Tab delimited Text into Excel using VBA

Mon, 04 May 2009 17:43:19 -0700

I am building a basic program that requires excel to read and import data from a tab delimited notepad file onto a worksheet. The notepad file has 5 columns with a sample line looking like this:

YM SELL 127 12600.25 1/2/2009

My code is below. I think I am either:
1.) Assinging the data types incorrectly
2.) Starting each parse from the 1st column (column 'A') of every line of the text file instead of column A, B, C, D, or E (columns 1-5 on the text file respectively) because it gives me an "over flow error."
OR 3.) Both

No one online seems to be able to answer this question, but I feel like I am making a stupid mistake that an expert would be able to notice.

Sub ReadTextFile()
Dim A As String * 2, B As String * 4, C As Integer, D As Single, E As Date
Dim iRow As Long
Dim Fname As Variant
Fname = Application.GetOpenFilename("Text Files (*.txt),*.txt", , _
"Select Text Data File")
If Fname = False Then Exit Sub

Open Fname For Input As #1
iRow = 1
Do While Not EOF(1)
Input #1, A, B, C, D, E
Cells(iRow, 1) = A
Cells(iRow, 1) = B
Cells(iRow, 1) = C
Cells(iRow, 1) = D
Cells(iRow, 1) = E
iRow = iRow + 1
Loop
Close 1
End Sub

I would greatly appreciate any insight you might have!

Thanks,
Dave

Automation Error -2147417843 in ActiveX component

Mon, 04 May 2009 00:34:27 -0700

Hi All

I trying to write an automation script to screen scrape a 3rd Party VB6 application.

I am having difficulty trying to enter in data in an ActiveX Masked Edit Control (textbox).

When I try using
call SendMessage(myHwnd1,&HC,0,"String")

I get an Automation Error
"An outgoing call can not be made since the application is dispatching an input synchronous call"

I have looked up the MSDN forums and knowledge base and it suggests using timers but I have tried that and it didn't make any difference.

I have tried using other Win32 API such as SetWindowText but that didn't work for obvivous reasons.

Does anyone know how to enter data into an activeX component using VBA and Win32 API calls and not get the automation error?

Kind Regards
Charles

VBA auto naming when saving files

Fri, 01 May 2009 01:44:35 -0700

I could do with some help, i've created a short macro that basically rearranges and calculates a large table of data and then saves the new file. Is there anyway to code the macro so that it saves the new file as the old file name . Currently it always saves the output as book 1. Which is annoying as i have to run this file several times.

Any help/Advice would be much appreciated.

How to create and track a unique number with vba

Fri, 24 Apr 2009 15:17:22 -0700

I have a database that I use a function to create inspection report number by setting the date in a specific format and extension number on the end. Here is what I have so far:

Public Function NextAutoNum() As String
Dim I As Integer
Dim D As Date
Dim MySQL As String

D = DLookup("[LastDate]", "tblICNNumber")
I = DLookup("[LastNumber]", "tblICNNumber")
If D = Date Then
I = I + 1
Else
D = Date
I = 1
End If

MySQL = "Update tblICNNumber SET[LastDate] = Date()"
CurrentDb.Execute MySQL
MySQL = "Update tblICNNumber SET[LastNumber] = " & I & ""
CurrentDb.Execute MySQL

NextAutoNum = Format$(Year(D), "0000") & Format$(Month(D), "00") & Format$(Day(D), "00") & "-" & Format$(I, "000")
End Function

It will give me an inspection number in a format such as 20090424-001 for the first inspection conducted on the 24th of April. It works great as longer as inspections are inputted on time, but if a inspection is inputted several days later, I don't know how to draw the last extension number inputted on the previous date. I could just continue to assign the control corresponding to the date the inspection was inputted, but I would like to have the control correspond to the date the inspection was performed. Any ideas???

Emailing from excel via Outlook contact list

Thu, 23 Apr 2009 09:49:11 -0700

I need to change a macro that sends email from excel through lotus notes. Now we have switched to Outlook so it needs a little adjustment but I'm not sure how, if anyone can help me that would be fantastic! The code has something abt username and password I dont think thts necessary for Outlook is it?

Dim Maildb As Object 'The mail database
Dim UserName As String 'The current users notes name
Dim MailDbName As String 'THe current users notes mail database name
Dim MailDoc As Object 'The mail document itself
Dim AttachME As Object 'The attachment richtextfile object
Dim Session As Object 'The notes session
Dim EmbedObj As Object 'The embedded object (Attachment)
'Start a session to notes
Set Session = CreateObject("Notes.NotesSession")
'Next line only works with 5.x and above. Replace password with your password
' Session.Initialize ("password")
'Get the sessions username and then calculate the mail file name

UserName = Session.UserName
MailDbName = Left$(UserName, 1) & Right$(UserName, (Len(UserName) - InStr(1, UserName, " "))) & ".nsf"
'Open the mail database in notes
Set Maildb = Session.GetDatabase("", MailDbName)
If Maildb.IsOpen = True Then
'Already open for mail
Else
Maildb.OPENMAIL
End If
'Set up the new mail document
Set MailDoc = Maildb.CreateDocument
MailDoc.Form = "Memo"
MailDoc.sendto = mail2
MailDoc.Subject = " COM trades " & tag1 & Chr(32) & Sheet1.Range("B19")
MailDoc.body = strbody
MailDoc.SaveMessageOnSend = saveit

MailDoc.PostedDate = Now() 'Gets the mail to appear in the sent items folder
MailDoc.send 0, Recipient
'Clean Up
Set Maildb = Nothing
Set MailDoc = Nothing
Set AttachME = Nothing
Set Session = Nothing
Set EmbedObj = Nothing
Next

End Sub

Desperately Need excell cripting Help (very complicated)

Wed, 22 Apr 2009 15:57:46 -0700

I'm developing this tool for work and I am stuck on how to implement what I need. See Attachment

Somehow I need to be able to let people add and remove lines under each stage (B8:B14), esentially copy and insert a row and delete the row if needed.

So far I have managed to get two possible methods by modifying scripts I found on the web:

Button 1 (I8) will copy cells B2:B8 and insert wherever I have specified. The problem is I don't know how I can force it to insert the copied cells under the corresponding stage.

Button 2 (I9) will insert a row directly below the button without having to copy cells B2:B8, however it does not copy the formulas down and it also shifts the content in column J and K.

Another problem I need addressed is how to remove the inserted lines if the user needs to. What I wanted to do was add a checkbox in front of the newly inserted lines so that if selected the user could push the "Delete Checked" button in (I7) and it will delete the checked rows.

Attachment: ROM_SampleFinal.xls (93696 bytes | downloaded 21 times)