Solved

autoit excel manipulation

Posted on 2014-11-27
5
2,282 Views
Last Modified: 2014-12-02
Hello Experts,

 I've had to ask this question again as the administrators closed off the original question before I could test out the answer.

 Hello MatrixNZ,
 I'm hoping that you can repost your proposed code as that looked to be spot on.

  I'm a programming noob and I am attempting to open an existing excel file with autoit, then populate specific cells with data and then closing the file with a new name.

  My attempt is as follows:


  ;CodeStart

  #include <ExcelCOM_UDF.au3>  ; Include the function collection

  Local $sFilePath = "C:\Prod"
  Local $sFileTempPath = "C:\Temp"
  Local $File1 = "File1.xls"
  Local $File2 = "File2.xls"

  Local $oExcel = _ExcelBookOpen(1)

   _ExcelWriteCell($oExcel, "26/11/2014", "G41")                        ; Write the current date to cell G41

   ; Now we save it into the temp directory; overwrite existing file if necessary
  _ExcelBookSaveAs($oExcel, $FileTempPath & "temp.xls", "xls", 0, 1)

  ; And finally we close out
  _ExcelBookClose($oExcel)

  ;CodeFinish

  It compiles successfully but does not seem to do anything.
  I do not get a temp file in the directory.

  Thanks in advance.

  Cheers,
  Joe
0
Comment
Question by:joedelapaz
  • 2
  • 2
5 Comments
 
LVL 18

Expert Comment

by:Simon
ID: 40469755
Hi, are you determined to do this with autoit or would you be happy with an Excel template that does the same?

From what I can see you are opening a file, writing a date to a cell and then saving the file to the temp directory. Is that the process or am I missing something?
0
 

Author Comment

by:joedelapaz
ID: 40469831
Hi Simon,
Here's the scenario.
I have received an excel form from a vendor. I in order to renew access for my guys, I/we have to fill out the form and submit it each month. The vendor then renews our access. Obviously, this is a painfully repetitive and silly process. All we really need to do is to automatically change the date on the form and email it to the vendor.
This gives us another month before we have to go through the same process again.

So the idea is to automatically open this spreadsheet each month, go to the appropriate cell and set a new application date. Then go to another cell and calculate application date plus 30 days. Then save the form and another script will automatically email it to the vendor.

This vendor form is full of macros and is heavily locked down. And as you can tell from their bureaucracy if we mess around with their form, they will reject it.

I'm happy to consider any workable option.

Cheers,
Joe
0
 
LVL 18

Expert Comment

by:Simon
ID: 40470151
Hi Joe, I don't have autoit, so I can't help with that. Whether doing it in Excel VBA is the right option will depend on your environment and how automated you want this to be. I don't know if autoit can run scheduled tasks to make this process run completely unattended and whether that would be advisable or not.

Here's the basic code (that I'd put in my personal startup workbook and either link to a button or wrap with some date logic in an auto_open() macro to determine if today is a good day to send a renewal.

It opens the form, updates the two date fields, saves a copy of the workbook to the temp directory and then generates a draft email to a hard-coded recipient. To send instead of displaying draft email, just change .display to .send

Sub UpdateWorkbookAndEmail()
Dim objOutlook As outlook.Application

    Workbooks.Open Filename:="\\[Your Path Here]\EE.xlsx"
    Range("D4").Value = DateValue(Now)
    Range("D12").Value = DateAdd("m", 1, DateValue(Now()))
    Application.DisplayAlerts = False 'set this to false to suppress overwrite warning dialog
    ActiveWorkbook.SaveAs "C:\Temp\temp.xls", , , , , , , xlLocalSessionChanges
    Application.DisplayAlerts = True
    
    Set objOutlook = CreateObject("Outlook.Application")
    Set objMailItem = objOutlook.CreateItem(0)
    With objMailItem
        .To = "sbrown15@nhs.net"
        .CC = ""
        .BCC = ""
        .Subject = "Monthly access renewal"
        .Body = "Hi, please see attached form"
        .Attachments.Add ActiveWorkbook.FullName
        .Display 'or .send
    End With
    Set objMailItem = Nothing
    Set objOutlook = Nothing
    ActiveWorkbook.Close False
End Sub

Open in new window

0
 
LVL 29

Accepted Solution

by:
matrixnz earned 500 total points
ID: 40471176
Just saw this reposted, fortunately still had the code in Scite.

Hope that helps.

Cheers

#include <Excel.au3>

;~ Source Directory - Note added "\"
	Global $SRC_FILEPATH = "C:\Prod\"
;~ Check if Source Directory Exists, create the directory if false
	If FileExists($SRC_FILEPATH) = 0 Then DirCreate($SRC_FILEPATH)
;~ Temp Directory - Note added "\"
	Global $TMP_FILEPATH = "C:\Temp\"
;~ Check if Temp Directory Exists, create the directory if false
	If FileExists($TMP_FILEPATH) = 0 Then DirCreate($TMP_FILEPATH)
;~ First XLS WorkBook to Open
	Global $File1 = "File1.xls"
;~ Second XLS WorkBook to Open
	Global $File2 = "File2.xls"

;~ Attempt to open Source Directory\First XLS WorkBook
	Global $oExcel = _ExcelBookOpen($SRC_FILEPATH & $File1)
	;~ If unable to open the WorkBook or the File does not exist, open Excel with a Blank Excel WorkBook
	If @error = 1 Or @error = 2 Then
		$oExcel = _ExcelBookNew ()
	EndIf
;~ Write Cell to G41
	_ExcelWriteCell($oExcel, "26/11/2014", "G41")
;~ Save the WorkBook as Temp Directory\Second XLS WorkBook
	_ExcelBookSaveAs($oExcel, $TMP_FILEPATH & $File2, "xls", 0, 1)
;~ Close Excel WorkBook
	_ExcelBookClose($oExcel)

Open in new window

0
 

Author Closing Comment

by:joedelapaz
ID: 40477450
Thanks Matrix. That did the trick! Cheers.
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …

929 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now