Re: Macro to import data from several files
Any one has an idea for this ?
Re: Macro to import data from several files
Any one has an idea for this ?
Hello every one.
Im trying to make a code to import data from (in exaple) 20 files, all located in the same path,
lets say that the file names are:
File01, File02, File03 and so on to File20
my tought process tells me that I may be able to use a variable named for example "current_file" and make that variable goes from File01 to File20, after declaring the names of each file in a list, then import the data, go to the next file and import that data under the data from last file and so on
Using that, I gess that a code to do that might look something like this, how ever im pretty sure it won't work like its writen
Sub import_data_from_csv()
Dim Last_Row As Long
Dim current_file As stringList
For current_file = File01 To File20
Last_Row = Range("A" & Rows.Count).End(xlUp).Row + 1
With ActiveSheet.QueryTables.Add(Connection:="TEXT;C:\path\ current_file & .csv", Destination:=Range("A" & Last_Row))
.Name = "current_file"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.TextFilePromptOnRefresh = False
.TextFilePlatform = 850
.TextFileStartRow = 11
.TextFileParseType = xlDelimited
.TextFileTextQualifier = xlTextQualifierDoubleQuote
.TextFileConsecutiveDelimiter = False
.TextFileTabDelimiter = False
.TextFileSemicolonDelimiter = False
.TextFileCommaDelimiter = True
.TextFileSpaceDelimiter = False
.TextFileColumnDataTypes = Array(1, 1, 4, 1, 1, 1, 1, 1)
.TextFileTrailingMinusNumbers = True
.Refresh BackgroundQuery:=False
Next current_file
End Sub
Display More
I have no idea how to make a variable a list or if VBA will understand the "C:\path\ current_file" & .csv
any guide I can get will be very appreciated.
Re: fix import date format from a CSV
nice tip, it worked perfectly. thank you
Quote from cytop;680115On the Import Wizard dialog:
Select 'Delimited' on screen 1
Select , as the delimiter on screen 2
Select Date (DMY in the dropdown list) on screen 3Works for me, at least...
Hello again.
today I come with the next issue:
I have several csv files that has info in the next format:
text1,11/08/13 03:44:16 am,texta
text2,12/08/13 05:06:21 am,textb
text3,13/08/13 05:05:16 am,textc
text4,12/08/13 05:01:56 am,textd
when i open it in excell. it asumes that the date/time is in format "mm/dd/yy hh:mm:ss" but its actually in "dd/mm/yy hh:mm:ss" so the dates are all wrong.
any idea on how to solve this issue?
thanks in advance
Re: Using a macro to concatenate while next cell is not empty
Quote from snb;679911What unnecessary work ?
it goes from A1 to A1000, so if i only have 300 lines, there are 700 that i dont need.
also if there are 2000 lines, i would have to update the macro to reach A2000
on other hand, if somehow i require to add columns D, E, F, etc, then it becomes even more complex as the other code only have to change the E for the required column to print the data.
I have worked with macros for only few days, I might very well be mistaken on that
Re: VLOOKUP based on two conditions
There should be a better way, but I do something like this:
in file 1:
I would insert a column before "A" and use the formula A1=B1&C1, then copy paste to the end
in File 2:
column C formula = A1&A2
column D formula = vlookup(C1,[file1]A:D,4,0)
there might be a better answer but this makes the work
Re: Using a macro to concatenate while next cell is not empty
also tried your code and works too, its just that it seems to do unnecesary work, how ever, also many thanks for sharing this
Quote from snb;679890
Re: Using a macro to concatenate while next cell is not empty
Thank you so much.
It worked perfectlly, just had to change
to
Quote from PCI;679886See how next code can help
Code Display MoreSub ConcatenateData() Dim LastRow As Long Dim I As Long Dim F As Range Dim G Application.ScreenUpdating = False LastRow = Range("A" & Rows.Count).End(xlUp).Row Range(Cells(1, "E"), Cells(LastRow, "E")).ClearContents For I = 1 To LastRow G = Empty For Each F In Cells(I, 2).Resize(1, 3) If (F <> "") Then G = G & "/" & F.Value Next F Cells(I, 5) = G Next I Application.ScreenUpdating = True End Sub
Hello, Im trying to create a macro that can concatenate data from a DB , but still cant make it work
the data I have is in the next format:
A B C D E
1 text1 text2 text3
2 texta textb
3 textx texty textz
4
and for each row I need to get on column E the data as shown:
E1 = /text1/text2/text3
E2 = /texta/textb
E3 = /textx/texty/textz
Note that on E2 there is only 2 "/" insted of 3 as in E1 and E3.
So i tryed this:
Sub function()
Dim var1 As String
Dim var2 As String
var1 = "/"
var2 = ""
Range("a1").Select
While IsEmpty(ActiveCell) = False
var2 = "var1&(ActiveCell)"
var1 = (ActiveCell)
ActiveCell.Offset(0, 1).Select
Wend
Range("E1").Select
ActiveCell.FormulaR1C1 = var2
ActiveCell.Offset(1, 0).Select
End Sub
Display More
I gess I need to adjust the range to be something like " Range(A[curren_row]).select " and " Range(E[current_row]) "
and also make the var2 = "var1&(ActiveCell)" part to work. any advice?
thanks in advance