Solved: Help opening csv files in Excel

[Ryu]

Hi everyone,

This is a really simple problem, on a daily basis I save 8 CSV files to a folder. (In this case imagine me creating a test folder in my documents and in the test folder I create my daily folders e.g 20080928, 20080929 and 20080930)

I was hoping would it be possible for me to save all 8 csv files in the daily folder and on my desktop to run an excel file, when I run the macro on that excel file a pop up window to appear for me to enter a folder name (the daily folder name) - when I have entered the folder name I want to only open up three selected csv files.

Is this possible? One big problem Im going to face is the three csv files I need to open are named as Report_A_20080930.csv, Report_B_20080930.csv and Report_C_20080930.csv - so the end date will change very day so could I open them using only the first 8 characters? (So open up Report_A Report_B and Report_C) - its ok as the other 5 csv files are not named report.

So far I have the following:



Option Explicit
Sub opencsv()
strfolder = "C:\Documents and Settings\jglasgow\My Documents\test\"
strfile = Dir(strfolder & "report_a*.*")
Do While Len(strfile) > 0
Open strfolder & strfile For Input As 1
'read file and process
Close 1
Loop
strfile = Dir(strfolder & "report_b*.*")
Do While Len(strfile) > 0
Open strfolder & strfile For Input As 2
'read file and process
Close 2
Loop
strfile = Dir(strfolder & "report_c*.*")
Do While Len(strfile) > 0
Open strfolder & strfile For Input As 1
'read file and process
Close 3
Loop
MsgBox "filename:= " & strfile
Open strfolder & strfile For Input As 1
myarr = Split(Input(LOF(1), #1), vbNewLine)
Close
End Sub


Any help please?

[Bob Phillips]

Not really sure where the 8 files comes from, nor the 3, but this might give you some ideas



Dim Folder As String

Folder = InputBox("What name for the folder")
If Folder <> "" Then

strfolder = "C:\Documents and Settings\jglasgow\My Documents\test\"
MkDir strfolder & Application.PathSeparator & Folder
strfile = strfolder & "report_a" & Format(Date, "yyyymmdd") & ".csv"
Open strfolder & strfile For Input As 1
'read file and process
Close 1
strfile = strfolder & "report_b_" & Format(Date, "yyyymmdd") & ".csv"
Open strfolder & strfile For Input As 2
'read file and process
Close 2
strfile = strfolder & "report_c" & Format(Date, "yyyymmdd") & ".csv"
Open strfolder & strfile For Input As 1
'read file and process
Close 3

[Ryu]

Thanks buddy, I tried your coding and it does work partially.

What I did was create a folder inside the test folder and named it Sep, now when I run your coding it and I enter sep it says the following:

Path/File Access Error

When I enter anything else other than Sep then it says Bad file name or number which tells me that it can read the folder but it can't read the csv files.

The coding is as follows:



Sub opencsv()
Dim Folder As String

Folder = InputBox("name blah blah")
If Folder <> "" Then

strfolder = "C:\Documents and Settings\jglasgow\My Documents\test\"
MkDir strfolder & Application.PathSeparator & Folder
strfile = strfolder & "report_a_" & Format(Date, "yyyymmdd") & ".csv"
Open strfolder & strfile For Input As 1
'read file and process
Close 1
strfile = strfolder & "report_b_" & Format(Date, "yyyymmdd") & ".csv"
Open strfolder & strfile For Input As 2
'read file and process
Close 2
strfile = strfolder & "report_c_" & Format(Date, "yyyymmdd") & ".csv"
Open strfolder & strfile For Input As 1
'read file and process
Close 3
End If
End Sub


The 8 csv files get emailed to me on a daily basis and I have to save them in to a folder, however out of these 8 I only really need to open the three csv files.

Thanks

[MaximS]

try this:

see attached file for details.

All you need to adjust is folowing code:


myDir = "C:\Documents and Settings\UserName\My Documents\" & _
myDate & "\"


located in UserForm1


BTW. Ryu - you cannot name the folder sep it needs to be in format yyyymmdd otherwise macro won't know the file name which hase the same date in name.

[Ryu]

Excellent that works a treat.

Could I run my additional coding on all 3 of these csv files?

So basically when the correct date has been entered and the three csv files open I want my other coding to run - one more thing how difficult would it be to open the three csv files in different worksheets? on my main excel file. For example Report A replace worksheet1, Report B replace worksheet 2 and Report C replace worksheet 3 - the csv files only have 1 worksheet each. Thanks

[MaximS]

try now revieved file :)

do you remember the format of your cvs files?? are they delimited??

[Ryu]

Hi Maxims

Thanks you soo much, everything is working perfectly - just one final thing before I can close this thread.

When all the data has been copied over from the three different reports I want to run some coding on the three worksheets - however the problem Im facing is when the three reports have been copied over the coding that I have in place freezes my excel for about 3/5 minutes and when it finishes freezing the coding does whats it's suppose to.

The coding is as follows:



Private Sub CommandButton1_Click()
Dim myDir, myPath, myDate As String
Dim myFile(2) As Variant
Dim LCol, LRow As Long
Dim i As Long
Dim cCell As Range
Dim cRange As Range
Dim Lastrow As Long
Dim InUsing As Long
Dim StrUsing As String
Dim bnlFoundInvalid As Boolean
Dim bnlFoundValid As Boolean
Dim intCounter As Integer
Dim intColValid As Integer
Dim intColInValid As Integer
Dim lngPosn As Long
Dim rngH As Range
Dim rng As Range
Dim rngToCheck As Range
Dim wst As Worksheet

myDate = TextBox1.Text
myDir = "C:\Documents and Settings\jglasgow\My Documents\test\" & _
myDate & "\"
myFile(0) = "Report_A_" & myDate & ".csv"
myFile(1) = "Report_B_" & myDate & ".csv"
myFile(2) = "Report_C_" & myDate & ".csv"
For i = 0 To 2
myPath = myDir & myFile(i)

If File_Exists(myPath) Then

ChDir myDir
Workbooks.OpenText Filename:=myPath
'Selecting and copying all
Range("A1").Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy

'Pasting into report
Windows("repopener.xls").Activate
Sheets(i + 1).Select
Range("A1").Select
ActiveSheet.Paste
Application.CutCopyMode = False
'Closing csv file
Windows(myFile(i)).Activate
ActiveWindow.Close
Else
MsgBox myFile(i) & " file does not exist"
End If
Next i

For Each wst In Worksheets
'Cols H to M
For i = 8 To 13

'Find last row of data Current Column
Lastrow = Cells(65536, i).End(xlUp).Row

'Set as range
Set cRange = Range(Cells(1, i), Cells(Lastrow, i))
'Do all
For Each cCell In cRange
'Lose "Hyperi"
If UCase(Left(cCell, 7)) = "Hyperi" Then
'Space between "HYPERI and ...some text...
cCell.Value = Right(cCell, Len(cCell) - 7)
ElseIf UCase(Left(cCell, 6)) = "Hyperi" Then
'No space between "HYPERIand ...some text...
cCell.Value = Right(cCell, Len(cCell) - 6)
End If
'Find " Using" (space before)
StrUsing = UCase(cCell)
InUsing = InStr(StrUsing, " USING")
'Delete if found
If InUsing > 0 Then
cCell.Value = Left(cCell, InUsing - 1)
Else
'Find "Using" (no space before)
InUsing = InStr(StrUsing, "USING")
If InUsing > 0 Then
cCell.Value = Left(cCell, InUsing - 1)
End If
End If
'Next row
Next cCell
'Next col
Next i
Set rngH = Range("H1:H2000")
For Each rng In rngH
bnlFoundInvalid = False
bnlFoundValid = False
For intCounter = 0 To 5
If InStr(1, rng.Offset(0, intCounter).Value, "fistype", vbTextCompare) > 0 Or _
IsNumeric(rng.Offset(0, intCounter).Value) Then
If bnlFoundInvalid = False Then
'An invalid column.
intColInValid = intCounter
End If
bnlFoundInvalid = True
rng.Offset(0, intCounter).ClearContents
Else
'1st valid column.
If bnlFoundValid = False Then
intColValid = intCounter
End If
bnlFoundValid = True
End If
If bnlFoundInvalid = True Then
If bnlFoundValid = True Then
If intColValid > 0 Then
'Col H had an invalid value.
rng.Value = rng.Offset(0, intColValid).Value
End If
End If
End If
Next intCounter
Next rng
For Each rng In rngH
For intCounter = 1 To 5
If rng.Value = rng.Offset(0, intCounter).Value Then
rng.Offset(0, intCounter).ClearContents
End If
Next intCounter
Next rng
'get a reference to the range in columns I to L
Set rngToCheck = wst.Range("I:M")
'if there are no empty cells then an error will be thrown
On Error Resume Next
'delete any empty cells, shift to left
rngToCheck.SpecialCells(xlCellTypeBlanks).Delete xlShiftToLeft
'resume normal error handling
On Error GoTo 0
Next wst
End Sub


Is there anything that is making my program slow? Sorry Iknow my coding skills are not up to scrach.

Thanks

[RonMcK]

Ryu,

Insert the following near the top of your code, just below the DIMs:Application.Calculation = xlCalculateManual and, at the bottom of the code just above the End Sub, insert:Application.Calculation = xlCalculateAutomatic
It's a matter of personal style, however, I would also add code to save a copy of my new workbook.

Cheers,

[Ryu]

Hi Ron mck

I inserted the above coding as requested however it broke on the first line with the following error



Run-time error '1004'
Method 'Calculationof object '_Application' failed


I know what you mean regarding saving my workbook and I'm going to look into it later on.

[RonMcK]

Ryu,

My bad. Those values should be xlCalculationManual and xlCalculationAutomatic.

Please correct the spelling.

Sorry adding a problem.

[Ryu]

Thanks RonMCK

I tried adding the coding as you suggested but the original problem happened again, it seems like the Open Selected File windowfreezes with the 'Not Responding' at the top then after a few minutes it becomes ok and runs the macro.

It's really bizzare if you ask me, thanks.

[RonMcK]

Ryu,

What other processes and programs is your PC running at the same time? I see this behavior from time to time on my machine.

Cheers,

[Ryu]

All I have running are a few internet explorer windows and outlook running at the same time.

However do you want me to close down all the other applications and then try again to see if that helps?

[RonMcK]

Ryu,

No, as I think about it, it's probably just that your program is very busy.

Still pondering,

(p.s. I'm off to lunch)

[Ryu]

To be honest it's not that much of a problem and when I come to think about it it's fine - When I started this thread I'd have given an arm and a leg to be in the position I'm currently in.

I'm prepared to wait 2/3 minutes for the macro to show the three csv files and to filter all of the data and show only the data I want.

I might just close this thread unless you/anyone else has more ideas.

Thanks a million xld, Maxims and RonMck - all your help has been muich appreciated.

[Ryu]

Hi guys,

**EDIT**

Ive got down to the root of the problem, the problem is that when I use MaximS excel sheet it only copies cells from A to H - now my sheets have data from A to M

Any idea to why it only copies the first 8 columns?

Thanks people.

[MaximS]

have you got all headders in columns A-M or just in A-H??

[Ryu]

Hiya

Report A has a header on Column H no headers on Columns I and J
Report B has a header on Column H, I and J
Report C has a header on Column H no headers on Columns I, J, K, L and M.

Thanks

[Slyboots]

Try using:

Range("A1").CurrentRegion

instead. When you do an End-Right, the last cell with an entry is selected. Because Report A doesn't have headers in Cols I and J, it won't pick them up.

[Slyboots]

One more thing. You may find that Workbooks.Open is faster than Workbooks.OpenText.

[Ryu]

Hi Sly,

Where would I put that coding?

Would i add that line or would I replace some previous coding? Thanks

[MaximS]

try replacing that:
Range("A1").Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select

with:
Range("A1").CurrentRegion

and that:
ChDir myDir
Workbooks.OpenText Filename:=myPath

with:
ChDir myDir
Workbooks.Open Filename:=myPath

if for some reasons that want work you can come back to the old version with added extra headers.

[Ryu]

Hi,

It broke on the Range("A1").CurrentRegion line with the following error



Compile Error:
Invalid use of property


Thanks

[Ryu]

Problem solved - it was due to the header.

Thanks buddy.