Automate a simple task - Help Please

[maca9]

Hi

My task is quite simple.

I have an excel sheet I need to send via email.

I have an access database which produces the excel report via a button. This works well except that once the query is dumped to excel I need to run a simple excel macro over it to format it correctly. That is expand the columns and rows to show the contents.

Is there anyway I can have the button automatically run that excel macro to reformat it so i don't need to open excel and do it manually after I export it from access?


Thanks in advance for any help.

Cheers

Scott

http://www.vegajewellery.co.nz

Diamond Specialists

 

[PHV]

You may consider OLE Automation.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[longhair]

maca9,

to do this automatically i usually import from excel. to div you an idea...
Sub macro1()
Dim newHour As Variant
Dim newMinute As Variant
Dim newSecond As Variant
Dim waitTime As Variant

newHour = Hour(Now())
newMinute = Minute(Now())
newSecond = Second(Now()) + 3
waitTime = TimeSerial(newHour, newMinute, newSecond)
Application.Wait waitTime
If IsNull(Application.MailSession) Then
Application.MailLogon "MS Exchange Settings", , False
End If
ChDir "C:\aafiles"
Workbooks.OpenText Filename:="C:\aafiles\dsnocoitm.txt", Origin:=xlWindows, _
StartRow:=1, DataType:=xlFixedWidth, FieldInfo:=Array(Array(0, 2), Array(7, _
2), Array(11, 2), Array(21, 2), Array(44, 2), Array(70, 2), Array(96, 2), Array(112, 2))
Columns("A:A").EntireColumn.AutoFit
Columns("B:B").EntireColumn.AutoFit
Columns("C:C").EntireColumn.AutoFit
Columns("D").EntireColumn.AutoFit
Columns("E:E").EntireColumn.AutoFit
Columns("F:F").EntireColumn.AutoFit
Columns("G:G").EntireColumn.AutoFit
Columns("H:H").EntireColumn.AutoFit
Application.DisplayAlerts = False
ActiveWorkbook.SaveAs Filename:="C:\aafiles\dsnocoitm_" & Format(Now(), "yyyymmdd") & ".xls" _
, FileFormat:=xlNormal, Password:="", WriteResPassword:="", ReadOnlyRecommended:=False _
, CreateBackup:=False
ActiveWorkbook.SendMail Recipients:=Array("user1@company.com", "user2@company.com")
Workbooks("dsnocoitm_" & Format(Now(), "yyyymmdd") & ".xls").Close SaveChanges:=False
Application.DisplayAlerts = True


Application.Quit
End Sub

i also use the following in the workbook:
Sub Autpen()
Application.Run "dsnocoitm.xls!Menus"
End Sub
then to automate i just call the workbook with a scheduled task.

hth
regards,
longhair

 

[maca9]

Thanks for your replies.

How would I use OLE automation to achieve this?

http://www.vegajewellery.co.nz

Diamond Specialists