Macro to save a file in excel and pdf

Microsoft Microsoft office excel 2007 ac...
May 23, 2010 at 07:14:26
Specs: Windows XP

I have 2 excel files:
1.excel statement that has to be saved.
2.excel database abc.xls which contains the client name, saving drive path and the name the file has to be saved.(this file contains column a: client name, b:file path c:file name to be saved)

I want the macro to capture the active cell data from the statement and find the data in the database file, and if found save the file in the file name defined in column C and the given path specified in Column B. If the name not found then pop-up msg "name not found". The file should save in xls and pdf format.

Please note I have 80-100 statements for diff clients and the same details are updated in the database file.

Let me know if u any further information.

Thanks in advance for your help.


See More: Macro to save a file in excel and pdf

Report •

May 23, 2010 at 12:07:39
re: The file should save in xls and pdf format.

How are you saving files in PDF format?

.pdf is not one of the default filetypes available in the "Save as type" list.

pdf's can be created with a number of different pdf creation utilities so knowing how you are doing it would be helpful when writing code.

Report •

May 23, 2010 at 14:48:49
hi there, thanks for ur reply.

currently after all the formating the file is saved directly as below:

'save as a pdf version
fileName = "S:\Ged\Z_Value\Val_xls\New_Statements\" & "_" & ValuationName & "_" & ValuationDate

' Create the postscript file and use Distiller to convert to PDF
ActiveWorkbook.PrintOut , , , , "Acrobat Distiller", True, , fileName & ".ps"

' MM Oct09
' unhide me if the below is broken
' Shell "C:\program files\Adobe\Acrobat 7.0\Distillr\acrodist.exe " & Chr$(34) & fileName & ".ps" & Chr$(34)

' MM Oct09
' this is supposed to allow different versions of adobe acrobat to work
' ******************
' hide everything between the asterisks if this breaks
' and unhide the line above beginning with "shell"
If RegKeyExists("HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows\CurrentVersion\App Paths\acrodist.exe\") Then
DistillerPath = RegKeyRead("HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows\CurrentVersion\App Paths\acrodist.exe\")
Shell DistillerPath & " " & Chr$(34) & fileName & ".ps" & Chr$(34)
Shell "C:\program files\Adobe\Acrobat 7.0\Distillr\acrodist.exe " & Chr$(34) & fileName & ".ps" & Chr$(34)
End If
' ******************

ActiveWorkbook.SaveAs fileName:="S:\Ged\Z_Value\Val_xls\New_Statements\" & "_" & ValuationName & "_" _
& ValuationDate & ".xls", FileFormat:=xlExcel8 _
, password:="", WriteResPassword:="", ReadOnlyRecommended:=False, _



but here the date is specified in the statement and saves accordingly, but the new way i want the macro look into the excel database file to look for the file name and path to save it in pdf and excel version.

Report •

Related Solutions

Ask Question