CreateObject working in excel 2003 but not 2010

Question

Essentially the code is just creating a text file in a directory that is filled with information. Like the title says I can open the worksheet in excel 2003 and it will run fine but if it runs in 2010 there is an error that pops up saying Run-time error "70" Permission denied.

Here is the code:

Dim i As Integer
Dim dataType As String
Dim dataName As String

Application.ScreenUpdating = False

ChDir ThisWorkbook.Path  'sets the directory to where the workpaper is
ChDir ".." 'Moves up a directory
ChDir (".\folder\")

direct = ThisWorkbook.Path

Worksheets("SchSB XML").Select

Set fs = CreateObject("Scripting.FileSystemObject")
Set a = fs.CreateTextFile(direct & "\" & flName &  ".xml", True)

a.writeline ("<?xml version=" & Chr(34) & "1.0" & Chr(34) & " encoding=" _
    & Chr(34) & "UTF-8" & Chr(34) & " standalone=" & Chr(34) & "yes" & Chr(34) & "?>")
a.writeline ("<SchSB xmlns:ns1=" & Chr(34) & "http://efast.gov/2011" _
    & Chr(34) & " xmlns:xsi=" & Chr(34) & "http://ww.w3.org/2001/XMLSchema-instance" & Chr(34) & ">")

There is a loop after this but it contains the same a.writeline code as above that rotates through ~100 cells

Debug is showing the Set fs = CreateObject("Scripting.FileSystemObject") is breaking and showing the permission denied error.

What is the value of direct & "\" & flName if you debug it? — Siddharth Rout
Not sure if this is causing, but I don't see your dimension of fs If you write Dim fs as Object does it help? Or what if you use early binding? Does it work then? — Scott Holtzman
What other variables are changing? Are both versions of Excel using the same operating systme, and the same user permissions? Based on this post the problem may be that the Excel 2010 user is not a local admin. — Daniel
Although you are using late binding, have you checked the references section in 2010 just to see if the "Microsoft Scripting Runtime" is present? — Matt Donnan
Are you running both Excel 2003 and Excel 2010 on the same client? — Olle Sjögren

Matt Donnan Matt Donnan · Accepted Answer · 2012-08-29T08:03:59

Even with 'late binding' you still need to check that the entry for "Microsoft Scripting Runtime" is present in the references list, and also that it is pointing to the correct filepath, e.g a network share that user's across all computers would have access to like:

Z:\Lib\scrrun.dll

As oppose to:

C:\Users\"Username"\...

CreateObject working in excel 2003 but not 2010

2 Answers