Replies

Thanks, I missed that. Dohl.

Hello Jaggi

I used the following stripped down version of your code. Very simple.
I have hard coded a path for simplicity. Converted docs are saved to the same location.
If I have two word documents (word 2010) and one document is password protected the macro pauses and requests a password.

If there is a pdf in the folder it seems to hang before failing.

Always disable resume next for debugging purposes.
Pay attention to the use of document vs documents and pay particular attention to the set commands in your code.

G

Code:

Sub jaggiTest()
Dim fs As Object
Dim oFolder As Object
Dim tFolder As Object
Dim oFile As File
Dim strDocName As String
Dim intPos As Integer
Dim locFolder As String
'On Error Resume Next

locFolder = "D:Datatestjaggi"
'Application.ScreenUpdating = False
    Set fs = CreateObject("Scripting.FileSystemObject")
    Set oFolder = fs.GetFolder(locFolder)
    For Each oFile In oFolder.Files
       Application.Documents.Open (oFile.Path)
        strDocName = ActiveDocument.Name
        intPos = InStrRev(strDocName, ".")
        strDocName = Left(strDocName, intPos - 1)
        ActiveDocument.ExportAsFixedFormat OutputFileName:=locFolder & strDocName, ExportFormat:=wdExportFormatPDF
            
        ActiveDocument.Close
    Next oFile
    'Application.ScreenUpdating = True
    
   MsgBox ("Documents are successfully Converted!")
End Sub

Hi Jaggi

Please comment out on error resume next.
Run the macro again to see the error dialog.
G

Hi

I will admit to be confounded. I didn’t give you a hope in Hades of getting close.

It converts the document to PDF, but when I am trying to add the code to close the document.

Do you actually see a .pdf created in addition to the .dot(?)
If so all bets are off. What happens with

documents.close saveChanges:=wdDoNotSaveChanges

See this link at msoft about the Close method

Cheers
G

Hi Maud

Space Mid String
Thanks for the advice on substitute() v Trim().
I was thinking that it was necessary to preserve the space mid string in that Post Code.
I wondered how it might mess with the URL though.
G

Hi
I thought there would have been a method to refresh the validation. I can’t find one.

I started playing around trying to test if the current cell had validation applied and if not then apply some.
In this example the range h1:h5 had a list of colours.

I would be interested to watch for others’ comments on this.

I wondered if an auto macro could be run on workbook_open that selected the last cell in the mileage column and checked for validation.
The little bit of following code is ugly but it works. I don’t like creating and then trapping for error conditions. Somebody might have a better idea.

In this example I am looking for a validation type. These types have constants 0-7.
I created the validation code in the With Selection.validation … End with structure by recording the macro that resulted when I clicked the validation button.

I added some more thoughts later in the day.

Code:

Sub TestValidation()
On Error GoTo errorHandler
If ActiveCell.Validation.Type > -1 Then
    'MsgBox (" has validation")
    Exit Sub
End If
errorHandler:
Select Case Err.Number
    Case 1004
    With Selection.Validation
        .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
        xlBetween, Formula1:="=$H$1:$H$5"
        .IgnoreBlank = True
        .InCellDropdown = True
        .InputTitle = ""
        .ErrorTitle = ""
        .InputMessage = ""
        .ErrorMessage = ""
        .ShowInput = True
        .ShowError = True
    End With
    Case Else
    'add stuff here
End Select
End Sub

Additional thoughts.
I have attached a workbook with an auto workbook_open macro in the this workbook object.

The code checks the first empty cell in the column for data validation and if necessary adds a validation rule. I also rejigged the error checking a bit for discussion.

41532-validationTest

Cheers
G

Hi
I found this link and thought it might help you.

I ran the code on Office 2010 and created a reminder OK. I commented out a line that references a nextbusinessday function. Read his page.

I cant guarantee anything.
I don’t know the author but he deserves credit for this.

Cheers
G

Hi
I think that the pivot views on tables and the ability to create pivot queries in Access might help.

Then there is the ability to export to Excel and use the Pivot table functionality within Excel.

PowerPivot might be another really powerful tool for you to play with. This is the free Excel add-in. That lets you connect directly to the database and introduces an expanded range of functions as well as DAX (Data Analysis Expressions).

See more about DAX here.

Cheers
G

Hi

The following line in you code means that the routine has to open the document. Password required !!

Code:

Set d = Application.Documents.Open(oFile.Path)

Do all the documents have the same password? A forlorn hope.

I guess you dont have access to the hard copies. If you did then you could scan them to pdf.

Nice to know the security works !

You can include the password in the code that opens a document.
It is not recommended to hard code this in a routine however.

see this linkat msoft.

Cheers
G
Geof

Hi

Here is a simple example

Code:

Sub makeURL()
Dim sURL As String
    sURL = Range("a2")
    sURL = sURL & Range("a3")
    sURL = sURL & Range("a4")
    sURL = sURL & Range("a5")
    sURL = sURL & Range("a6")
    sURL = sURL & Range("a7")
    sURL = sURL & Range("a8")
    
    Range("a10").Value = sURL
    Range("a11").Select
    With ActiveSheet
        .Hyperlinks.Add anchor:=.Range("A11"), _
        Address:=sURL
    End With

End Sub

Screen shot of associated spreadsheet.
41495-makeURL

Obviously you need to work on a loop to drag in 60 cells and trim each up a bit.
Hope it helps

Cheers
G

Hi

The vba Trim() function might work for you in place of the substitute expression.
Put something in A1 that contains spaces fore and aft then run this.

Code:

Sub testTrim()
    Dim sNew As String
    sNew = Trim(Range("a1"))
    ActiveCell.Offset(1, 0).Select
    ActiveCell.Value = sNew
End Sub

G

Hi

I have office 2010 and they are in
C:Program FilesMicrosoft OfficeOffice141033QuickStyles

Just a guess try C:Program FilesMicrosoft OfficeOffice151033QuickStyles.

Cheers
G

Hi sbdale
Welcome to the world of date arithmetic.
You need to understand that excel treats dates as serial numbers. (Capt Kirk says star date 42214)
We mere mortals need dates formatted to show years, months and days.

See this postfrom the office support folks at microsoft.

Enter a date in a cell and experiment with the formatting (Ctrl + 1).

Experiment with the datedif() function as well. See this linkfor info.

Cheers
G

Hi

You could try the F5 key.
Select the option for visible cells.

Make sure you have the data selected first though.

Without knowing your exact intentions I would suggest that you read up on pivot tables.

G

Hi
or this

=C2-DATE(YEAR(C2),1,1)+1

Then there is =DatedIf function.
=DatedIf(date1, date2, intervalType)

Cheers
G