Importing Data

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Norgbort Machine - 15 Mar 2006 06:37 GMT

I have a workbook with 2 worksheets. One is a cover sheet with a
variety of VLOOKUP's that reference the second sheet, which is the
data collected.

I am constantly getting new data in from outside sources, but luckily
its in a CVS file that is always the same.

What I need to do is create a VB / macro that automatically goes into
the folder on my D: drive that contains the received data forms and
Imports the data onto the 2nd sheet. The data is always the same
format, and always in a plain CSV so it imports really easily, its
just the volume that kills me.

I also need it to keep the OLD data on the sheet. Whether this is
done by importing missing data, or whether ALL the data is imported
each time (thereby grabbing new dataalong with the old)... either way
is fine with me.

Also, preferably, it starts importing at line 3 of the second sheet,
since the first 2 lines are titles and descriptions.

Thanks,

Reply to this Message

hansyt@gmx.de - 15 Mar 2006 10:07 GMT

Hi,

see this VBA code that imports two text files onto two sheets. If it
suits your needs you can modify it to reflect your actual situation. If
you need help with this post back please.

Not sure what you mean with "need to keep the old data".

Hans

Sub insert()

wn = ActiveWorkbook.Name

With Application.FileSearch

.NewSearch
.LookIn = "C:\lexware" ' Your path here
.SearchSubFolders = False
.Filename = "t*1.mod" 'Your file name here
.MatchTextExactly = True
.FileType = msoFileTypeAllFiles

If .Execute() > 1 Then
MsgBox "There were too many files found."
ElseIf .Execute() < 1 Then
MsgBox "There were no files found."
Else
fn = "Text;" & .FoundFiles(1)
Windows("Book2.xls").Activate 'Your workbook name here
Sheets("Sheet1").Select 'Your sheet name for the
impoprt here
Range("A1").Select 'Your starting cell here

With ActiveSheet.QueryTables.Add(Connection:=fn,
Destination:=Range("a1")) 'start cell here again
.TextFileParseType = xlDelimited
.TextFileTabDelimiter = True
.RefreshOnFileOpen = True
.AdjustColumnWidth = False
.RefreshStyle = xlInsertEntireRows
.FillAdjacentFormulas = True
.Refresh
End With

End If

.NewSearch
.LookIn = "C:\lexware"
.SearchSubFolders = False
.Filename = "t*2.mod"
.MatchTextExactly = True
.FileType = msoFileTypeAllFiles

If .Execute() > 1 Then
MsgBox "There were too many files found."
ElseIf .Execute() < 1 Then
MsgBox "There were no files found."
Else
fn = "Text;" & .FoundFiles(1)
Windows("Book2.xls").Activate
Sheets("Sheet2").Select
Range("A1").Select

With ActiveSheet.QueryTables.Add(Connection:=fn,
Destination:=Range("a1"))
.TextFileParseType = xlDelimited
.TextFileTabDelimiter = True
.RefreshOnFileOpen = True
.AdjustColumnWidth = False
.RefreshStyle = xlInsertEntireRows
.FillAdjacentFormulas = True
.Refresh
End With

End If

End With

End Sub

Reply to this Message