[chatty]

I am trying to automate the faxing of several individual excel pages in one spreadsheet to specific fax numbers.
I can fax each page one at a time from Excel 2003 with WFP 8, using the WFXXL97.xls macro file and the wfxFaxNum field, but I want to use the additional field values such as wfxSetHold and wfxShowsendScreen so that I can queue everything in the outbox and then fax everything with one click of a button. These field values only appear in the WFXL2000/2002.xls macro, so I installed WFP 10 and the Excel 2002 macro, but the macros/fields have no effect and nothing works.
Also tried installing my old copy of Excel 07 with the various macro versions – no luck
Should I try and find a copy of Excel 2000 or 2002?

[JohnD]

What version of WinFax PRO 10 ? (See Help, About) and are you using Windows XP?

[chatty]

Hi John
Thankyou for the quick response
I am using WIN XP SP3, Winfax pro10.02

[JohnD]

10.02 is not compatible due to a known bug that effects DDE/SDK, you need version 10.0, 10.01 or 10.03.

The options wfxSetHold and wfxShowsendScreen were added to the Excel macros included with version 10 and above.

[chatty]

Thanks John
any ideas where I can get an upgrade to 10.03?

[JohnD]

see my response to your other posting regarding 10.03

[chatty]

Thanks John
I bought v10.0 off E-bay and upgraded to 10.01. Everything is working well, except I want to add a few lines to my macro to prevent blank pages (ie pages with no data from faxing)
I got this macro below from an Excel forum but the Winfax macro is so long I wouldn’t know where to insert it

Sub EmailWorkbook()
Dim ws As Worksheet
For Each ws In Worksheets
If Not IsEmpty(ws.Range(“A3:IV65536”)) Then ‘header in first two rows
ws.PrintOut ActivePrinter:=”Winfax?”
End If
Next ws
End Sub

[Administrator]

Are you planning on modifying the stock WinFax macro? do you have this automated now by use the “wfxfaxnum” named cell?

this code you listed above will create multiple print jobs, not just a single print job which probably will not work for an automated process. If your automating WinFax, you need (a fax number+name(optional)+a print job+attachment(optional)”, sending a new print job requires the fax number again, thus resulting in a 2nd fax,3rd,4th fax…which the stock WinFax macro does not do.

What you probably need to do instead, is set a range so only a single print job is generated, which would omit the blank pages. Example, print this workbook, but only pages 1,3,4,5,7 as a single print job (on you find that pages 1,3,4,5,7 actually had data to print)

psuedo code here…

Dim ws As Worksheet
For Each ws In Worksheets
If Not IsEmpty(ws.Range(“A3:IV65536”)) Then ‘header in first two rows
‘
‘ code in here to determine which sheets contain data here
‘
End If
Next ws
‘
ws.Sheets(Array(“Sheet1”, “Sheet3″)).PrintOut ActivePrinter:=”Winfax”
‘prints the sheets listed in the the array assuming sheet 1 and sheet 3 had data found.
‘

I’m not an Excel VBA expert, so don’t know if that would actually work, there probably is a more elegant method to accomplish this same task, like hiding blank sheets before printing them, then unhiding the blank sheets after you printed them to WinFax.

EDIT:

I did a simple test, and it worked by printing sheet 1 and sheet 3 of a workbook. There is a line in the macro code that is found in Sub WinFaxMacro()

[chatty]

Hi Moderator
Thanks for the reply
I had already managed to get it all to work!

I inserted the following few lines into the macro just before the end:

If Range(“C65:C65”) > “0” Then
ActiveSheet.PrintOut
End If
In the spreadsheet all I did was add a SUM formula in cell C65 at the end of the column of data. If there is no data, then the total at cell C65 =0
This will prevent the page from faxing

With regard to making the macro work for multiple pages:
On the first page of my spreadsheet I inserted
wfxFaxNum, wfxRecipient, wfxShowSendScreen (= 0) wfxSetHold (=1) (I want all the faxes to sit in the outbox before I send them)
Each page has to fax to a different supplier, so I then created wfxFaxNumA, WfxRecipientA etc on the next page, wfxFaxNumB on the third page and so on for all the pages.
I then copied my modified winfax macro and then imported it into a new module, one for each supplier.

I then modified each copied macro by
1) renaming it to WinfaxMacroA, WinFaxMacroB etc
2) searched and replaced wfxFaxNum with wfxFaxNumA, wfxRecipient with wfxRecipientA etc

I tested each page by running the specific macro for each page, then I recorded a macro that starts at page one, runs the WinfaxMacro, goes to the next page , runs the WinFaxMacroA , next page etc. Then added a custom “Fax All” button to the toolbar linked to this new macro.
All the user has to do is click on the button. It all works very smoothly with no problems

[Administrator]

Glad it worked out! This is one area where WinFax can really out perform other fax programs out there, many of them don’t have this ability to allow for automation with other software.

[chatty]

@Moderator wrote:

Glad it worked out! This is one area where WinFax can really out perform other fax programs out there, many of them don’t have this ability to allow for automation with other software.

Hi again
I have been busy developing other parts of my spread sheet.
One of the problems I have is that I have to re-create and rename the winfax macro for every page to be faxed sequentially, and I have over 70 pages, one for each supplier that needs to be faxed with a daily order.
So for example, sheet 1 is linked to winfaxmacro1, sheet 2 is linked to winfaxmacro 2.
So I have to create 70 macros by modifying the text in the original macro: wfxFaxNum is changed to wfxFaxNumA, wfxRecipient is changed to wfxFaxRecipientA etc.
Excel doesn’t allow me to copy the defined names wfxFaxNum; wfxFaxRecipient; wfxShowSendScreen; wfxSetHold across multiple pages, so in each worksheet I have to define 4 names: wfxFaxNumA; wfxFaxRecipientA wfxShowSendScreenA; wfxSetHoldA etc, using insert name>define.
I am try to find a way so that I don’t have to create 70 individual macros, but to just use the main winfax pro macro “locally” on each sheet. Any ideas?

[JohnD]

I suggest creating a new macro that goes through all the active sheets one at a time, printing each indiviual sheet to WinFax and including the receipient details for each sheet. Repeat for each sheet that contains fax information until you reach the end of sheets.

[chatty]

Hi John
I see what you mean, but while I’m recording the macro where would I tell winfax to look for the recipient details ? IE: do you mean I must ditch the idea of using embedded names (like wfxFaxNUM) and refer winfax to a list on that page?

[JohnD]

@chatty wrote:

Hi John
I see what you mean, but while I’m recording the macro where would I tell winfax to look for the recipient details ? IE: do you mean I must ditch the idea of using embedded names (like wfxFaxNUM) and refer winfax to a list on that page?

I’m not sure if recording the macro with Excels macro record function will do everything you need, since for something sophisticated you would need modifications. I don’t know how your spreadsheets are formatted, but you could find a specific fax number at a cell location and use that. Loop through the pages/sheets, send the recipient data to winfax for each page, print the page to winfax, and repeat until you reach the end of the sheets. End result is X number of held faxes in the outbox, X=number of active sheets with proper fax details.

[chatty]

Hi John, thanks for the response.
What do you mean by “you could find a specific fax number at a cell location. Loop through the pages/sheets, send the recipient data to winfax for each page,” Are you saying that the user needs to do a few manual steps for each page? Remember I am trying to make this a totally automated process for the user.

[Author]

Posts