Bat file question

[ipbr21054]

Hi,
I was reading about how good etc Bat files were.

I see you can have a button which would run a Bat file.

Just how good are they ?

Running a Bat file would it be able to apply a hyperlink to the number in the active cell.
Obviously the path to where the files would be kept would be advised.

So for example.
Invoices are all stored at C:\Users\Ian\Desktop\Invoices
The active cell on the database was say invoice 100

Could the Bat file apply a hyperlink to Invoice 100 on the database for where it is stored as shown in path above.
So if i then click on this 100 value the invoice would then be shown ?

 

[Jaafar Tribak]

Hi,
I was reading about how good etc Bat files were.

I see you can have a button which would run a Bat file.

Just how good are they ?

Running a Bat file would it be able to apply a hyperlink to the number in the active cell.
Obviously the path to where the files would be kept would be advised.

So for example.
Invoices are all stored at C:\Users\Ian\Desktop\Invoices
The active cell on the database was say invoice 100

Could the Bat file apply a hyperlink to Invoice 100 on the database for where it is stored as shown in path above.
So if i then click on this 100 value the invoice would then be shown ?

If I understand correctly, you can do this with plain vba as follows :

Sub Test()

    Const FILE_PATH As String = "C:\Users\Ian\Desktop\Invoices\"
    
    If Len(Dir(FILE_PATH & ActiveCell.Value)) Then
        ActiveCell.Hyperlinks.Add Anchor:=ActiveCell, Address:=FILE_PATH & ActiveCell.Value
    End If
End Sub

 

[ipbr21054]

Hi,
Am i missing something here ?
I select the cell which shows 500.
In the Invoices folder on the desktop is a file named 500.
When i press the button nothing happens & no error message is shown ?


Code i have is supplied below.

Private Sub HyperlinkButton_Click()    Const FILE_PATH As String = "C:\Users\Ian\Desktop\Invoices\"
    
    If Len(Dir(FILE_PATH & ActiveCell.Value)) Then
        ActiveCell.Hyperlinks.Add Anchor:=ActiveCell, Address:=FILE_PATH & ActiveCell.Value
    End If
End Sub

 

[ipbr21054]

Evening,
Ive looked at this again and see an issue.
Ive now put this code supplied below in a macro & assigned it to a button.

I click in the cell in question which currently shows 500 then i click on the button to run the macro.
What should now happen is that the 500 number in that cell should be hyperlinked so next time i click on it that invoice number 500 is shown on the screen.

However nothing happens when i do the above BUT i have noticed this.
What i did get it to do was if i clicked in an empty cell then run the macro the path is then pasted into that empty cell,does this help ?

Do you see why or can advise a code that will do as expected,thanks.

Sub hyperlink()

    Const FILE_PATH As String = "C:\Users\Ian\Desktop\Invoices\"
    
    If Len(Dir(FILE_PATH & ActiveCell.Value)) Then
        ActiveCell.Hyperlinks.Add Anchor:=ActiveCell, Address:=FILE_PATH & ActiveCell.Value
    End If
End Sub

 

[Jaafar Tribak]

Evening,
Ive looked at this again and see an issue.
Ive now put this code supplied below in a macro & assigned it to a button.

I click in the cell in question which currently shows 500 then i click on the button to run the macro.
What should now happen is that the 500 number in that cell should be hyperlinked so next time i click on it that invoice number 500 is shown on the screen.

However nothing happens when i do the above BUT i have noticed this.
What i did get it to do was if i clicked in an empty cell then run the macro the path is then pasted into that empty cell,does this help ?

Do you see why or can advise a code that will do as expected,thanks.

Sub hyperlink()

    Const FILE_PATH As String = "C:\Users\Ian\Desktop\Invoices\"
    
    If Len(Dir(FILE_PATH & ActiveCell.Value)) Then
        ActiveCell.Hyperlinks.Add Anchor:=ActiveCell, Address:=FILE_PATH & ActiveCell.Value
    End If
End Sub

I see this thread is related to the other thread where you mentioned that the invoice files are pdf files - Right ?

The code doesn't work because you omitted the file extension .

Try this and see if it now works :

Sub hyperlink()

    Const FILE_PATH As String = "C:\Users\Ian\Desktop\Invoices\"

    If Len(Dir(FILE_PATH & ActiveCell.Value & ".pdf")) Then
        ActiveCell.Hyperlinks.Add Anchor:=ActiveCell, Address:=FILE_PATH & ActiveCell.Value & ".pdf"
    End If
End Sub

 

[ipbr21054]

Many thanks,
A quick try and it worked well.
I will run through the other now.

The other thread was asking for a 2 button msg box,this text that looks like this post was just advising what was currently in use.

 

[ipbr21054]

I have now applied links to all in question and perfect,saves me so much time.
Many thanks for the continued support.

I am sorry to say that i had another after thought.

If the active cell is anything other than a cell in column P then the macro will not run & we should see a msgbox with just OK option to press.

Text should be,
Please Select Invoice Number.

Clicking OK will then close msgbox where user will then select a cell in column P and this time when clicking the button the macro will run.

Basically only run macro if active cell is in column P

Many thanks.

 

[Jaafar Tribak]

I have now applied links to all in question and perfect,saves me so much time.
Many thanks for the continued support.

I am sorry to say that i had another after thought.

If the active cell is anything other than a cell in column P then the macro will not run & we should see a msgbox with just OK option to press.

Text should be,
Please Select Invoice Number.

Clicking OK will then close msgbox where user will then select a cell in column P and this time when clicking the button the macro will run.

Basically only run macro if active cell is in column P

Many thanks.

Maybe this :

Sub hyperlink()

    Const FILE_PATH As String = "C:\Users\Ian\Desktop\Invoices\"

    If ActiveCell.Column = Columns("P").Column Then
        If Len(Dir(FILE_PATH & ActiveCell.Value & ".pdf")) Then
            ActiveCell.Hyperlinks.Add Anchor:=ActiveCell, Address:=FILE_PATH & ActiveCell.Value & ".pdf"
        End If
    Else
        MsgBox "Please Select Invoice Number.", vbInformation
    End If
End Sub

 

[ipbr21054]

I never doubted you for 1 minute.
You came up trumps again.

Just what i needed.

have a nice evening.