PDA

View Full Version : Solved: VBA insanity



jamward
02-26-2008, 06:46 AM
I have a folder on LAN server with proof photos. X:\2008\Ready\
All photos are in this format: AdvertiserName_5555_Feb.jpg
My WorkBook has an array table with all info for locations (i.e. 5555)
Would like Macro to go to this folder, Insert image at Q10 of my Workbook, resize photo to 6" wide, extract "5555" from inserted file name and put it into CELL S31. Then save file as a Word Document named appropriately AdvertiserName_5555_Feb.doc back into the X:\2008\Ready\ folder
I have working VLOOKUP formula in CELL S31 now.

Is any of this even possible??
Many Thanks,

RonMcK
02-26-2008, 10:36 AM
jamward,

I think we need some more information. How many photos in folder? You description hints at one photo per worksheet. Is that right? Can you post a sample of what you want to get? Parsing the location id out of the photo's jpg filename is doable.

Thanks,

Ron
Orlando, FL

jamward
02-26-2008, 02:14 PM
Folder contents change daily, but usually no more than 50 images. Attached is unprotected spreadsheet. 80% of table data removed to decrease file size.

Uploaded file name: <PhotoMaker_Progress.zip>

Thanks fo your quick reply.

RonMcK
02-26-2008, 02:18 PM
And, thank you for your prompt response, as well. :thumb

I'll look at this when I get home this evening.


Thanks,

Ron
Orlando, FL

jamward
02-26-2008, 02:24 PM
Thanks so much for the help. This forum is incredible.

Jim
Grand Rapids, MI

RonMcK
02-26-2008, 08:27 PM
All you lurkers,

I think I'm getting in over my head. Anyone with ideas, please jump in.

One challenge here is to read the file names and parse out the location code.

The other is sizing the imported photo.

When I ran one of my photos through Jim's code, I got some resizing (an a messed-up aspect ratio) but the photo is far larger than the intended space.

I recorded a macro to insert the photo and resize it to 6" (Jim's desired photo width). The resized photo is still too large, although not as large as the first effort but still exceeding the desired range ("J11:V30").

I'm going to sit back and learn from the wizards.

P.S. Query: Is there a msoFileType code for JPGs? Or do the msoFileType codes only exist for MS Office application files?

-------------------------------------


Jim,

Your answers to these questions may help the others who join to help.

Are you saving the document as a Word doc so you strip the logic and formulae from the file? Have you considered printing using a 'pdfmaker' (Adobe or another mfgr) to generate a PDF of the document, instead? This would sanitize the doc, make it tamper-proof, and give you a format anyone can print. Adobe Reader is a free download if the client doesn't already have it.

Since you have a Get New Picture button, can we assume you want to 'manually' (you click the button) process the pictures? The alternative would be an automatic process that does them one after the other.

Would you consider using some subfolders under you \Ready? It will be easier if we take the pictures one at a time from that folder, process, print, and move each to another folder, adding the new .doc (or pdf) file to that 2nd folder, as well.

