MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Question about macros


Posted by David on November 18, 2001 8:44 AM

Hello:
I would like to know if the following is possible to do with an Excel macro:
1: Data will be sent to Excel from Access, macro needs to run after data has been sent.
2: Print a specific worksheet page on a network printer (I know I can do this one)
3: Open an Access database and enter a login and password.
3a: copy and paste information from worksheet to DB form.
3b: Close DB.
4: Close and Save worksheet as todays' date.

Thank in advance for any and all advise.

Dave


Posted by Gary Hewitt-Long on November 18, 2001 4:12 PM

Not so sure about the rest but this one can be done by having one cell set as e.g. A1 =TODAY() another cell referencing that as e.g. B1 =TEXT(A1,"dd-mm-yy")
Change the format of the date by modifying the "dd-mm-yy". Reference that value in the code below. If you are saving to a network location you may need to concatenate the directory in with the name i.e. =CONCATENATE("\\PC6\c\Folder\",B1,".xls")

NewFileName = Range("Sheet2!C1").Value
ActiveWorkbook.SaveAs FileName:=NewFileName, _
FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=True


HTH

Regards,

Gary Hewitt-Long

Posted by David on November 19, 2001 6:17 AM

Thanks for the idea:)
I guess an easier way of asking my question would have been: Can excel macros run other programs?

Thanks for the help.


Dave