Hi,

I'm trying to create a file system object so I can cycle through a
folder and quickly append 30 different text files into one. This is
the short macro I have. When I try to run it, it tells me the
following:

ActiveX component can't create object or return reference to this
object (Error 429)



Sub Gather()
Dim ObjFso As New FileSystemObject
Dim ObjectFolder As Object
Dim ColFiles As Object
Dim ObjFile As Object
Dim T As Integer
Dim MyFile As String
Dim FileNum As Integer
Dim Filenum1 As Integer

FileNum = FreeFile
Filenum1 = FreeFile

MyFile = "Q:\DropBox\csv Files\Appended Data.TXT"


Set ObjFso = CreateObject("Sripting.FileSystemObject")
Set objfolder = ObjFso.getfolder("Q:\DropBox\csv Files")
Set ColFiles = objfolder.Files

Open MyFile For Append As #FileNum


For Each ObjFile In ColFiles
Open ObjFile For Input As #Filenum1
Do Until EOF(Filenum1)
Line Input #Filenum1, Data
Print #FileNum, Data
Close #Filenum1
Loop
Next



End Sub



I have run this same type of procedure before. I can't figure out why
it's giving me this error now. Any help would be appreciated.

Hi

You have a spelling mistake in the string you are passing to CreateObject:

Set ObjFso = CreateObject("Sripting.FileSystemObject")

should be "SCripting.FileSystemObject" ;-)

Richard



"R Tanner" <(E-Mail Removed)> wrote in message
news:f7549798-41c2-4c46-8b78-(E-Mail Removed)...
> Hi,
>
> I'm trying to create a file system object so I can cycle through a
> folder and quickly append 30 different text files into one. This is
> the short macro I have. When I try to run it, it tells me the
> following:
>
> ActiveX component can't create object or return reference to this
> object (Error 429)
>
>
>
> Sub Gather()
> Dim ObjFso As New FileSystemObject
> Dim ObjectFolder As Object
> Dim ColFiles As Object
> Dim ObjFile As Object
> Dim T As Integer
> Dim MyFile As String
> Dim FileNum As Integer
> Dim Filenum1 As Integer
>
> FileNum = FreeFile
> Filenum1 = FreeFile
>
> MyFile = "Q:\DropBox\csv Files\Appended Data.TXT"
>
>
> Set ObjFso = CreateObject("Sripting.FileSystemObject")
> Set objfolder = ObjFso.getfolder("Q:\DropBox\csv Files")
> Set ColFiles = objfolder.Files
>
> Open MyFile For Append As #FileNum
>
>
> For Each ObjFile In ColFiles
> Open ObjFile For Input As #Filenum1
> Do Until EOF(Filenum1)
> Line Input #Filenum1, Data
> Print #FileNum, Data
> Close #Filenum1
> Loop
> Next
>
>
>
> End Sub
>
>
>
> I have run this same type of procedure before. I can't figure out why
> it's giving me this error now. Any help would be appreciated.

Incidentally you can probably achieve the same much faster with the DOS
command COPY - you can execute this thru VBA using the Shell command:

Shell Environ$("COMSPEC") & " /c COPY Q:\DropBox\csv Files\*.*
Q:\DropBox\csv Files\AppendedData.TXT"


Richard



"R Tanner" <(E-Mail Removed)> wrote in message
news:f7549798-41c2-4c46-8b78-(E-Mail Removed)...
> Hi,
>
> I'm trying to create a file system object so I can cycle through a
> folder and quickly append 30 different text files into one. This is
> the short macro I have. When I try to run it, it tells me the
> following:
>
> ActiveX component can't create object or return reference to this
> object (Error 429)
>
>
>
> Sub Gather()
> Dim ObjFso As New FileSystemObject
> Dim ObjectFolder As Object
> Dim ColFiles As Object
> Dim ObjFile As Object
> Dim T As Integer
> Dim MyFile As String
> Dim FileNum As Integer
> Dim Filenum1 As Integer
>
> FileNum = FreeFile
> Filenum1 = FreeFile
>
> MyFile = "Q:\DropBox\csv Files\Appended Data.TXT"
>
>
> Set ObjFso = CreateObject("Sripting.FileSystemObject")
> Set objfolder = ObjFso.getfolder("Q:\DropBox\csv Files")
> Set ColFiles = objfolder.Files
>
> Open MyFile For Append As #FileNum
>
>
> For Each ObjFile In ColFiles
> Open ObjFile For Input As #Filenum1
> Do Until EOF(Filenum1)
> Line Input #Filenum1, Data
> Print #FileNum, Data
> Close #Filenum1
> Loop
> Next
>
>
>
> End Sub
>
>
>
> I have run this same type of procedure before. I can't figure out why
> it's giving me this error now. Any help would be appreciated.