If the process gets interrupted ('puter problems, etc) you know what remains to be processed. Similarly, when the 1st folder is empty, we know that we're done. So, the folder substructure might be:

\Ready\photos-in\
\Ready\processed\


Thanks,

Ron
Orlando, FL

jamward
02-27-2008, 08:57 AM
Incredible. I'm in awe of your talent. Sub-folders are always good if the process is cleaner. Better for organizationa as well. The macro can be totally automated for the button. One click can do the entire batch. You can rename it if you want, or I can later. I'm thinkin' "Process Images" or something. I love learning how vb works and contantly amazed at the knowledge of people on this and other forums. Hopefully, I will be able to help people in the future when I actually know a little about it. Life is good...

ps forgot about the pdf option. I have tried using pdf documents (which I prefer for the reasons you gave), but because of occasional editing requirements and the lack of Acrobat editing software in our office, they requested Word documents.
Thanks to All,
-Jim

coliervile
02-28-2008, 03:48 PM
jamward could I see what the whole workbook looks like. I'm in Kalamzoo,MI and in the aviation industry and this is something for mr that's been on the back burner and could be useful.

Best regards,

Charlie

Charlize
02-28-2008, 04:05 PM
If the format of the filename is always like this format :
name_123_monthyou could use the split function. Based on that function, split(1) would hold the location. But I believe the split function always uses a string (I could be wrong) as result.

Charlize

Charlize
02-29-2008, 04:24 AM
Variation by using the split function. I guess you manually fill in the number in S31
Sub Place_Picture()
'worksheet
Dim ws As Worksheet
'name of picture
Dim v_filename As String
'path of pictures
Dim v_path As String
'define ws
Set ws = ActiveWorkbook.Worksheets("MAIN")
'directory of your pictures
v_path = "C:\Pictures\*.jpg"
v_filename = Dir(v_path)
'When incorrect nameformat an error will occur
'so we continue with the next picture because
'we continue the program
On Error Resume Next
'loop through your pictures
Do While v_filename <> ""
If Split(v_filename, "_")(1) = vbNullString Then
v_filename = Dir
ElseIf Split(v_filename, "_")(1) = ws.Range("S31").Text Then
ws.Range("Q10:V30").Select
ActiveSheet.Pictures.Insert(Left(v_path, Len(v_path) - 5) & v_filename).Select
Selection.ShapeRange.ScaleWidth 0.81, msoFalse, msoScaleFromTopLeft
Selection.ShapeRange.ScaleHeight 1.4, msoFalse, msoScaleFromTopLeft
Exit Do
Else
v_filename = Dir
End If
Loop
'set errorchecking back to normal
On Error GoTo 0
End Sub
Charlize

RonMcK3
02-29-2008, 10:08 PM
jamward could I see what the whole workbook looks like. I'm in Kalamzoo,MI and in the aviation industry and this is something for mr that's been on the back burner and could be useful.

Have you unhidden col A:0 (or thereabouts)? They give one a good idea of what he is doing. The code he stripped out so the file would meet the upload max includes the formulas (proprietary business information) for applying this information. I believe that you can see from that how to adapt this technique for your own purposes.

Ron
Orlando, FL

coliervile
03-01-2008, 06:01 AM
Thanks I took a look at this and it something that similar to what I would need.

Would Charlize coding in this macro Sub Place_Picture() replace this macro in the original worksheet Sub InsertPictureInRange. Laso could you explain what the coding in module 5, Function Get_Word does?

Best regards,

Charlie

jamward
03-01-2008, 06:53 AM
Hi Charlie,
The original attachment is the entire workbook. In the hidden columns is data array. I removed about 80% of data because of file size constraints. I have attached zipped spreadsheet file document to this reply. There's not much to it. Just trying to build a little time-saver.

Thanks again to everyone. I'm feeling quite overwhelmed and humbled by the response.

-Jim

coliervile
03-01-2008, 07:51 AM
Thanks Jim for responding. I didn't see the zip file attached to your last thread #13. I did open up the other columns that had various data that's specific to the GRR area. I'm wanting to use something similar with aircraft accidents and insert the picture of the accident/aircraft site/wreckage. Are you using the number in S31 from column "A" to insert your photo? What starts the macro to insert your photo?

Best regards,

Charlie

Charlize
03-02-2008, 02:06 PM
Another variation on this theme. Process all the pictures in a directory and move them to another directory.
Sub Process_Directory_with_Pictures()
'worksheet
Dim ws As Worksheet
'name of picture
Dim v_filename As String
'path of pictures
Dim v_path As String
'path of processed pictures
Dim v_processed As String
'define ws
Set ws = ActiveWorkbook.Worksheets("MAIN")
'directory of your pictures
v_path = "C:\Pictures\*.jpg"
'directory of processed pictures
v_processed = "Printed"
v_filename = Dir(v_path)
'When incorrect nameformat an error will occur
'so we continue with the next picture because
'we continue the program
On Error Resume Next
'loop through your pictures
Do While v_filename <> ""
If Split(v_filename, "_")(1) = vbNullString Then
v_filename = Dir
Else
'get number from the file
ws.Range("S31").Value = Split(v_filename, "_")(1)
'get name from the file
ws.Range("Q33").Value = Split(v_filename, "_")(0)
ws.Range("Q10:V30").Select
'insert picture and give the desired format
ActiveSheet.Pictures.Insert(Left(v_path, Len(v_path) - 5) & v_filename).Select
With Selection.ShapeRange
.LockAspectRatio = msoFalse
.Height = 240.75
.Width = 354#
.Rotation = 0#
End With
Application.Wait Now + TimeSerial(0, 0, 1)
MsgBox "Here you call a printroutine", vbInformation
'Remove picture from excelfile
Selection.Delete
'Move picture to directory of printed pictures.
'Directory of printed images is a sub directory
'of images to be processed and is named Printed
'in this routine. Directory must be present.
'if you want you can also add the date of processing to the file
Name Left(v_path, Len(v_path) - 5) & v_filename As _
Left(v_path, Len(v_path) - 5) & v_processed & "\" & v_filename
v_filename = Dir
End If
Loop
'set errorchecking back to normal
On Error GoTo 0
End Sub
Charlize

jamward
03-02-2008, 08:11 PM
Thank you very much. This looks great. I will try it out tomorrow morning when i get to work.

RonMcK
03-02-2008, 09:29 PM
Charlize,

Does your technique only work for opening and walking the files in folders on the c:\ drive? It's not retrieving filenames from my folder is on my g: drive (part of my 2nd HD). No error is returned, just no non-null v_filename is returned.

Thanks,

Ron
Orlando, FL

RonMcK
03-02-2008, 09:51 PM
Thanks Jim for responding. I didn't see the zip file attached to your last thread #13. I did open up the other columns that had various data that's specific to the GRR area. I'm wanting to use something similar with aircraft accidents and insert the picture of the accident/aircraft site/wreckage. Are you using the number in S31 from column "A" to insert your photo? What starts the macro to insert your photo?
coliervile (Charlie),

I think Jim might say he's doing it the other way around. He reads a filename from the folder, parses the location ID code from the filename (Charlize does this using split()). Jim has the photo file and the location ID code, so he inserts that code into the worksheet (S31) and inserts the photo; Jim's vlookup takes the location ID code and finds the related details.

Starting the macro: Jim wants to start the process by clicking the button on the template document. Charlize's most recent macro processes all the photos in a folder, so Jim can attach it (the macro) to his button. Click it once and all of today's photos are processed and the documents printed.

You can create a different process for your needs: click the button to start, have code open a userform where you ask the user to give you a folder and photo to find and process, your code can then scan folder and process the photo, if it is found. Or you could invoke the file open dialog with its explorer window so your user can navigate thru folders and select the specific photo to insert. Using the photo name (or a code parsed from it) you can use the vlookup table to retrieve accident information related to that specific photo and add it to your document.

HTH,

Thanks,

Ron
Orlando, FL

Charlize
03-03-2008, 03:07 AM
Charlize,

Does your technique only work for opening and walking the files in folders on the c:\ drive? It's not retrieving filenames from my folder is on my g: drive (part of my 2nd HD). No error is returned, just no non-null v_filename is returned.

Thanks,

Ron
Orlando, FLMaybe it's because the name of the file must be in a specific format ie. nameofsomebody_number_month . Because in this post the poster used _ in the name of his files. Based on that underscore I seperate the name and the number and use them in the coding instead of a vlookup formula. When split(...)(1) generates an error, the coding proceeds to the next file and so on ... until all files are processed.

Charlize

RonMcK
03-03-2008, 07:38 AM
Charlize,

Thanks but no, my problem isn't (at least not yet) with the filenames and the split logic.

Sub another()
<snipped out section of code>
v_path = "G:\my G documents\vba express\jamwards\ready\*.jpg"
v_filename = Dir(v_path)
On Error Resume Next
'loop through your pictures
Do While v_filename <> ""
<omitted code bcs it was not executed>
Loop
On Error GoTo 0
End Sub


After I change v_path to the path noted above, when I single-step the code, I find that only these lines of code are executed: v_filename returns "" so the Do While fails immediately, dropping me to the 'On Error GoTo 0' and then 'End Sub'.

When I look in the specified folder with file explorer, I find the 5 JPGs that I placed there for testing; each JPG has a '_####_' in the filename and each file name ends with a 3-char month name. So your split logic should work if I ever get VBA/Excel to read the filenames in that folder.

Note if I add a v_filename = DIR("") in my code, vba finds files in my c:\my Documents folder.

So, what might be preventing it from finding files on my G: drive?

Thanks,

Ron
Orlando, FL

Charlize
03-03-2008, 08:01 AM
Maybe change the active drive with ChDrive "G" before the line of v_path = ... . Just a shot in the dark. Will try something later this day ... I'll keep you informed if I find something.

Charlize

RonMcK
03-03-2008, 09:01 AM
Charlize,

Thanks for the point-out. I'll try that, myself, this evening when I get home from my after dinner meeting. I'll also try it, later today, on my Mac here at work since I have some network drives that can emulate my g: drive at home.

Thanks,

Ron
Orlando, FL

jamward
03-03-2008, 09:38 AM
Charlize code works great. I edited it slightly to place the parsed location number into S31 and Adv. Name into R32 thereby allowing VLOOKUP formula in Q33 to execute.

'get number from the file
ws.Range("S31").Value = Split(v_filename, "_")(1)
'get name from the file
ws.Range("R32").Value = Split(v_filename, "_")(0)

this routine saves the oringinal image in the "Processed" folder after running print routine. Can I specify the file name when printing instead of manually entering it? I use PDF Creator print driver. Ultimate coolness would be to save the selected print range as a Word document, but not even sure if that's possible??


Range("P1:W39").Select
Selection.PrintOut Copies:=1, PrintToFile:=True

Thanks again to everyone,

clang
03-03-2008, 10:27 AM
What is wrong with my code below? I cannot get the second button to work properly. The first button works.

Private Sub cmdAdd_Click()
Dim iRow As Long
Dim ws As Worksheet
Set ws = Worksheets("Estimationdata")
'find first empty row in database
iRow = ws.Cells(Rows.Count, 1) _
.End(x1Up).Offset(1, 0).Row

'check for a code number
If Trim(Me.txtCostcode.Value) = "" Then
Me.txtCostcode.SetFocus
MsgBox "Please enter a part number"
Exit Sub
End If
'copy the data to the database
ws.Cells(iRow, 1).Value = Me.txtCostcode.Value
ws.Cells(iRow, 2).Value = Me.txtDescription.Value
ws.Cells(iRow, 3).Value = Me.txtUnit.Value
ws.Cells(iRow, 4).Value = Me.txtMaterial.Value
ws.Cells(iRow, 5).Value = Me.txtLaborHours.Value
ws.Cells(iRow, 6).Value = Me.txtLaborDollars.Value
ws.Cells(iRow, 7).Value = Me.txtOther.Value
ws.Cells(iRow, 8).Value = Me.txtTotal.Value
'clear the data
Me.txtCostcode.Value = ""
Me.txtDescription.Value = ""
Me.txtUnit.Value = ""
Me.txtMaterial.Value = ""
Me.txtLaborHours.Value = ""
Me.txtLaborDollars.Value = ""
Me.txtOther.Value = ""
Me.txtTotal.Value = ""
Me.txtCostcode.SetFocus
End Sub

Private Sub cmdClose_Click()
Unload Me
End Sub

Charlize
03-03-2008, 12:48 PM
Charlize code works great. I edited it slightly to place the parsed location number into S31 and Adv. Name into R32 thereby allowing VLOOKUP formula in Q33 to execute.

'get number from the file
ws.Range("S31").Value = Split(v_filename, "_")(1)
'get name from the file
ws.Range("R32").Value = Split(v_filename, "_")(0)

this routine saves the oringinal image in the "Processed" folder after running print routine. Can I specify the file name when printing instead of manually entering it? I use PDF Creator print driver. Ultimate coolness would be to save the selected print range as a Word document, but not even sure if that's possible??


Range("P1:W39").Select
Selection.PrintOut Copies:=1, PrintToFile:=True

Thanks again to everyone,I did it once so I think I should be able to automatically create pdf's instead of printing them out. Or I could create a new Word Document based on some template. The word document has some placeholders for the info that you want. Last step is to save your word document in the processed folder.

It depends on what you want to have. But it's doable.

Charlize

Charlize
03-03-2008, 01:14 PM
Charlize,

Thanks for the point-out. I'll try that, myself, this evening when I get home from my after dinner meeting. I'll also try it, later today, on my Mac here at work since I have some network drives that can emulate my g: drive at home.

Thanks,

Ron
Orlando, FLI've tried it with an H: drive and everything works well with all the pictures (I even used your directory structure.). I'm sorry but I've got no idea what the problem could be. You are sure that you gave the exact path to the pictures ?

Charlize

RonMcK
03-03-2008, 01:37 PM
I've tried it with an H: drive and everything works well with all the pictures (I even used your directory structure.). I'm sorry but I've got no idea what the problem could be. You are sure that you gave the exact path to the pictures ?

Charlize,

Thanks. I believe that it's correct, hoever, I'll double-check the path when I get home around 9:00 pm EST.

Thanks,

Ron
Orlando, FL

Charlize
03-03-2008, 02:47 PM
Charlize code works great. I edited it slightly to place the parsed location number into S31 and Adv. Name into R32 thereby allowing VLOOKUP formula in Q33 to execute.

'get number from the file
ws.Range("S31").Value = Split(v_filename, "_")(1)
'get name from the file
ws.Range("R32").Value = Split(v_filename, "_")(0)

this routine saves the oringinal image in the "Processed" folder after running print routine. Can I specify the file name when printing instead of manually entering it? I use PDF Creator print driver. Ultimate coolness would be to save the selected print range as a Word document, but not even sure if that's possible??


Range("P1:W39").Select
Selection.PrintOut Copies:=1, PrintToFile:=True

Thanks again to everyone,Example to print to pdf using PDFCreator. Pictures is a subdirectory of the directory where your activeworkbook is saved. Printed is the subdirectory of pictures.Option Explicit
Public v_path As String
'directory of your pictures
Public v_processed As String
'directory of processed pictures
Public v_filename As String
'name of picture
Sub Process_Directory_with_Pictures()
'worksheet
Dim ws As Worksheet
'define ws
Set ws = ActiveWorkbook.Worksheets("MAIN")
'directory of your pictures
v_path = ActiveWorkbook.Path & "\Pictures\*.gif"
'directory of processed pictures
v_processed = "Printed"
v_filename = Dir(v_path)
'When incorrect nameformat an error will occur
'so we continue with the next picture because
'we continue the program
On Error Resume Next
'loop through your pictures
Do While v_filename <> ""
If Split(v_filename, "_")(1) = vbNullString Then
v_filename = Dir
Else
'get number from the file
ws.Range("S31").Value = Split(v_filename, "_")(1)
'get name from the file
ws.Range("Q33").Value = Split(v_filename, "_")(0)
ws.Range("Q10:V30").Select
'insert picture and give the desired format
ActiveSheet.Pictures.Insert(Left(v_path, Len(v_path) - 5) & v_filename).Select
With Selection.ShapeRange
.LockAspectRatio = msoFalse
.Height = 240.75
.Width = 354#
.Rotation = 0#
End With
Application.Wait Now + TimeSerial(0, 0, 1)
Call PrintToPDF
Application.Wait Now + TimeSerial(0, 0, 1)
'Remove picture from excelfile
Selection.Delete
'Move picture to directory of printed pictures.
'Directory of printed images is a sub directory
'of images to be processed and is named Printed
'in this routine. Directory must be present.
'if you want you can also add the date of processing to the file
v_filename = Dir
End If
Loop
'set errorchecking back to normal
On Error GoTo 0
MsgBox "Pictures have been processed.", vbInformation, "Pdf info ..."
End Sub
Sub PrintToPDF()
'Author : Ken Puls (www.excelguru.ca (http://www.excelguru.ca))
'Macro Purpose: Print to PDF file using PDFCreator
'(Download from http://sourceforge.net/projects/pdfcreator/)
'Designed for early bind, set reference to PDFCreator
'Adapted by Charlize for this particular need
Dim pdfjob As PDFCreator.clsPDFCreator
Dim sPDFName As String
Dim sPDFPath As String
Set pdfjob = New PDFCreator.clsPDFCreator
sPDFPath = Left(v_path, Len(v_path) - 5) & v_processed
If pdfjob.cStart("/NoProcessingAtStartup") = False Then
MsgBox "Can't initialize PDFCreator.", vbCritical + _
vbOKOnly, "PrtPDFCreator"
Exit Sub
End If
With pdfjob
'/// Change the output file name here! ///
sPDFName = Left(v_filename, Len(v_filename) - 4) & ".pdf"
.cOption("UseAutosave") = 1
.cOption("UseAutosaveDirectory") = 1
.cOption("AutosaveDirectory") = sPDFPath
.cOption("AutosaveFilename") = sPDFName
.cOption("AutosaveFormat") = 0 ' 0 = PDF
.cClearCache
End With
'Print the document to PDF
ActiveSheet.PrintOut copies:=1, ActivePrinter:="PDFCreator"
Application.Wait Now + TimeSerial(0, 0, 5)
'Wait until the print job has entered the print queue
Do Until pdfjob.cCountOfPrintjobs = 1
DoEvents
Loop
pdfjob.cPrinterStop = False
Application.Wait Now + TimeSerial(0, 0, 5)
'Wait until PDF creator is finished then release the objects
Do Until pdfjob.cCountOfPrintjobs = 0
DoEvents
Loop
pdfjob.cClose
Set pdfjob = Nothing
Name Left(v_path, Len(v_path) - 5) & v_filename As _
Left(v_path, Len(v_path) - 5) & v_processed & "\" & v_filename
Application.Wait Now + TimeSerial(0, 0, 1)
End Sub
Charlize

jamward
03-04-2008, 07:20 AM
Getting Complie Error: User-defined type not defined at:

Dim pdfjob As PDFCreator.clsPDFCreator

can this be as a result of the spreadsheet being on network and PDF Creator print drivers on local machine? I have PDF Creator shared on the network. When I actually run the macro no images are inserted and I get a "Pdf Info... Pictures have been processed" message, but no processed pictures.

RonMcK
03-04-2008, 10:15 AM
Charlize,

You nailed it. I had a wee typo in the path. Once corrected all worked as expected.

Thanks!

Ron
Orlando, FL

Charlize
03-04-2008, 04:19 PM
Getting Complie Error: User-defined type not defined at:

Dim pdfjob As PDFCreator.clsPDFCreator

can this be as a result of the spreadsheet being on network and PDF Creator print drivers on local machine? I have PDF Creator shared on the network. When I actually run the macro no images are inserted and I get a "Pdf Info... Pictures have been processed" message, but no processed pictures.You've got to set a reference to this printer.
1. alt + f11 (programming interface)
2. in menu Tools - references
3. put a checkmark at PDFCreator

Charlize

Charlize
03-04-2008, 04:21 PM
Charlize,

You nailed it. I had a wee typo in the path. Once corrected all worked as expected.

Thanks!

Ron
Orlando, FLNo problem, glad it works well for you.

jamward
03-05-2008, 06:30 AM
If PDFCreator is not in Reference index what does this mean? I check marked all references to PDF, but still get compile error. I have asked my network admin to install latest version of PDFCreator including Ghostscript. Hopefully, this will solve the problem. I will reply back with progress. Thinking I might have to use Microsoft Document Imaging and save output as a jpg. Does enabling other reference library files affect anything else in operation of macros?
http://wardesign.com/public/screencap.jpg

Checked out excelguru's site. there sure is a lot of great stuff on there. can't wait to try some of it.

Charlize
03-05-2008, 07:08 AM
When you go to <Start> (green left button - menu) - Printers, there comes a list with printers. Maybe you have to install the network printer to be used with your computer (at least that's what we have to do to use a printer that's connected to the network). In other words : Some printers have a connection to the servers and when I want to use that printer I'll have to install it (the drivers) to be used with my cli?ntcomputer. So when networkserver is NWno1 and you named the printer PDFCreator, the name will be PDFCreator on NWno1 (on your computer list of printers). When you install that printer you give it a name. That name will be the same in the references box (that's what I think).

