PDA

View Full Version : [SOLVED] Importing CVS files in to a worksheet



mduff
12-09-2013, 09:27 PM
Hi I found this code on an an other board and it was adapted from Ron de Bruin (http://www.rondebruin.nl/win/s3/win022.htm) page but I am not good enough to get it to what I need.

This code works perfectly the first time but on subsequent runs, new tabs are added after exiting tabs -- I would like to overwrite existing tabs when re-importing the same csv file. Also if possible instead of a fixed path if it could prompt the user to browse for the location of the CSV files, and lastly if not asking too much if it could check the names of the imported CVS files vs a set list of names and error out if the user is trying to import a file that is not in the list.






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:\Users\8114535\Documents\eWFM Exports\12.8 IDps"


'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




thanks so much for your help

mikerickson
12-09-2013, 11:12 PM
Perhaps this will work for you.

Sub test()
Dim uiValues As Variant
Dim uiFilePaths As Variant, oneFilePath As Variant, temp As Variant
Dim oneSheetName As String
Dim Flag As Boolean

#If Mac Then
uiValues = "False"
On Error Resume Next
uiValues = MacScript("choose file with multiple selections allowed")
Rem returns comma delimited string of file paths
'e.g. "Macintosh HD:Users:merickson:Desktop:Test2.csv, Macintosh HD:Users:merickson:Desktop:TestFileOne.csv"
On Error GoTo 0

uiFilePaths = Split(uiValues, ",")
#Else
Rem code for windows
uiValues = Application.GetOpenFilename(MultiSelect:=True)
uiFilePaths = uiValues
Rem debugging point alpha ******
#End If

For Each oneFilePath In uiFilePaths
temp = Split(oneFilePath, Application.PathSeparator)
oneSheetName = Split(temp(UBound(temp)), ".")(0)

Flag = SheetExists(oneSheetName, ThisWorkbook)

If Not Flag Then
If MsgBox("There is no sheet " & oneSheetName & "." & vbCr & vbCr & "Add that sheet?", vbYesNo) = vbYes Then
With ThisWorkbook
.Worksheets.Add(after:=.Sheets(.Sheets.Count)).Name = oneSheetName
End With
Flag = True
Else
Flag = False
End If
End If

If Flag Then
Rem open file
On Error Resume Next
With Application.Workbooks.Open(Filename:=Trim(oneFilePath))
With .Worksheets(1)
Range(.Cells(1, 1), .UsedRange).Copy Destination:=ThisWorkbook.Worksheets(oneSheetName).Range("A1")
End With
.Close savechanges:=False
End With
On Error GoTo 0
End If
Next oneFilePath
End Sub

Function SheetExists(SheetsName As String, Optional wb As Workbook) As Boolean
If wb Is Nothing Then Set wb = ThisWorkbook
With wb
On Error Resume Next
SheetExists = (wb.Sheets(SheetsName).Name = SheetsName)
On Error GoTo 0
End With
End Function

mduff
12-10-2013, 08:46 AM
Thanks a lot that works perfectly:beerchug::beerchug:

mikerickson
12-10-2013, 01:04 PM
I'm curious, are you using a Mac or a Windows machine?
The file dialog box is different and I had to use conditional compiling. I was wondering if I guessed the Windows format correctly.