Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Unique filenames
Here's a tricky one (for me anyway)
I have a macro that reads a line of data from a sheet and presents it in a second sheet as a report. This report also contains some fixed text and simple graphics. The macro then prints the 'report' and goes back to the first sheet for the next line of data ....... and so on. There are hundreds of rows in the sheet and therefore hundreds of printed reports. OK so far? What I would like to do is to save each of the 'reports' using a unique reference taken from one of the cells in the row that has been copied onto the report. Any ideas? As an addition, it would be preferable to save the report in a PDF format. Thanks in anticipation. Under Pressure |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Unique filenames
Hi
See if this can help you: MyPath = "C:\Temp\" MyFileName = "Report" & Range("A1").Value & ".pdf" Sheets("Report").ExportAsFixedFormat Type:=xlTypePDF, Filename:= _ MyPath & MyFileName, Quality:=xlQualityStandard, _ IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:= _ False Regards, Per "Under Pressure" skrev i meddelelsen ... Here's a tricky one (for me anyway) I have a macro that reads a line of data from a sheet and presents it in a second sheet as a report. This report also contains some fixed text and simple graphics. The macro then prints the 'report' and goes back to the first sheet for the next line of data ....... and so on. There are hundreds of rows in the sheet and therefore hundreds of printed reports. OK so far? What I would like to do is to save each of the 'reports' using a unique reference taken from one of the cells in the row that has been copied onto the report. Any ideas? As an addition, it would be preferable to save the report in a PDF format. Thanks in anticipation. Under Pressure |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Unique filenames
Is this Excel 2007? It didn't work for me and I'm running 2003.
..ExportAsFixedFormat Type:=xlTypePDF Eric |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Unique filenames
Yes it is Excel 2007, in previous versions, you have to install a PDF
writer, and then print the sheet using the PDF writer. To save the sheet this should do it then: MyPath = "C:\temp\" MyFileName = "Report" & Range("A1").Value & ".xls" Sheets("Report").SaveAs Filename:=MyPath & MyFileName Regards, Per "EricG" skrev i meddelelsen ... Is this Excel 2007? It didn't work for me and I'm running 2003. .ExportAsFixedFormat Type:=xlTypePDF Eric |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Unique filenames
Per
Thanks for the prompt reply. I can't try your solution until I get to work tomorrow. However, I'm not sure from your lines of code where abouts I need to store my unique identifier. I'm also using Excel 2003 so presumably from the comment from Eric, I'll need to have a go with your second post. Thanks again Under Pressure "Per Jessen" wrote: Hi See if this can help you: MyPath = "C:\Temp\" MyFileName = "Report" & Range("A1").Value & ".pdf" Sheets("Report").ExportAsFixedFormat Type:=xlTypePDF, Filename:= _ MyPath & MyFileName, Quality:=xlQualityStandard, _ IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:= _ False Regards, Per "Under Pressure" skrev i meddelelsen ... Here's a tricky one (for me anyway) I have a macro that reads a line of data from a sheet and presents it in a second sheet as a report. This report also contains some fixed text and simple graphics. The macro then prints the 'report' and goes back to the first sheet for the next line of data ....... and so on. There are hundreds of rows in the sheet and therefore hundreds of printed reports. OK so far? What I would like to do is to save each of the 'reports' using a unique reference taken from one of the cells in the row that has been copied onto the report. Any ideas? As an addition, it would be preferable to save the report in a PDF format. Thanks in anticipation. Under Pressure |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Unique filenames
Hi again,
In my example, I assumed the unique identifier is in A1 of activesheet. The cell reference can be changed as desired. If the unique identifier is found on another sheet, we have to use the sheet reference too: MyFileName = "Report" & Sheets("Sheet1").Range("A1").Value & ".xls" Regards, Per "Under Pressure" skrev i meddelelsen ... Per Thanks for the prompt reply. I can't try your solution until I get to work tomorrow. However, I'm not sure from your lines of code where abouts I need to store my unique identifier. I'm also using Excel 2003 so presumably from the comment from Eric, I'll need to have a go with your second post. Thanks again Under Pressure "Per Jessen" wrote: Hi See if this can help you: MyPath = "C:\Temp\" MyFileName = "Report" & Range("A1").Value & ".pdf" Sheets("Report").ExportAsFixedFormat Type:=xlTypePDF, Filename:= _ MyPath & MyFileName, Quality:=xlQualityStandard, _ IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:= _ False Regards, Per "Under Pressure" skrev i meddelelsen ... Here's a tricky one (for me anyway) I have a macro that reads a line of data from a sheet and presents it in a second sheet as a report. This report also contains some fixed text and simple graphics. The macro then prints the 'report' and goes back to the first sheet for the next line of data ....... and so on. There are hundreds of rows in the sheet and therefore hundreds of printed reports. OK so far? What I would like to do is to save each of the 'reports' using a unique reference taken from one of the cells in the row that has been copied onto the report. Any ideas? As an addition, it would be preferable to save the report in a PDF format. Thanks in anticipation. Under Pressure |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Unique filenames
Thanks once again, Per This appears to be working OK at present, I won't get chance to do the full blown test until next monh. You've saved me a great deal of time - again thanks!!!!! Under Pressure "Per Jessen" wrote: Hi again, In my example, I assumed the unique identifier is in A1 of activesheet. The cell reference can be changed as desired. If the unique identifier is found on another sheet, we have to use the sheet reference too: MyFileName = "Report" & Sheets("Sheet1").Range("A1").Value & ".xls" Regards, Per "Under Pressure" skrev i meddelelsen ... Per Thanks for the prompt reply. I can't try your solution until I get to work tomorrow. However, I'm not sure from your lines of code where abouts I need to store my unique identifier. I'm also using Excel 2003 so presumably from the comment from Eric, I'll need to have a go with your second post. Thanks again Under Pressure "Per Jessen" wrote: Hi See if this can help you: MyPath = "C:\Temp\" MyFileName = "Report" & Range("A1").Value & ".pdf" Sheets("Report").ExportAsFixedFormat Type:=xlTypePDF, Filename:= _ MyPath & MyFileName, Quality:=xlQualityStandard, _ IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:= _ False Regards, Per "Under Pressure" skrev i meddelelsen ... Here's a tricky one (for me anyway) I have a macro that reads a line of data from a sheet and presents it in a second sheet as a report. This report also contains some fixed text and simple graphics. The macro then prints the 'report' and goes back to the first sheet for the next line of data ....... and so on. There are hundreds of rows in the sheet and therefore hundreds of printed reports. OK so far? What I would like to do is to save each of the 'reports' using a unique reference taken from one of the cells in the row that has been copied onto the report. Any ideas? As an addition, it would be preferable to save the report in a PDF format. Thanks in anticipation. Under Pressure |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Unique filenames
Per
Just to let you know that I have now completed this project at work and it has worked a treat. Thanks again - you saved me a great deal of time. I posted another query yesterday but it doesn't appear to have shown up in the list of posts. I tried it again this morning and I still can't see it. Have I done something wrong? I hope not as posting a new thread looks simple enough. Cheers Under Pressure "Per Jessen" wrote: Hi again, In my example, I assumed the unique identifier is in A1 of activesheet. The cell reference can be changed as desired. If the unique identifier is found on another sheet, we have to use the sheet reference too: MyFileName = "Report" & Sheets("Sheet1").Range("A1").Value & ".xls" Regards, Per "Under Pressure" skrev i meddelelsen ... Per Thanks for the prompt reply. I can't try your solution until I get to work tomorrow. However, I'm not sure from your lines of code where abouts I need to store my unique identifier. I'm also using Excel 2003 so presumably from the comment from Eric, I'll need to have a go with your second post. Thanks again Under Pressure "Per Jessen" wrote: Hi See if this can help you: MyPath = "C:\Temp\" MyFileName = "Report" & Range("A1").Value & ".pdf" Sheets("Report").ExportAsFixedFormat Type:=xlTypePDF, Filename:= _ MyPath & MyFileName, Quality:=xlQualityStandard, _ IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:= _ False Regards, Per "Under Pressure" skrev i meddelelsen ... Here's a tricky one (for me anyway) I have a macro that reads a line of data from a sheet and presents it in a second sheet as a report. This report also contains some fixed text and simple graphics. The macro then prints the 'report' and goes back to the first sheet for the next line of data ....... and so on. There are hundreds of rows in the sheet and therefore hundreds of printed reports. OK so far? What I would like to do is to save each of the 'reports' using a unique reference taken from one of the cells in the row that has been copied onto the report. Any ideas? As an addition, it would be preferable to save the report in a PDF format. Thanks in anticipation. Under Pressure |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Generation of filenames | Excel Programming | |||
what do 'blue filenames' mean? | Excel Discussion (Misc queries) | |||
getting filenames | Excel Programming | |||
unique filenames in macros | Excel Programming | |||
Getting filenames via VBA | Excel Programming |