Posts by alring

alring · Mar 31st 2006

Re: Search and replace with a number that starts with zeros

Hi

Go to Format Cells and click on Custom. In the bar where there is writen Standard you write ##00

Alring

alring · Mar 29th 2006

Re: Calculating between dates and times

Hi

See if you can use this one.

This one counts the dates and the hours. But the result comes as dates:hours:minutes.

alring

alring · Mar 17th 2006

Re: Continue macro

Hi there

I got it not. I have changed the code total after trying alot of different things. So now it's working. The code look like this now.

Code

'   Search file and open if exist
    bNameAndPath = Range("A9")
    f = Range("D1") & "NKT" & Range("D2")
Set fs = Application.filesearch
With fs
    .LookIn = bNameAndPath
    .filename = f
    If .Execute > 0 Then
        Workbooks.Open filename:=bNameAndPath & f
    ActiveWindow.SelectedSheets.PrintOut Copies:=1
    ActiveWindow.Close SaveChanges:=False
    Else
        MsgBox "Document do not exist."
    End If
End With

Display More

Regards
Alring

alring · Mar 7th 2006

Re: Continue macro

Hi

I have changed the last part of the code. It's the part after:

Code

Application.CutCopyMode = False

I have changed it to:

Code

'   Open Toolslist
    bNameAndPath = Range("A9")
    filesearch = Range("D1") & _
    "NKT" & _
    Range("D2")
    Set fs = CreateObject("Scripting.FileSystemObject")
    Set f = fs.GetFolder(bNameAndPath)
    Set fc = f.Files
    For Each filename In fc
        If Left(filename.Name, Len(filesearch)) = filesearch Then
            ActiveWorkbook.FollowHyperlink bNameAndPath & filename.Name
            Exit For
        End If
    Next
    ActiveWindow.SelectedSheets.PrintOut Copies:=intPrintJobs, Collate:=True
    ActiveWindow.Close SaveChanges:=False

Display More

The path for the file is in A9 and the filename comes from D1 and D2. and it's working as it should.
But I still need to put in a message box that will come if the file don't exist. I have tried to put the code for the message box in after "Exit For" but then I have to press OK 18 times before the message box disapear again.
So if some one knows where I can write the code for the message box then I will be gratefull.
Alring

alring · Mar 6th 2006

Re: Continue macro

Hi Wigi

This is a short code. the right one is very long because I open about 20 Word documents. But it's the last part where I want to open bNameAndPath that where I want to macro to see if the file (Range A10) is there and then print it. If it's not there then come with a message box saying it's not there and then end the macro.

Code

Sub excelToWord_click()
    'Open the word document
    aNameAndPath = Range("A7")
    Set wdApp = CreateObject("Word.Application")
    wdApp.documents.Open Filename:=aNameAndPath
    wdApp.Visible = True
    'copy cells
    Worksheets(1).Range("A1:D4").Select
    Selection.Copy
    Range("A9").Select
    'insert cells
    With wdApp.ActiveDocument.Sections(1)
        .Headers(1).Range.inlineshapes(1).Activate
        Range("A1:D4").Select
        ActiveSheet.Paste
    End With
    'restore Word
    If wdApp.ActiveWindow.View.SplitSpecial <> 0 Then
        wdApp.ActiveWindow.Panes(2).Close
    End If
    If wdApp.ActiveWindow.ActivePane.View.Type = 1 _
    Or wdApp.ActiveWindow.ActivePane.View.Type = 2 Then
        wdApp.ActiveWindow.ActivePane.View.Type = 3
    End If
    wdApp.WordBasic.AcceptAllChangesInDoc
    wdApp.ActiveDocument.PrintOut , Copies:=1
    wdApp.ActiveWindow.Close SaveChanges:=False
    
    Application.CutCopyMode = False
    
    bNameAndPath = Range("A9")
    Filename = Range("A10")
    Workbook.Open Filename:=aNameAndPath & Filename
    ActiveWindow.SelectedSheets.PrintOut Copies:=intPrintJobs, Collate:=True
    ActiveWindow.Close SaveChanges:=False
    
End Sub

Display More

Alring

alring · Mar 6th 2006

Hi

I have a macro that prints alot of other documents. All the documents is listed in Excel. The macro looks into some cells and print the document that is written in those cells. My problem is that the last document that the macro prints maybe isn't there. Meaning that the document maybe isn't made yet. How can I get the macro to see if the document is there? And if it's there then print the document. And if it's not there then a mesage box should come telling that the document can't been printed and then end the macro?

Alring

alring · Jan 31st 2006

Re: Comparing values

Hi tomgribbin

I'm not sure wnat it is you want. But try to look at the attachment to see if it's that you want.

Alring

alring · Jan 31st 2006

Re: Vlookup without false

Hi again

I’m happy to tell you that I have found a third way to skin the cat.

Since I don’t want to change the order to Descending and I also want that if the number I write in A1 is present in the array then I have changed the formula. I have used Biz’s solution and combined it with Counta. Now the formula works as I want it to.

