Importing new data
 

I have a problem that i can not figure out. I need to import new data
file into existing woorksheet but it needs to be imported into first
empty cell in A.

This is the code i am working with right now. It importsw the data
fine, but that last cell thing i can not figure out.

With ActiveSheet.QueryTables.Add(Connection:= _
"TEXT;C:\Documents and Settings\ikirin\Desktop\San
Fran\Demolition-Data\Bent 2\BENT 2 -Final0022.dat" _
, Destination:=*Range("a1"))*
..Name = "BENT 2 -Final0022"
..FieldNames = True
..RowNumbers = False
..FillAdjacentFormulas = False
..PreserveFormatting = True
..RefreshOnFileOpen = False
..RefreshStyle = xlInsertDeleteCells
..SavePassword = False
..SaveData = True
..AdjustColumnWidth = True
..RefreshPeriod = 0
..TextFilePromptOnRefresh = False
..TextFilePlatform = 437
..TextFileStartRow = 1
..TextFileParseType = xlDelimited
..TextFileTextQualifier = xlTextQualifierDoubleQuote
..TextFileConsecutiveDelimiter = False
..TextFileTabDelimiter = True
..TextFileSemicolonDelimiter = False
..TextFileCommaDelimiter = True
..TextFileSpaceDelimiter = False
..TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1, 1, 1, 1, 1,
1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, _
1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1,
1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1)
..TextFileTrailingMinusNumbers = True
..Refresh BackgroundQuery:=False
End With


Bolded part is the file input location so if i need to open another
file in the same directory i will make a loop, i got that. But i will
need to import that file on the end of existing one. So please help.


--
ikirin
------------------------------------------------------------------------
ikirin's Profile: http://www.excelforum.com/member.php...o&userid=35599
View this thread: http://www.excelforum.com/showthread...hreadid=553700

Importing new data
 

Range("A1").End(xlDown).Offset(1,0).Select

HTH

Die_Another_Day

ikirin wrote:
I have a problem that i can not figure out. I need to import new data
file into existing woorksheet but it needs to be imported into first
empty cell in A.

This is the code i am working with right now. It importsw the data
fine, but that last cell thing i can not figure out.

With ActiveSheet.QueryTables.Add(Connection:= _
"TEXT;C:\Documents and Settings\ikirin\Desktop\San
Fran\Demolition-Data\Bent 2\BENT 2 -Final0022.dat" _
, Destination:=*Range("a1"))*
.Name = "BENT 2 -Final0022"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.TextFilePromptOnRefresh = False
.TextFilePlatform = 437
.TextFileStartRow = 1
.TextFileParseType = xlDelimited
.TextFileTextQualifier = xlTextQualifierDoubleQuote
.TextFileConsecutiveDelimiter = False
.TextFileTabDelimiter = True
.TextFileSemicolonDelimiter = False
.TextFileCommaDelimiter = True
.TextFileSpaceDelimiter = False
.TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1, 1, 1, 1, 1,
1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, _
1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1,
1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1)
.TextFileTrailingMinusNumbers = True
.Refresh BackgroundQuery:=False
End With


Bolded part is the file input location so if i need to open another
file in the same directory i will make a loop, i got that. But i will
need to import that file on the end of existing one. So please help.


--
ikirin
------------------------------------------------------------------------
ikirin's Profile: http://www.excelforum.com/member.php...o&userid=35599
View this thread: http://www.excelforum.com/showthread...hreadid=553700

Importing new data
 

If you want the macro to determine the last row of data in column A try
something along the lines of:

Dim intLastrow
intLastrow = Cells(65536, 1).End(xlUp).Row

Then change the external data query destination cell (in bold) to
read:

With ActiveSheet.QueryTables.Add(Connection:="TEXT;C:\f ile1.txt", _
Destination:=*Cells(intLastrow + 1, 1)*)

Hope I've understood what you're trying to achieve!


--
ben77
------------------------------------------------------------------------
ben77's Profile: http://www.excelforum.com/member.php...o&userid=35602
View this thread: http://www.excelforum.com/showthread...hreadid=553700

Importing new data
 

i have another problem. i want to manualy input the file name in the
textbox so the macro can open it automatically. But it gives me an
error. Please help.

Private Sub CommandButton1_Click()

Dim intLastrow

intLastrow = Cells(65536, 1).End(xlUp).Row

With ActiveSheet.QueryTables.Add(Connection:= _
"TEXT; txtInputFile.text" _
, Destination:=Cells(intLastrow + 1, 1))
..Name = txtFileName.Text
..FieldNames = True
..RowNumbers = False
..FillAdjacentFormulas = False
..PreserveFormatting = True
..RefreshOnFileOpen = False
..RefreshStyle = xlInsertDeleteCells
..SavePassword = False
..SaveData = True
..AdjustColumnWidth = True
..RefreshPeriod = 0
..TextFilePromptOnRefresh = False
..TextFilePlatform = 437
..TextFileStartRow = 1
..TextFileParseType = xlDelimited
..TextFileTextQualifier = xlTextQualifierDoubleQuote
..TextFileConsecutiveDelimiter = False
..TextFileTabDelimiter = True
..TextFileSemicolonDelimiter = False
..TextFileCommaDelimiter = True
..TextFileSpaceDelimiter = False
..TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1, 1, 1, 1, 1,
1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, _
1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1,
1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1)
..TextFileTrailingMinusNumbers = True
* .Refresh BackgroundQuery:=False*
End With



