Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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 -------------------------------------------------------------------------- |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Macro to import CSV file from fixed directory | Excel Programming | |||
Help with macro. Import text file (fixed width) | Excel Programming | |||
Import data from other Excel file if file ends with "[directory][filename].xls" | Excel Programming | |||
auto file path update when excel sheet moved to another directory. | Excel Discussion (Misc queries) | |||
Import *.asc file into excel fixed width | Excel Programming |