Error 424 - Copy and paste b/w worksheets

[Benji Price]

Hi all,

first post here. I am a complete VBA newbie (never even opened Visual Basic until 2 weeks ago) but now I need it for my research. I looked up this problem on this forum and on Google but couldn't find any solutions.
What I need (at this stage,at least) is quite simple - copy the content of a given cell from all .csv files within a folder, and paste it in the first blank row of the master workbook.
But the loop stops after the first paste occurs, with a 424 error. The same error occurs if I try a simple copy&paste from only one .csv workbook onto the main workbook.

Below is the code that I managed to put up until now. The error always occurs at: dWbs.Range("C" & BlankRow).PasteSpecial.xlPasteValues
Thanks in advance!


Sub Test()'Optimize speed
With Application
.ScreenUpdating = False
.EnableEvents = False
.DisplayAlerts = False
End With


'General preparations
Dim dWbk, sWbk As Workbook
Dim dWbs As Worksheet
Dim Filename, Path As String
Set dWbk = ThisWorkbook
Set dWbs = dWbk.Worksheets(1)
'For last row
Dim BlankRow As Long


'For path selection
Path = "C:\Users\Documents\Università\Bilanci\"
Filename = Dir(Path & "*.csv")


Do While Len(Filename) > 0
Set sWbk = Workbooks.Open(Path & Filename, ReadOnly:=True, Local:=True)
sWbk.Activate
sWbk.Worksheets(1).Range("B3").Copy
sWbk.Close False
dWbk.Activate
BlankRow = dWbk.Worksheets(1).Cells(Rows.Count, 3).End(xlUp).Row + 1
dWbs.Range("C" & BlankRow).PasteSpecial.xlPasteValues
Application.CutCopyMode = False


Filename = Dir
Loop




With Application
.ScreenUpdating = True
.EnableEvents = True
.DisplayAlerts = True
End With


End Sub

[Paul_Hossler]

Welcome to the best Excel forum

For a first effort that is very good, well structured code

Something like this

The Paste:=xlPasteValues is called a named parameter and is one way to call a method or sub. The other is something like SubName(parm1, parm2, parm3). Note the colon equals.




Range("A1").Copy
Range("E6").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False




Also in VBA, each variable that you Dim needs the 'As ...' otherwise it's assumed to be to be Variant



Dim dWbk As Workbook, sWbk As Workbook
Dim dWbs As Worksheet
Dim Filename as String, Path As String



Also 2 -- 'Filename' and 'Part' are also VBA keywords. I find it less error prone to use something descriptive: FileNameToRead, PathToReadFrom, etc.

[Benji Price]

Hi Paul! Thanks for the quick reply, and the advice concerning declaring all variables.

I tried modifying that piece of code as you suggested,and after moving sWbk.Close False after having pasted it (before it simply wouldn't close the source worksheet),now it works! Thanks a lot!

If anyone is interested,this is the new code

Do While Len(MyFilename) > 0
Set sWbk = Workbooks.Open(MyPath & MyFilename, ReadOnly:=True, Local:=True)
sWbk.Activate
sWbk.Worksheets(1).Range("B3").Copy
dWbk.Activate
BlankRow = dWbk.Worksheets(1).Cells(Rows.Count, 3).End(xlUp).Row + 1
dWbs.Range("C" & BlankRow).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False
sWbk.Close False

MyFilename = Dir
Loop

[snb]

Please do not Quote !

An alternative approach:


sub M_snb()
sn=split(createobject("wscript.shell").exec("cmd /c Dir C:\Users\Documents\Università\Bilanci\*.csv /b").stdout.readall,vbcrlf)

for j=0 to ubound(sn)-1
with getobject(c00 & sn(j))
sn(j)=.sheets(1).cells(3,2).value
.close 0
end with
next

thisworkbook.sheets(1).cells(1).resize(ubound(sn))=application.transpose(sn )
End Sub

[p45cal]

Replace the dot here:
PasteSpecial.xlPasteValues
with a space:
PasteSpecial xlPasteValues

[p45cal]

snb,
For j = 0 To UBound(sn) - 1

[snb]

@p45cal:

Quite right ! ;)

[Benji Price]

Thanks for your suggestions as well :)

Now I have a different question,still on the same code.

I want to copy data from files with the same name (say entrate.csv) but in different folders. I thought I could simply insert a "*" for the folder into Dir() but it's not working.
More specifically,the path I have is: "C:\Users\Documents\Università\Dati PA\Bilanci\City\2004\preventivo\01\entrate.csv", and I want to loop through all the year folders, and then copy from the "entrate.csv" files.

This particular problem could be solved by hand,but I'm just trying to understand how to loop between different folders for a given filename and a given (partial) path,since I need to do it for a number of files AND a number of both cities and years.

I tried this, but the code runs with no error, and does nothing.


Sub Test()'Optimize speed
With Application
.ScreenUpdating = False
.EnableEvents = False
.DisplayAlerts = False
End With
'General preparations
Dim dWbk As Workbook
Dim sWbk As Workbook
Dim dWbs As Worksheet
Dim MyPathName As String
Dim MainPath As String
Dim FinPath As String
Dim PathSeek As String
Set dWbk = ThisWorkbook
Set dWbs = dWbk.Worksheets(1)
'For last row
Dim BlankRow As Long