Both Dave and Biz THANK you for the help and guide dings.

Please take a look at the example to see the solution.

Alring

PS:. Thank you for a perfect forum.

alring · Jan 31st 2006

Re: Vlookup without false

Hi

Dave>> It was my mistake. Your formula works perfect. Sorry. And THANK you for the help.

Biz>> Your formula works perfect also. THANK you for the help.

Now I just have the problem to pick one of them. But that's my problem.

THANK you both.

Alring

alring · Jan 31st 2006

Re: Vlookup without false

Hi Dave

Thank you for your reply.

When I use your formula then I get #N/A as result.

Alring

alring · Jan 31st 2006

Hi,

I have a small problem. When I use Vlookup and don't put "False" in the code then Vlookup find a match that is lower and nearest to the number I want to lookup. How can I make it so it will give me the number that is nearest but up? Meaning that if I write 220 then the result should be 6 and not 5.

Please take a look at my exsample.

Alring

alring · Jan 27th 2006

Re: international date settings

Hi,

Why don't you format the cell to be US date format??

If you want to use VBA you can write this if the date is in A1:

Code

Sub date_format()


    Range("A1").Select
    Selection.NumberFormat = "m/d yyyy;@"


End Sub

Or you can under Sheet1 code write this code if the date is in A1:

Code

Private Sub date_format()


    Range("A1").Select
    Selection.NumberFormat = "m/d yyyy;@"


End Sub

alring · Jan 26th 2006

Re: Hyperlink

Balu

A similar code works perfect on my computer. Remember to format A1 on sheet SAP2 to be a hyperlink.

alring · Jan 23rd 2006

Re: Open Hyperlink with no warning

Hi Roy

This is not good.

Thank you for the help. I will tell our IT-department that they have to save the files in another way.

Thanks again.

Alring

alring · Jan 23rd 2006

Re: Open Hyperlink with no warning

Hi Roy

Thank you for your reply.

I'm still geting the warning.

By the way the warning also say: "Some files can contain viruses or otherwise be harmful to you computer. It's important to be certain that the file is from a trustworty source."

Sorry I didn't write the first time.

alring · Jan 23rd 2006

Hi,

I have made the below macro. In cell (5,1) I have a hyperlink that start with http://..... and so on. When I run the macro then I get a warning askin me if I want to open the file the hyperlink is connected to. What can I write in the macro so I don't get this warning?

Code

Sub Open_()
    
    sdir = Cells(5, 1)
    ActiveWorkbook.FollowHyperlink sdir


End Sub

Alring

alring · Jan 17th 2006

Re: Vlookup in second colum

Hi Seti

Thank you for the help. You helped me alot there. I didn't think so long to use Index/Match. Maybe I just need a extra brain.

Again. THANK YOU

alring · Jan 17th 2006

Hi

Normal when I use vLookup then the value I lookup is in the first colum. Is it possible that the value I use to lookup with is in the second colum instead of the first?
Another problem to this that I also need the result from the first colum.

I have attached an exsample. On sheet2 is the table array.

On sheet1 is it cell A2 I want to lookup in the table array.

alring

alring · Jan 17th 2006

Re: Copy and paste to word header

Hi

Now I got the solution for my problem. See below:

Maybe someone can move this back to Excel help, because it's a Excel VBA.

Code

Sub excelToWord_click()
    'Open the word document
    aNameAndPath = Range("A6")
    Set wdApp = CreateObject("Word.Application")
    wdApp.documents.Open Filename:=aNameAndPath
    wdApp.Visible = True
    'copy cells
    Worksheets(1).Range("A1:D4").Select
    Selection.Copy
    Range("A5").Select
    'insert cells
    With wdApp.ActiveDocument.Sections(1)
        .Headers(1).Range.inlineshapes(1).Activate
        Range("A1:D4").Select
        ActiveSheet.Paste
    End With
    'restore Word
    If wdApp.ActiveWindow.View.SplitSpecial <> 0 Then
        wdApp.ActiveWindow.Panes(2).Close
    End If
    If wdApp.ActiveWindow.ActivePane.View.Type = 1 _
    Or wdApp.ActiveWindow.ActivePane.View.Type = 2 Then
        wdApp.ActiveWindow.ActivePane.View.Type = 3
    End If
    wdApp.WordBasic.AcceptAllChangesInDoc
    wdApp.ActiveDocument.PrintOut , Copies:=1
    wdApp.ActiveWindow.Close savechanges:=False

Display More

this macro also print and close the document.

alring

alring · Jan 15th 2006

Re: Macro to input picture in cell and also delete picture

Hi Fendore

Thank you for your reply. The picture have to be put in a cell that have been decribe in the macro.

Also when we start a new year then the picture have to be deleted again.

To explain a little bit more. Is't the pictures of the moon I want to put in the calender. Pictures that show if the moon is full or new.

Alring