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

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

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.c sv,
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.
.
Example:
data from file 120502141657_PHC4# should be saved in column B row 6-25,
data from file 120603215613_PJ25N_A should be save in column B row 26,
1206703215613_PJ2XN_C should be saved in column B and Row 46 onward.


I have recorded a macro to import the data to excel via excel Text Import Wizard.


--------------------------------------------------------------------------
Sub Button1_Click()
'
' Button1_Click Macro
'

'
Range("B6").Select
With ActiveSheet.QueryTables.Add(Connection:= _
"TEXT;H:\WYKO\120502141657_PHC4#_A_PHC4G_A_IBE001_ 0785_Shallow.csv", _
Destination:=Range("$B$6"))
.Name = "120502141657_PHC4#_A_PHC4G_A_IBE001_0785_Shal low"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.TextFilePromptOnRefresh = False
.TextFilePlatform = 936
.TextFileStartRow = 11
.TextFileParseType = xlDelimited
.TextFileTextQualifier = xlTextQualifierDoubleQuote
.TextFileConsecutiveDelimiter = True
.TextFileTabDelimiter = True
.TextFileSemicolonDelimiter = True
.TextFileCommaDelimiter = True
.TextFileSpaceDelimiter = True
.TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1)
.TextFileTrailingMinusNumbers = True
.Refresh BackgroundQuery:=False
End With
ActiveWindow.SmallScroll Down:=3
Range("B26").Select
ActiveWindow.SmallScroll Down:=-12
With ActiveSheet.QueryTables.Add(Connection:= _
"TEXT;H:\WYKO\120503215613_PJ25N_A_P5K2#_C_IBE001_ 0824_Deep.csv", Destination _
:=Range("$B$26"))
.Name = "120503215613_PJ25N_A_P5K2#_C_IBE001_0824_Deep "
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.TextFilePromptOnRefresh = False
.TextFilePlatform = 936
.TextFileStartRow = 11
.TextFileParseType = xlDelimited
.TextFileTextQualifier = xlTextQualifierDoubleQuote
.TextFileConsecutiveDelimiter = True
.TextFileTabDelimiter = True
.TextFileSemicolonDelimiter = True
.TextFileCommaDelimiter = True
.TextFileSpaceDelimiter = True
.TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1)
.TextFileTrailingMinusNumbers = True
.Refresh BackgroundQuery:=False
End With
ActiveWindow.SmallScroll Down:=-3
End Sub
--------------------------------------------------------------------------

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

windslayer wrote:


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

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.c sv,
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

--
Being watched by two sleazy guys wasn't enough.
If it were millions of sleazy guys, that would be okay.
But two? That's sick.