On Oct 10, 11:47*am, "Richard Schollar" <richardschol...@hotmail.com>
wrote:
> Incidentally you can probably achieve the same much faster with the DOS
> command COPY - you can execute this thru VBA using the Shell command:
>
> Shell Environ$("COMSPEC") & " /c COPY Q:\DropBox\csv Files\*.*
> Q:\DropBox\csv Files\AppendedData.TXT"
>
> Richard
>
> "R Tanner" <tanner.ro...@gmail.com> wrote in message
>
> news:f7549798-41c2-4c46-8b78-(E-Mail Removed)...
>
>
>
> > Hi,
>
> > I'm trying to create a file system object so I can cycle through a
> > folder and quickly append 30 different text files into one. *This is
> > the short macro I have. *When I try to run it, it tells me the
> > following:
>
> > ActiveX component can't create object or return reference to this
> > object (Error 429)
>
> > Sub Gather()
> > Dim ObjFso As New FileSystemObject
> > Dim ObjectFolder As Object
> > Dim ColFiles As Object
> > Dim ObjFile As Object
> > Dim T As Integer
> > Dim MyFile As String
> > Dim FileNum As Integer
> > Dim Filenum1 As Integer
>
> > FileNum = FreeFile
> > Filenum1 = FreeFile
>
> > MyFile = "Q:\DropBox\csv Files\Appended Data.TXT"
>
> > Set ObjFso = CreateObject("Sripting.FileSystemObject")
> > Set objfolder = ObjFso.getfolder("Q:\DropBox\csv Files")
> > Set ColFiles = objfolder.Files
>
> > Open MyFile For Append As #FileNum
>
> > For Each ObjFile In ColFiles
> > * *Open ObjFile For Input As #Filenum1
> > * *Do Until EOF(Filenum1)
> > * * * *Line Input #Filenum1, Data
> > * * * *Print #FileNum, Data
> > * * * *Close #Filenum1
> > * *Loop
> > Next
>
> > End Sub
>
> > I have run this same type of procedure before. *I can't figure out why
> > it's giving me this error now. *Any help would be appreciated.- Hide quoted text -
>
> - Show quoted text -

oh wow. You know what is so bad about that is I retyped it in there -
the wrong way. lol

I have no idea how the shell command works through vba. It looks like
you don't have to do a loop or anything? I have never seen that
before. It's pretty cool. I can do that with any shell commands eh?
How cool...You just gave me something to keep me occupied for the next
several hours...lol

You may want to look at the way Ron de Bruin approached it:
http://www.rondebruin.nl/csv.htm



R Tanner wrote:
>
> Hi,
>
> I'm trying to create a file system object so I can cycle through a
> folder and quickly append 30 different text files into one. This is
> the short macro I have. When I try to run it, it tells me the
> following:
>
> ActiveX component can't create object or return reference to this
> object (Error 429)
>
> Sub Gather()
> Dim ObjFso As New FileSystemObject
> Dim ObjectFolder As Object
> Dim ColFiles As Object
> Dim ObjFile As Object
> Dim T As Integer
> Dim MyFile As String
> Dim FileNum As Integer
> Dim Filenum1 As Integer
>
> FileNum = FreeFile
> Filenum1 = FreeFile
>
> MyFile = "Q:\DropBox\csv Files\Appended Data.TXT"
>
> Set ObjFso = CreateObject("Sripting.FileSystemObject")
> Set objfolder = ObjFso.getfolder("Q:\DropBox\csv Files")
> Set ColFiles = objfolder.Files
>
> Open MyFile For Append As #FileNum
>
> For Each ObjFile In ColFiles
> Open ObjFile For Input As #Filenum1
> Do Until EOF(Filenum1)
> Line Input #Filenum1, Data
> Print #FileNum, Data
> Close #Filenum1
> Loop
> Next
>
> End Sub
>
> I have run this same type of procedure before. I can't figure out why
> it's giving me this error now. Any help would be appreciated.

--

Dave Peterson