Excel VBA macro to auto import new CSV file from fixed directory

[windslayer]

Hi Dear Excel Experts;
I need help with my excel macro from all of expert;
I need to automate data import in excel where:

I recieve a CSV file from production updated daily which i save it i
the computer shared drive.

HOW TO MAKE the TEXT IMPORT AUTOMATED DAILY IF THERE IS NEW FILE ADDE
INTO THE SAME FOLDER LOCATION?

- each file saved by Name: Date&Time_Lot No1_Lot No2_machine name_Ru
No_Process.csv
• ( like: 120502141657_PHC4#_A_PHC4G_A_IBE001_0785_Shallow.csv,
120603215613_PJ25N_A_P5K2#_C_IBE001_0824_Deep.csv)
- file is in csv/Comma delimiated format
- each file have 14columns A to O and contain 30rows
- i only need data from 11 to 30 rows every time
- the rows i need are going to be same each time
-I need a macro to allow the csv file to be automated/import daily / a
least if I press a refresh button

 

[Auric__]

Hi Dear Excel Experts;
I need help with my excel macro from all of expert;
I need to automate data import in excel where:

I recieve a CSV file from production updated daily which i save it in
the computer shared drive.

HOW TO MAKE the TEXT IMPORT AUTOMATED DAILY IF THERE IS NEW FILE ADDED
INTO THE SAME FOLDER LOCATION?

- each file saved by Name: Date&Time_Lot No1_Lot No2_machine name_Run
No_Process.csv
• ( like: 120502141657_PHC4#_A_PHC4G_A_IBE001_0785_Shallow.csv,
120603215613_PJ25N_A_P5K2#_C_IBE001_0824_Deep.csv)
- file is in csv/Comma delimiated format
- each file have 14columns A to O and contain 30rows
- i only need data from 11 to 30 rows every time
- the rows i need are going to be same each time
-I need a macro to allow the csv file to be automated/import daily / at
least if I press a refresh button
------------------------------------------------------------------------- -
I want to save automated data in a file name: masterdata.xls
Fore each ROW data should be saved in new row below to last saved
column.

Something like this, perhaps? (This assumes that masterdata.xls is already
open and is the active workbook. Not the best way to do this, I'm sure, but
it works for me...)

Sub importNewCSVs()
Dim done As String, e As String, e1 As String, f As String
Dim tmp As String, t2 As Variant, t3 As Variant
prevdir = CurDir
'Edit to fit:
ChDir "path\to\CSV\files"
e = "already_imported.txt"
e1 = Dir(e)
If Len(e1) Then
Open e For Binary As 1
done = Space$(LOF(1))
Get #1, 1, done
Close
End If
Open e For Append As 1
If Len(done) < 1 Then Print #1,
f = Dir("*.csv")
While Len(f)
If InStr(done, vbNewLine & f & vbNewLine) < 1 Then
Open f For Binary As 2
tmp = Space$(LOF(2))
Get #2, 1, tmp
t2 = Split(tmp, vbNewLine)
For L0 = 10 To 29
t3 = Split(t2(L0), ",")
'The ", 1" needs to be altered to point at the correct column:
Cells(Cells.SpecialCells(xlCellTypeLastCell).Row + 1, 1).Select
For L1 = 0 To UBound(t3)
ActiveCell.Value = t3(L1)
ActiveCell.Offset(0, 1).Select
Next
Next
Close 2
Print #1, f
End If
f = Dir
Wend
Close
ChDir prevdir
End Sub