Posts by alring

Hi Roy

It was my mistake. The closed workbook wasn't updated for some days.

STUPID ME.

Hi All

Stupid me.

The closed workbook was not updatetd for some days, that's why the error apears.

Sorry for having used your time.

/Mads

It say that the value isn't present.

But the value is in the closed workbook. Remember that VLookup works asune as the closed workbook is open.

Hi Roy

Yes I'm sure.

Hi all

I have a workbook with a VLookup formula to a closed workbook. It works most of the time.

But sometimes it returns #I/T in some rows. And it is not the same row from time to time. If I then open the closed workbook, then it works perfect.

Can someone please tell me what is wrong and maybe how to fix it?

Thank you in advance.

Regards

Mads A

Glad to be able to help.

Hi wendyl

Try this:

Sub Name_Area()
    Range("A1:G8").Select
    ActiveWorkbook.Names.Add Name:="GRADES", RefersToR1C1:="=Sheet1!R1C1:R8C7"
    Range("A1").Select
End Sub

Hi,

Try this:

Sub Cell_test()

Sheets("Sheet1").Select 'Change "Sheet1" to your sheetname

Row = 1

For iRun = 1 To 15 'Change 15 to the number of row you have

Cells(Row, 1).Select
If Not IsEmpty(ActiveCell) Then
    Cells(Row, 11).Select
        If Not IsEmpty(ActiveCell) Then
            K = 1
        Else
            K = 0
        End If
    Cells(Row, 12).Select
        If Not IsEmpty(ActiveCell) Then
            L = 1
        Else
            L = 0
        End If
    If K = 0 And _
        L = 0 Then
        Cells(Row, 13).Select
            If Not IsEmpty(ActiveCell) Then
            Range("A1").Select
                GoTo Out
            Else
                Cells(Row, 4).Select
                If ActiveCell > Date And _
                    ActiveCell < Date + 3 Then
                    Rows(Row).Select
                    Exit Sub
                End If
            End If
    End If

Else
    GoTo Out
End If

Out:
Row = Row + 1

Next 'iRun

End Sub

Remember to change Sheet Name And the number of Rows.

Cell B37 is in the exsamble "501" and it shall find a folder named 501 Monte

Cell B38 is in the exsamble "139" and it shall find a folder named 139 LWD

Next time the numbers in the two celsl will be different and also the name on the folders.

The folders that I want to find will always start with the numbers in the two cells, but will always has another text behind the numbers.

The file I want to look in will always be Cell B37 follow by a dot follow by cell 38 follow by space follow by text. But that is type in cell B1 (Cells(1,2)).

Hope this will explain. If not then just say that.

Hi Carim

Yea I have tried that, and it don't work for me.

Here is the total code, with start up next part of code:

Sub master_01()

Path = "='H:\Orders\" 'Path to the file
Path_1 = Cells(37, 2) & "\" 'Folder name "501"
Path_2 = Cells(38, 2) & "\" 'Folder name "139"
Path_3 = "08 Documentation\" 'Folder in the Path
File_1 = "[" & Cells(1, 2) 'Start of Workbook name "501.139 Monte"
File_2 = " Tidsplan.xlsm]Gantt'!" 'End of workbook name
cel = "B6:B25" 'Cells where data shall be take from
mydata = Path & Path_1 & "*" & Path_2 & "*" & Path_3 & File_1 & File_2 & cel 'Path and Workbook name and Cells
Before_FAT 'Start up next macro
End Sub

Sub Before_FAT()
    With ThisWorkbook.Worksheets(1).Range(Cells(6, 2), Cells(25, 2)) 'Celler der indskrives i
        .Formula = mydata
        .Value = .Value
    End With
End Sub

Hi there,

I have this formula in VBA to get data from at closed Workbook.

mydata = 'H:\Orders\501 Mor\139 LWD\08 Documentation\[501.139 Monte Tidsplan.xlsm]Gantt'!B6:B25

Right now a part of my VBA look like this:

Path = "='H:\Orders\" 'Path to the file
Path_1 = Cells(37, 2) & "\" 'Folder name "501 Mor"
Path_2 = Cells(38, 2) & "\" 'Folder name"139 LWD"
Path_3 = "08 Documentation\" 'folder in the Path
File_1 = "[" & Cells(1, 2) 'Start of Workbook name "501.139 Monte"
File_2 = " Tidsplan.xlsm]Gantt'!" 'End of workbook name