Charlize

jamward
03-07-2008, 08:33 AM
Back with you. it's the weirdest thing...I have PDFCreator installed on my computer. I can share it on the network and find it on the network, but when i "Add a network Printer" everything goes as normal, but nothing really changes. It still has the same name and everything and no reference in the VBA reference Library?? Anyway, code below is what I am using. Everything works, but I just installed Adobe CS3 and it may have messed things up a little. Pdf files are saved in the proper folder with the proper name and extention, but Acrobat says it is not coded properly to open. ConversionsPlus Info says it is an Illustrator file and it will open with Illustrator/with font issues, but it opens! I'll keep working on it and if I find anything that may help someone else I will post it.

Sub PrintPDF()
'
' PrintPDF Macro
' Macro recorded 3/5/2008 by Jim
'

'
'worksheet
Dim ws As Worksheet
'define ws
Set ws = ActiveWorkbook.Worksheets("MAIN")
'directory of your pictures
Dim server As String
server = "\\xxx\xxxxxxx\xxxxxxxxxxx\xxxxxx\xxxxxxxx xxxxxx\2008\Ready\Printed\"
ActiveSheet.PageSetup.PrintArea = "$P$1:$W$36"
Application.ActivePrinter = "Adobe PDF on Ne05:"
Application.Wait Now + TimeSerial(0, 0, 2)
ActiveWindow.SelectedSheets.PrintOut Copies:=1, ActivePrinter:= _
"Adobe PDF on Ne05:", Collate:=True, PrToFileName:=server & Left(v_filename, Len(v_filename) - 4) & ".pdf"
End Sub