End Sub

Bold is wher it gives me a error


--
ikirin
------------------------------------------------------------------------
ikirin's Profile: http://www.excelforum.com/member.php...o&userid=35599
View this thread: http://www.excelforum.com/showthread...hreadid=553700

Importing new data
 

Try adding / amending the bits in *bold*

Private Sub CommandButton1_Click()

Dim intLastrow
DIM STRINPUTFILE

intLastrow = Cells(65536, 1).End(xlUp).Row
'THIS WILL ALLOW YOU TO BROWSE FOR THE FILE, RATHER THAN HAVING TO TYPE
IT AND THE PATH.
*strInputfile = Application.GetOpenFilename*

With ActiveSheet.QueryTables.Add(Connection:= _
*"TEXT;" & strInputfile *_
, Destination:=Cells(intLastrow + 1, 1))


--
ben77
------------------------------------------------------------------------
ben77's Profile: http://www.excelforum.com/member.php...o&userid=35602
View this thread: http://www.excelforum.com/showthread...hreadid=553700

Importing new data
 

thank you so much, this works so far. I need to add a lot of new thing
now, if i have any question i hope i can ask you again. Thank you s
much again

--
ikiri
-----------------------------------------------------------------------
ikirin's Profile: http://www.excelforum.com/member.php...fo&userid=3559
View this thread: http://www.excelforum.com/showthread.php?threadid=55370

Importing new data
 

again a problem with the same thing. i open a file with one button, sore
it in the variable, and than with another button i want to import it to
the excell. Bold is a problem. I read something like that i do not have
a premmission to acces a database. I do not even know how to change this
and if i do have it

Private Sub btnCombine_Click()

Dim intLastrow

intLastrow = Cells(65536, 1).End(xlUp).Row


With ActiveSheet.QueryTables.Add(Connection:= _
"TEXT;" & strFirstFileName _
, Destination:=Cells(intLastrow + 1, 1))
..Name = "BENT 2 -Final0022"
..FieldNames = True
..RowNumbers = False
..FillAdjacentFormulas = False
..PreserveFormatting = True
..RefreshOnFileOpen = False
..RefreshStyle = xlInsertDeleteCells
..SavePassword = False
..SaveData = True
..AdjustColumnWidth = True
..RefreshPeriod = 0
..TextFilePromptOnRefresh = False
..TextFilePlatform = 437
..TextFileStartRow = 1
..TextFileParseType = xlDelimited
..TextFileTextQualifier = xlTextQualifierDoubleQuote
..TextFileConsecutiveDelimiter = False
..TextFileTabDelimiter = True
..TextFileSemicolonDelimiter = False
..TextFileCommaDelimiter = True
..TextFileSpaceDelimiter = False
..TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1, 1, 1, 1, 1,
1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, _
1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1,
1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1)
..TextFileTrailingMinusNumbers = True
*.Refresh BackgroundQuery:=False*

End With

End Sub

Private Sub btnFirstFileName_Click()

Dim strFirstFileName

strFirstFileName = Application.GetOpenFilename
txtFirstFileName.Text = strFirstFileName

End Sub


--
ikirin
------------------------------------------------------------------------
ikirin's Profile: http://www.excelforum.com/member.php...o&userid=35599
View this thread: http://www.excelforum.com/showthread...hreadid=553700

Importing new data
 

Hi,

The '.Refresh BackgroundQuery:=False' error is being received in thi
case because the variable is set in a different private sub, so Privat
Sub btnCombine_Click() does not know what strFirstFileName is. I'v
copied your code and justed tweaked a bit:


Code
-------------------
PUBLIC STRFIRSTFILENAME AS STRIN

Private Sub btnCombine_Click()

Dim intLastrow

intLastrow = Cells(65536, 1).End(xlUp).Row

With ActiveSheet.QueryTables.Add(Connection:= _
"TEXT;" & strFirstFileName _
, Destination:=Cells(intLastrow + 1, 1))
.Name = "BENT 2 -Final0022"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.TextFilePromptOnRefresh = False
.TextFilePlatform = 437
.TextFileStartRow = 1
.TextFileParseType = xlDelimited
.TextFileTextQualifier = xlTextQualifierDoubleQuote
.TextFileConsecutiveDelimiter = False
.TextFileTabDelimiter = True
.TextFileSemicolonDelimiter = False
.TextFileCommaDelimiter = True
.TextFileSpaceDelimiter = False
.TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, _
1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1)
.TextFileTrailingMinusNumbers = True
.Refresh BackgroundQuery:=False

End With

End Sub

Private Sub btnFirstFileName_Click()

strFirstFileName = Application.GetOpenFilename
txtFirstFileName.Text = strFirstFileName

End Su
-------------------


Hope this helps,



--
ben7
-----------------------------------------------------------------------
ben77's Profile: http://www.excelforum.com/member.php...fo&userid=3560
View this thread: http://www.excelforum.com/showthread.php?threadid=55370