cel = "B6:B25" 'Cells where data shall be take from
mydata = Path & Path_1 & Path_2 & Path_3 & File_1 & File_2 & cel 'Path and Workbook name and Cells

What I would like is that Path_ 1 will be "501" and then Wildcard. And Path_ 2 will be "139" and then Wildcard. So the code will look like this:

Path = "='H:\Orders\" 'Path to the file
Path_1 = Cells(37, 2) & "\" 'Folder name "501"
Path_2 = Cells(38, 2) & "\" 'Folder name "139"
Path_3 = "08 Documentation\" 'Folder in the Path
File_1 = "[" & Cells(1, 2) 'Start of Workbook name "501.139 Monte"
File_2 = " Tidsplan.xlsm]Gantt'!" 'End of workbook name

cel = "B6:B25" 'Cells where data shall be take from
mydata = Path & Path_1 & "*" & Path_2 & "*" & Path_3 & File_1 & File_2 & cel 'Path and Workbook name and Cells

I hope this make sense, if not then please tell me.

Thank you in advance for all your time and help.

Alring

Re: Print a PDF file using VBA

Hi Kenneth

I though so that Excel can't control Adope's printing menu. I have made the changes in Adope and all works perfect now. Thanks for your time and your help.

Re: Print a PDF file using VBA

Hi MrRedli and Kenneth

Both of them works perfectly. Thank you to you both.

Now I can see that both of the codes print the Pdf file in A4. Is it possible to have the code to prnt A3 as the PDF file are savede in??

Thank you in advance.

Re: Print a PDF file using VBA

Hi Kenneth

Thank you for the answer. Bur don't understand what you mean about the pass command line parameters. Where shall I put in the letters you write?

Where in the code is the mistake that do that the code don't work??

Thank you in advance
/Mads

Hi all

I'm using Excel 2010 and have Adobe Reader 11.

I'm trying to make a code in VBA that can print a specific PDF file. It's OK if the file opens first but it not necessary.

The file name change from time to time but it’s written in a specific cell in Excel. Let us say that it’s A1 in sheet1. The path is the same every time so that can be written directly in the code.

I have found this code on WWW but it doesn’t work for me.

Option Explicit
Public Declare Function ShellExecute Lib "shell32.dll" Alias "ShellExecuteA" ( _
ByVal hwnd As Long, _
ByVal lpOperation As String, _
ByVal lpFile As String, _
ByVal lpParameters As String, _
ByVal lpDirectory As String, _
ByVal nShowCmd As Long) As Long
 
Public Function PrintThisDoc(Formname As Long, FileName As String)
On Error Resume Next
Dim X As Long
X = ShellExecute(Formname, "Print", FileName, 0&, 0&, 3)
End Function
 
Sub testPrint()
Dim printThis
Dim strDir As String
Dim strFile As String
 
strDir = "J:\KAL\Machines\Extruder I\Køredata\Tegninger\"
strFile = "370_Rev.pdf"
 
printThis = PrintThisDoc(0, strDir & strFile)
End Sub

I do hope that someone can help me.

Thank you in advance

Re: Excel macro to print a section from word

It seems to be OK. But any way thenak you for the help.

Re: Excel macro to print a section from word

Hi cytop

Thank you for the help. It works perfect.
Maybe it's my word that now have a problem because if a section is on 2 pages then there come 3 pages out of the printer.
But the macro works. THANK YOU.

Hi

I use Excel to open word documents and print the word document. I use this code to open word and to print it.

Set wdApp = CreateObject("Word.Application")
    wdApp.documents.Open Filename:=aNameAndPath & Filename
        wdApp.Visible = True


    wdApp.ActiveDocument.PrintOut , Copies:=1

Now i have word documents that is divide into Sections. I would like to find a code so that it's only Section 2 in the word document that is been printed.

Thank you in advance.

Alring

Re: Concatenate text in cells to a formula.

Hi Batman

You saved my day. Thank you for the help.

As long as I in A1 write [min 2015.xlsm] and in A2 write Feb'!$C$9 then all is perfect. And as you said the other document have to be open.

Regards
Mads

Re: Concatenate text in cells to a formula.

Hi Again

I know that I'm missing a ":" after C i celle A1. Sorry.

In call A3 the formula should be:
=SUM('C:\mads\private\[min 2015.xlsm]Feb'!$C$9)

Thank you