Thanks Again to all that helped me with this. It is truly appreciated.

Charlize
03-10-2008, 01:10 AM
Strange, can't tell you what's going wrong for you. Are you still interested in that word thing to replace the pdf routine ? Will post it as a zipfile if interested and when testing has been done.

Charlize

jamward
03-10-2008, 07:00 AM
Yes sir, that may be the answer to my problem.
Thanks,

jamward
03-10-2008, 07:05 AM
quick question. i have another project going for a guy that uses a spreadsheet to calculate band uniform sizes based on waist, chest, height, etc. measurements. should i just start a new thread? not sure what the forum protocol would be for this.
Thanks,

Charlize
03-10-2008, 04:25 PM
Yes sir, that may be the answer to my problem.
Thanks,Attached a zipfile. Included are a workbook and a word template. The idea is to put those two files in a directory. In that directory you create a new one, named Pictures and in that directory Pictures, you create a new one, named Printed.

Some modifications could be a modeless userform to show the progress, some error handling to stop when an error occures, a directory picker, ...

Have fun with it.

Charlize

ps.: If you want to rename the word template, be sure to change the coding. My code uses Photosheet.doc as the name.

jamward
03-11-2008, 05:00 AM
I may have inadvertantly turned off a default reference library. On this line at the "Left" command I get:

