Faulty Code

[jabawalkie5000]

Hi All

I am trying to get this code to open up several documents stored in a sheet called "tab"

It doesnt work, can you spot the problem?

Thanks

Sub openFiles2()
Dim strpath As String
Dim rngFileNames As Range: Set rngFileNames = ActiveSheet.Range("A2", Cells(Rows.Count, "a").End(xlUp))
Dim FileNameCell As Range
Application.ScreenUpdating = False
strpath = "F:\J\Procurement\Change Management\Del\"
For Each FileNameCell In rngFileNames
If Not IsEmpty(FileNameCell) Then Workbooks.Open strpath & FileNameCell.Value
Next FileNameCell
Application.ScreenUpdating = False
End Sub

 

[Weaver]

How does its failing manifest itself?

Just had a thought - is the sheet with the filenames active when the code is run?

 

[jabawalkie5000]

I get the message

excel gives a runtime error 1004 - file format not valid

The file is hidden

Thanks

How does its failing manifest itself?

Just had a thought - is the sheet with the filenames active when the code is run?

 

[jim may]

It doesn't work - can you be more helpful?
What happend when you run the code?
Is the code running without error (bombing)?
Is your code in a Standard module?

 

[jabawalkie5000]

its running but nothing is happening. I even changed the path for something invalid, it didnt even pick that up!!

 

[Weaver]

The fact that nothing is happening suggests it's going through the range and finding nothing but empty cells.

try

Set rngFileNames = sheets("tab").Range("A2", sheets("tab").Cells(Rows.Count, "a").End(xlUp))

 

[jabawalkie5000]

Hi thanks it works, only problem is it wont let me open a word document or a powerpoint document in the code. I have added the word and powerpoint reference libraries, is there a code to invoke the opening of both applications and getting the files to be opened?

Thanks

The fact that nothing is happening suggests it's going through the range and finding nothing but empty cells.

try

Set rngFileNames = sheets("tab").Range("A2", sheets("tab").Cells(Rows.Count, "a").End(xlUp))

 

[Weaver]

You could use the shell command

shell "winword.exe ""c:\myfiles\this is a test.doc""

Note you need the two sets of double quotes if the file name has a space.

Put it together like:

Sub test()
    strpath = "F:\J\Procurement\Change Management\Del\"
    fname = "mydocument.doc"
    strshell = "winword.exe """ & strpath & fname & """"
    Shell strshell
End Sub

You could use Select Case or some if... then to test the last 3 or 4 chars of the filename if there are several apps you might need to use.