Importing new data

  • Thread starter Thread starter ikirin
  • Start date Start date
I

ikirin

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.
 
Range("A1").End(xlDown).Offset(1,0).Select

HTH

Die_Another_Day
 
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:\file1.txt", _
Destination:=*Cells(intLastrow + 1, 1)*)

Hope I've understood what you're trying to achieve!
 
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
 
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))
 
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
 
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
 
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
 
You must log in or register to reply here.

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Help with Connection to external text data file 0
Imported text file to begin from the first empty cell 3
Text Importer 2
How can I automatically import into next empty row 4
.Refresh BackgroundQuery:=False 2
How can I automatically format a column as it is imported? 6
SOS:why run error 3
User Select File on Import External Data 3

Back
Top