macro issue

[ruudbo]

I'm using this macro to import multiple csv files into multiple sheets within one excel (2007) workbook. The problem is when I update the csv files and run the macro with the updated csv's, it adds more worksheets. How do I tweek the macro to relace the existing imported csv worksheet....

Sub Example12()
Dim MyPath As String
Dim FilesInPath As String
Dim MyFiles() As String
Dim SourceRcount As Long
Dim Fnum As Long
Dim mybook As Workbook
Dim basebook As Workbook
'Fill in the path\folder where the files are
'on your machine
MyPath = "c:\Data"
'Add a slash at the end if the user forget it
If Right(MyPath, 1) <> "\" Then
MyPath = MyPath & "\"
End If
'If there are no Excel files in the folder exit the sub
FilesInPath = Dir(MyPath & "*.csv")
If FilesInPath = "" Then
MsgBox "No files found"
Exit Sub
End If
On Error GoTo CleanUp
Application.ScreenUpdating = False
Set basebook = ThisWorkbook
'Fill the array(myFiles)with the list of Excel files in the folder
Fnum = 0
Do While FilesInPath <> ""
Fnum = Fnum + 1
ReDim Preserve MyFiles(1 To Fnum)
MyFiles(Fnum) = FilesInPath
FilesInPath = Dir()
Loop
'Loop through all files in the array(myFiles)
If Fnum > 0 Then
For Fnum = LBound(MyFiles) To UBound(MyFiles)
Set mybook = Workbooks.Open(MyPath & MyFiles(Fnum))
mybook.Worksheets(1).Copy after:= _
basebook.Sheets(basebook.Sheets.Count)
On Error Resume Next
ActiveSheet.Name = mybook.Name
On Error GoTo 0
' You can use this if you want to copy only the values
' With ActiveSheet.UsedRange
' .Value = .Value
' End With
mybook.Close savechanges:=False
Next Fnum
End If
CleanUp:
Application.ScreenUpdating = True
End Sub

 

[ruudbo]

in other words...how do i add or change the macro to import the updated csv files and replace the existing worksheets, rather than create new worksheets????

 

[njimack]

Rather than copying the worksheet across, just copy the used range and paste over one of your existing sheets. Something like this (untested)...

mybook.Worksheets(1).UsedRange.Copy Destination:= basebook.Worksheets(1).Range("A1")

 

[ruudbo]

i tried inserting into the macro and it didnt work....how do i incorporate this into the existing macro (see above)

 

[njimack]

Replace this:

mybook.Worksheets(1).Copy after:= _
basebook.Sheets(basebook.Sheets.Count)
On Error Resume Next
ActiveSheet.Name = mybook.Name
On Error GoTo 0
' You can use this if you want to copy only the values
' With ActiveSheet.UsedRange
' .Value = .Value
' End With

with the code I posted earlier. You'll need to change the sheet reference, depending on which sheet you want the data pasted to.

Also, if the number of rows varies, you should delete the content of the destination sheet before copying & pasting

 

[ruudbo]

i appreciate your help, but when i performed the cut n paste within the macro and re-ran it, nothing happens....didnt even copy additional imported worksheets...so my issue is still unresolved..

 

[ruudbo]

actually when i sifted through the workbook...all that the updated macro did is copy pasted all the data into worksheet 1....

 

[njimack]

actually when i sifted through the workbook...all that the updated macro did is copy pasted all the data into worksheet 1....

That's what it's supposed to do - paste the data rather than copy the entire sheet.

Alternatively, just delete your existing sheet(s) that you no longer need, e.g

basebook.Worksheets(1).Delete