Re: Locate Word In Column & Copy Adjacent Range
Hi, Jindon
Where is ASCII in the patha bove?
wkurukul
Re: Locate Word In Column & Copy Adjacent Range
Hi, Jindon
Where is ASCII in the patha bove?
wkurukul
Re: Locate Word In Column & Copy Adjacent Range
Tried with ASCII and now the message "No file found" does not come any more, but the code does not introduce blank A3 row and update for 23-May-2012.
Any reason why?
Re: Locate Word In Column & Copy Adjacent Range
Does not accept without inverted commas. Even after that No luck!
Re: Locate Word In Column & Copy Adjacent Range
Now the code should;
1) Read the csv file
2) Loop through all the worksheet
3) If sheet name is found in csv file, add new row at the bottom of its worksheet with corresponding data
Do you see any new line added to the worksheet?
Re: Locate Word In Column & Copy Adjacent Range
Just try
1) Downlaod the files and save them on to your desktop
2) Change .txt to .csv
3) then run the code and see.
Re: Locate Word In Column & Copy Adjacent Range
Hi, jindon
New row should be at the top at ROW A3 not at the bottom??? Data are arranged from Bottom to Top (FBT) not From Top to Bottom (FTB) as seen in the BulkQoutesXL Settings worksheet.
Yes. I checked now. Well done!!! Your code has been adding data at the bottom which is not what I need. It must add Data at the top after creating a new blank row at A3. Please do this correction.
I think your code seems to be the way to go. Please help refine it further.
Kind regards
wkurukul
Re: Locate Word In Column & Copy Adjacent Range
Hi, jindon
If you carefully look at the Master Workbook I uploaded for you, the dates in its Column A are arranged with the most current date at ROW A3. I need to change your code to insert blank Row at A3 and paste the data from CSV file on to the first 5 cells of the new blank row at A3. The following code given by Smallman works well inside you code to insert blank row.
I did this change to your code and it seems to be working. You are a VBA MAESTRO!!! I congratulate you for helping me out. No doubt many others will be able to use such codes for other purposes.
I modified this part of your code
to read as
PLEASE ADVISE IF I NEED TO DO ANY CHANGES!!!
Kind regards
wkurukul
Re: Locate Word In Column & Copy Adjacent Range
Hi, Jindon
The format of the pasted data are in 'text' NOT converted into 'number' format. The date should be in d-mmm-yy format as shown below in the Master Workbook uploaded for your reference. Please help here. Thanks and kind regards - wkurukul
[TABLE="width: 389"]
[TD="align: right"]22-May-12[/TD]
[TD="align: right"]1.25[/TD]
[TD="align: right"]1.25[/TD]
[TD="align: right"]1.23[/TD]
[TD="align: right"]1.25[/TD]
[TD="align: right"]17000[/TD]
[TD="align: right"]21-May-12[/TD]
[TD="align: right"]1.24[/TD]
[TD="align: right"]1.25[/TD]
[TD="align: right"]1.22[/TD]
[TD="align: right"]1.24[/TD]
[TD="align: right"]163400[/TD]
[TD="align: right"]18-May-12[/TD]
[TD="align: right"]1.17[/TD]
[TD="align: right"]1.24[/TD]
[TD="align: right"]1.17[/TD]
[TD="align: right"]1.24[/TD]
[TD="align: right"]241500[/TD]
[TD="align: right"]17-May-12[/TD]
[TD="align: right"]1.20[/TD]
[TD="align: right"]1.21[/TD]
[TD="align: right"]1.17[/TD]
[TD="align: right"]1.17[/TD]
[TD="align: right"]78900[/TD]
[TD="align: right"]16-May-12[/TD]
[TD="align: right"]1.21[/TD]
[TD="align: right"]1.23[/TD]
[TD="align: right"]1.17[/TD]
[TD="align: right"]1.17[/TD]
[TD="align: right"]297100[/TD]
[/TABLE]
Re: Locate Word In Column & Copy Adjacent Range
OK
Just try with the actual csv file
Sub test()
Dim myDir As String, ws As Worksheet
Dim fn As String, txt As String, x, temp, y
Dim maxDate As Date, myDate As Date, msg As String
myDir = CreateObject("WScript.Shell").SpecialFolders("mydocuments") & _
"\EODData\DataClient\ASCII\ASX\"
fn = GetMostCurrentFile(myDir)
If fn = "" Then
MsgBox "No file found"
Exit Sub
End If
txt = CreateObject("Scripting.FileSystemObject") _
.OpenTextFile(fn).ReadAll
For Each ws In Worksheets
x = InStr(1, txt, ws.Name, 1)
If x <> 0 Then
temp = Mid$(txt, x)
temp = Mid$(temp, InStr(temp, ",") + 1)
y = Split(Split(temp, vbCrLf)(0), ",")
With ws
maxDate = Application.Max(.Columns(1))
myDate = DateValue(Split(y(0), "-")(2) & "/" _
& Split(y(0), "-")(1) & "/" & Split(y(0), "-")(0))
If myDate > maxDate Then
.Rows(3).Insert
With .Range("a3").Resize(, UBound(y) + 1)
.Value = y
.Value = .Value
On Error Resume Next
.SpecialCells(4).Value = 0
On Error GoTo 0
End With
Else
msg = msg & vbLf & ws.Name & " has been already updated on " & maxDate
End If
End With
End If
Next
If Len(msg) Then MsgBox msg
End Sub
Function GetMostCurrentFile(myDir As String) As String
Dim fn As String, AL As Object
fn = Dir(myDir & "*.csv")
If fn = "" Then Exit Function
Set AL = CreateObject("System.Collections.ArrayList")
Do While fn <> ""
AL.Add fn
fn = Dir
Loop
AL.Sort
AL.Reverse
GetMostCurrentFile = myDir & AL(0)
Set AL = Nothing
End Function
Function IsSheetExists(ByVal sn As String) As Boolean
On Error Resume Next
IsSheetExists = Len(Sheets(sn).Name)
On Error GoTo 0
End Function
Display More
Re: Locate Word In Column & Copy Adjacent Range
OK
Run the code again and when it stops, can you read y(0)?
When it stops, click on Debug, and if you put the cursor on y(0), don't click, you will see pop up.
Re: Locate Word In Column & Copy Adjacent Range
Hi, jindon
y(0) = "25 May 2012"
Re: Locate Word In Column & Copy Adjacent Range
OK
In your posted csv file, the date is like "23-May-12".
Do you have both type of date mixed?
Re: Locate Word In Column & Copy Adjacent Range
Hi, jindon
I am more interested in an effcient code that works without message boxes.
I update data almost everyday for the stocks I trade in, do not do so for stocks that I dont trade but intend to trade.
Kind regards
wkurukul
Re: Locate Word In Column & Copy Adjacent Range
Hi, Jindon
Everyday, s new data file is dowbloaded from EODData website to ASX folder I have already downloaded up to 25th Friday for this week and my Master Workbooks are all updated up to 24-May-12.
Cheers
wkurukul
Re: Locate Word In Column & Copy Adjacent Range
I asked
Quote from jindon;610351OK
In your posted csv file, the date is like "23-May-12".
Do you have both type of date mixed?
Re: Locate Word In Column & Copy Adjacent Range
Each csv file has only one date in column B
Re: Locate Word In Column & Copy Adjacent Range
You found
Quote from wkurukul;610350Hi, jindon
y(0) = "25 May 2012"
But I found 23-May-12 in you posted csv file.
Do you have both types of date?
Don’t have an account yet? Register yourself now and be a part of our community!