Copying contents CSV-XLS

[Yarroll]

Hi,

Could someone please help me with the following macro?
A folder contains lots of .csv files along with corresponding .xls files
(file names are identical, except for the extension). The macro is supposed
to open the xls file, open corresponding csv file, copy contents from csv to
user-defined (Input box) sheet in xls, save and close, then goes to the next
pair.

The trouble seems to be with the line: also marked below:
nam_0 = Left(nam_1, InStr(1, nam_1, ".csv") - 1)
- where Excel says "Invalid procedure call or argument", but I have no clue
why that would be so.

Please help,
Best, Yarroll


Sub Tester3()
Dim nam_0 As String, nam_1 As String, nam_2 As String
Dim plik As Workbook
Dim i As Integer
Dim myVal As Variant

With Application.FileSearch

.NewSearch
.LookIn = "g:\test"
.SearchSubFolders = False
.FileName = "*.csv"

If .Execute() > 0 Then

For i = 1 To .FoundFiles.Count

nam_1 = .FoundFiles(i)
nam_0 = Left(nam_1, InStr(1, nam_1, ".csv") - 1)
nam_2 = nam_0 & ".xls"

If Dir(nam_2) = "" Then
MsgBox ("No file: " & nam_2)
Else
Set plik = Workbooks.Open(nam_2)
myVal = Application.InputBox("Which sheet this time: ")
If myVal <> False Then
With Workbooks.Open(nazwa_1)
.Worksheets(1).Cells.Copy Destination:= _
plik.Worksheets(myVal).Cells
.Close
End With
plik.Save
End If
plik.Close
End If
Next
Else
MsgBox "No files to process"
End If
End With
Set plik = Nothing
End Sub

 

[mudraker]

I steped through your code and it worked ok for me.

2 things that could cause the error message is

nam_1 not being set with a file name

or

if InStr(1, nam_1, ".csv") = 0


which should not happen in your cod

 

[Yarroll]

I steped through your code and it worked ok for me.

2 things that could cause the error message is

nam_1 not being set with a file name

or

if InStr(1, nam_1, ".csv") = 0


which should not happen in your code

Thanks Mudraker. I've been experimenting some more and doing a lot of
thinking...
Finally, I noticed my CSVs have extension in CAPITAL letters.
Excel would rather have them as *.csv.

ROTFLOLLL!

Thanks, best regards. Yarroll