'For path selection
MainPath = "C:\Users\Documents\Università\Dati PA\Bilanci\City\"
FinPath = "\preventivo\01\entrate.csv"
PathSeek = MainPath & "****"
MyPathName = Dir(PathSeek, vbDirectory)




Do While Len(MyFilename) > 0
Set sWbk = Workbooks.Open(MainPath & MyPathName & FinPath, ReadOnly:=True, Local:=True)
sWbk.Activate
sWbk.Worksheets(1).Range("B3").Copy


dWbk.Activate
BlankRow = dWbk.Worksheets(1).Cells(Rows.Count, 3).End(xlUp).Row + 1
dWbs.Range("C" & BlankRow).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False
sWbk.Close False
MyFilename = Dir
Loop








With Application
.ScreenUpdating = True
.EnableEvents = True
.DisplayAlerts = True
End With




End Sub

[snb]

Sub M_snb()
sn=split(createobject("wscript.shell").exec("cmd /c Dir ""C:\Users\Documents\Università\Dati PA\Bilanci\city\entrate.csv"" /b/s").stdout.readall,vbcrlf)

For j=0 To ubound(sn)-1
With getobject(sn(j))
sn(j)=sn(j) & "_" & .sheets(1).cells(3,2).value
.close 0
End With
Next

thisworkbook.sheets(1).cells(1).resize(ubound(sn))=application.transpose(sn )
End Sub

[Benji Price]

Hi snb,thanks for the quick reply.
I have one little problem with the code you posted - I don't really know what it means/does :D and so I can't figure out where to place it exactly in my code,and how eventually to modify it slightly.
Could you explain it very briefly please?

[snb]

1. make an inventory of all 'entrate.csv' files in directory...\city\ and all its subdirectories
2. put the inventory in array sn
3. open each file from the inventory
4. read the value in cell B3
5. write that value into array sn
6. close the file
7. write the array sn into the first sheet of the workbook that also contains this macro.

[Benji Price]

Ok,thanks,now it's much clearer.
Still,I can't get it to work. It gives me Error 13 at

sn(j) = sn(j) & "_" & .Sheets(1).Cells(3, 2).Value
I've defined j As Integer, and sn As Variant (and also tried sn() As String).

[snb]

Please do not declare any variable.


Sub M_snb()
sn=split(createobject("wscript.shell").exec("cmd /c Dir ""C:\Users\Documents\Università\Dati PA\Bilanci\city\entrate.csv"" /b/s").stdout.readall,vbcrlf)

For j=0 To ubound(sn)-1
sn(j)=split(split(createobject("scripting.filesystemobject").opentextfile(sn(j)).readall,vbcrlf)(2),",")(1)
Next

thisworkbook.sheets(1).cells(1).resize(ubound(sn))=application.transpose(sn )
End Sub

Do your csv files contain comma or semicolon ; as fieldseparator ?

[Benji Price]

Oh,allright,no declaring variables then.
I tried this new code and still obtain Error 13.

Since the csv files contain semicolon as fieldseparator,I also tried to run

for j = 0 To UBound(sn) - 1
sn(j) = Split(Split(CreateObject("scripting.filesystemobject").opentextfile(sn(j)).readall, vbCrLf)(2), ";")(1)
Next
but to no avail - still Error 13.

[snb]

what message do you get ?


Sub M_snb()
sn=split(createobject("wscript.shell").exec("cmd /c Dir ""C:\Users\Documents\Università\Dati PA\Bilanci\city\entrate.csv"" /b/s").stdout.readall,vbcrlf)

For j=0 To ubound(sn)-1
msgbox ubound(sn) & "__" & sn(j)
sn(j)=split(split(createobject("scripting.filesystemobject").opentextfile(sn(j)).readall,vbcrlf)(2),",")(1)
Next

thisworkbook.sheets(1).cells(1).resize(ubound(sn))=application.transpose(sn )
End Sub

Please post a sample csv file here.

[Benji Price]

Ok, with this last piece of code you posted (and after changing slightly the directory's name,since it also contained an Italian character not recognized by VBA),it all works. Thanks a lot.

Now the script is running properly and giving me the variables I wanted. Only, the numbers (which are in the B column of the .csv files) that are copied in the master workbook have a " at their beginning and end, i.e. " "2390.00" ".
The csv files are very basic and at a first look their formatting is standard; with the code in the OP I managed to capture only the number by using Workbooks.Open(... Local:=True).

[snb]

In this case you should avoid 'workbooks.open'.


Sub M_snb()
sn=split(createobject("wscript.shell").exec("cmd /c Dir ""C:\Users\Documents\Università\Dati PA\Bilanci\city\entrate.csv"" /b/s").stdout.readall,vbcrlf)

For j=0 To ubound(sn)-1
sn(j)=replace(split(split(createobject("scripting.filesystemobject").opentextfile(sn(j)).readall,vbcrlf)(2),",")(1),chr(43),"")
Next

thisworkbook.sheets(1).cells(1).resize(ubound(sn))=application.transpose(sn )
End Sub

[Benji Price]

This helped,thanks!

One issue though (again,I know...): I have something like 80,000 values, but after the 25,000th all others show #N/A,even though there should be no reason for it...is it because I am using too much data in a single workbook?