joedelapaz
asked on
autoit excel manipulation
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
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
ASKER
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
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
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
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks Matrix. That did the trick! Cheers.
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?