CODEWITHSUNDEEP
vbaexcel
Gravity Kills
Gravity Kills

Reputation: 99

CreateObject working in excel 2003 but not 2010

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.

Upvotes: 2

Views: 1527

Answers (2)

Tamer Nasr
Tamer Nasr

Reputation: 1

It's possibly because you have a row of code to the effect of: Set fs = CreateObject("Scripting.FileSystemObject") or Set a = fs.CreateTextFile(direct & "\" & flName & ".xml", True) inside a loop. This may cause problems as VBA thinks you are trying to overwrite an existing file, open a file that's already open, or something to that effect. To get around this issue CreateObject and/ or `CreateTextFile outside of the loop, either before or after (most probably before), then write whatever data you want to it from inside the loop.

Upvotes: 0

Matt Donnan
Matt Donnan

Reputation: 4993

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"\...

Upvotes: 1

Related Questions