JGttttt


I am trying to get excel to open a .csv file using the get open file method. I want excel to open the file and read the data from the .csv file and put the data into an expense sheet. When I hard code the file using the open filename method, I am able to open the file and read the file to the expense sheet using a Do loop. I am using two counters to cycle through the .csv file and expense sheet. The i counter is reading the data from the .csv file and the j counter is reading through the expense sheet. I am using the Cells = Cells to place the data from the .csv file into the appropriate cells within the expense sheet.When I use the Get open file name method, excel would not read the .csv file and put the data into the expense.sheet . Here is an example of my code:

Sub RunIt()
Dim j As Integer
Dim strFilename As String
' INSERT YOUR CSV FILE ON NEXT LINE
strFilename = Application.GetOpenFilename 'When I type in this line of code, excel will not read the data from the .csv file

'Workbooks.Open Filename:="C:\test.csv" 'When I use this type of code excell will read the data



j = 7 ' expense sheet counter
i = 1 ' input file counter
Do Until Cells(i, 1) = ""
Sheets("Expense").Cells(j, 2) = Cells(i, 1)
Sheets("Expense").Cells(j, 4) = Cells(i, 2)
Sheets("Expense").Cells(j, 9) = Cells(i, 3)
Sheets("Expense").Cells(j, 11) = Cells(i, 4)
Sheets("Expense").Cells(j, 13) = Cells(i, 5)
i = i + 1
j = j + 1
Loop
Sheets("Expense").Activate
End Sub
Sub Clearit()

Workbooks.Open Filename:="C:\test.csv"
j = 7 ' expense sheet counter
i = 1 ' input file (csv) counter
Do Until Cells(i, 1) = ""
Sheets("Expense").Cells(j, 2) = ""
Sheets("Expense").Cells(j, 4) = ""
Sheets("Expense").Cells(j, 9) = ""
Sheets("Expense").Cells(j, 11) = ""
Sheets("Expense").Cells(j, 13) = ""
i = i + 1
j = j + 1
Loop
Sheets("Expense").Activate


End Sub

' NewFN = Application.GetOpenFilename(FileFilter:="Comma delimited Files (*.csv), *.xls", Title:="Please select a file")
' If NewFN = False Then
' ' They pressed Cancel
' MsgBox "Stopping because you did not select a file"
' Exit Sub
' Else

'End If





Re: Hello, I am trying to get a .csv file to open using the get open filename method! I want excel

magicalclick


Sorry, too lazy to look it up. Here is the function I am using. I am using filesystemobject instead. Maybe it can help you a little bit. You need to reference Scripting.Runtime for it to work. Technically Excel has a build-in reader to read any kind of delimiter and text qualifier. I don't think you need to write your own. The only reason I wrote mine is because Excel keeps turing 00123 to 123, which is really bad in the case of zip code.

-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

Public fso As Scripting.FileSystemObject

Private Sub Class_Initialize()
Set fso = New Scripting.FileSystemObject
End Sub

Sub GetDataWithDelimiter(SourcePath As String, DestSheetName As String, _
Optional Delimiter As String = ",", Optional TextQualifier As String = "chr(34)")
If TextQualifier = "chr(34)" Then TextQualifier = Chr(34)

FreshSheet DestSheetName
Sheets(DestSheetName).Cells.NumberFormat = "@"
Dim outFile As Scripting.TextStream, oneLine As String
Set outFile = fso.OpenTextFile(SourcePath, ForReading)
Sheets(DestSheetName).Cells(2, 2).Select
Dim i As Long, j As Long
i = 1
j = 1
Do Until outFile.AtEndOfStream
oneLine = outFile.ReadLine
Do Until oneLine = ""
Cells(i, j).FormulaR1C1 = NextToken(oneLine, Delimiter, TextQualifier)
j = j + 1
Loop
j = 1
i = i + 1
Loop
Sheets(DestSheetName).Cells(1, 1).Select
outFile.Close
End Sub

Sub FreshSheet(name As String)
Dim oldAlerts
oldAlerts = Application.DisplayAlerts

On Error Resume Next
Sheets(1).Select
Application.DisplayAlerts = False
Sheets(name).Delete
Sheets.Add
ActiveSheet.name = name
Range("A1").Select
Application.DisplayAlerts = oldAlerts
On Error GoTo 0
End Sub






Re: Hello, I am trying to get a .csv file to open using the get open filename method! I want excel

magicalclick

Opse, didn't include enough helper functions.

Function Peek(text As String, Optional stopBy As String = ",", Optional TextQualifier As String = "chr(34)") As String
Dim tempText As String
tempText = Left(text, Len(text))
Peek = NextToken(tempText, stopBy, TextQualifier)
End Function

Function NextToken(ByRef text As String, Optional stopBy As String = ",", Optional TextQualifier As String = "chr(34)") As String
If TextQualifier = "chr(34)" Then TextQualifier = Chr(34)

Dim skipStopBy As Boolean
skipStopBy = False
NextToken = ""
Do Until Left(text, Len(stopBy)) = stopBy And Not skipStopBy
If Left(text, Len(TextQualifier)) = TextQualifier Then
If skipStopBy = True Then
skipStopBy = False
Else
skipStopBy = True
End If
End If

If Left(text, Len(stopBy)) = stopBy Then
NextToken = NextToken + Left(text, Len(stopBy))
text = Right(text, Len(text) - Len(stopBy))
Else
NextToken = NextToken + Left(text, 1)
text = Right(text, Len(text) - 1)
End If

If Len(text) < 1 Then GoTo Final_Step
Loop
text = Right(text, Len(text) - 1)
Final_Step:
If Left(NextToken, 1) = TextQualifier And Right(NextToken, 1) = TextQualifier Then NextToken = Mid(NextToken, 2, Len(NextToken) - 2)
End Function






Re: Hello, I am trying to get a .csv file to open using the get open filename method! I want excel

magicalclick

Too much code from me. Ouch.

Anyway, here is the definition,

Displays the standard Open dialog box and gets a file name from the user without actually opening any files.

So, yeah, the file is not opened. It is normal. Why do you want to disable Workbooks.Open That's the code to open up the file.





Re: Hello, I am trying to get a .csv file to open using the get open filename method! I want excel

Mohamed Shifaz

Or you could use a Form with Common Dialog Control(ComDlg32.ocx, which comes with VB6 runtime files).

But the only problem then would be, if a user who does not have this Common Dialog Control then, user will end up with error messages like 'Object not found'.