http://wardesign.com/public/error1.jpg

list on next reply. can you see something missing or can i reset to defaults?

Thanks

jamward
03-11-2008, 05:01 AM
http://www.wardesign.com/public/error2.jpg

jamward
03-11-2008, 05:04 AM
offending line:
v_NoOfFiles = CountFiles(Left(v_path, Len(v_path) - 5))

jamward
03-11-2008, 05:20 AM
http://www.wardesign.com/public/VBAGlobals.jpg

Charlize
03-11-2008, 05:35 AM
Untag the references with MISSING in front of it. ie. MISSING: PDFCreator

Charlize

jamward
03-11-2008, 07:38 AM
yippee...that is so sweet. you are the best!

Thanks you so much. It's perfect!

Charlize
03-11-2008, 02:44 PM
It's perfect!Well ..., it's never finished, you could always give it a tweak here or there to make it run better, smoother, more eyecatching, ...

- animation on a form like in windows when copying stuff
- as a bonus : display the filename that is going to be processed
- or use some kind of circle to be filled completely until copying is finished
- hide excel so it seems no excel is used (be carefull with this)
...

Charlize

jamward
03-11-2008, 03:28 PM
that's the most. going in and "messin'" with stuff to get it just right plus i love to go over things line by line to learn what you did to make things work. It's absolutlely fascinating. I always keep a "good" copy though. Just in case...:>)

Thanks Charlize,
I'm sure that you spent a lot of time doing this for me. You are a great person and appreciate your talent and knowledge more that you can ever know.