Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Imported text file to begin from the first empty cell
Hi
I want to import a textfile to excel worksheet. Problem is, that I want the imported data to begin in the first empty cell in column A. How do I do this? So far the macro is: Sub data_import() With ActiveSheet.QueryTables.Add(Connection:="TEXT;C:\O pticon\Data\Data.txt" _ , Destination:=Range("A1")) .Name = "Data" .FieldNames = True .RowNumbers = False .FillAdjacentFormulas = False .PreserveFormatting = True .RefreshOnFileOpen = False .RefreshStyle = xlInsertDeleteCells .SavePassword = False .SaveData = True .AdjustColumnWidth = False .RefreshPeriod = 0 .TextFilePromptOnRefresh = False .TextFilePlatform = 850 .TextFileStartRow = 1 .TextFileParseType = xlDelimited .TextFileTextQualifier = xlTextQualifierDoubleQuote .TextFileConsecutiveDelimiter = False .TextFileTabDelimiter = True .TextFileSemicolonDelimiter = True .TextFileCommaDelimiter = False .TextFileSpaceDelimiter = False .TextFileColumnDataTypes = Array(1, 1, 1, 1) .TextFileTrailingMinusNumbers = True .Refresh BackgroundQuery:=False End With End Sub Thanks, Elina |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Imported text file to begin from the first empty cell
Add this line to the top of your code:
' finds first empty cell in Col A, starting from top of sheet lngLastRow = ActiveSheet.Cells(1, "A").End(xlDown).Row + 1 Then change your destination line: Destination:=Range("A" & lngLastRow) -- Cheers, Ryan "p11p00" wrote: Hi I want to import a textfile to excel worksheet. Problem is, that I want the imported data to begin in the first empty cell in column A. How do I do this? So far the macro is: Sub data_import() With ActiveSheet.QueryTables.Add(Connection:="TEXT;C:\O pticon\Data\Data.txt" _ , Destination:=Range("A1")) .Name = "Data" .FieldNames = True .RowNumbers = False .FillAdjacentFormulas = False .PreserveFormatting = True .RefreshOnFileOpen = False .RefreshStyle = xlInsertDeleteCells .SavePassword = False .SaveData = True .AdjustColumnWidth = False .RefreshPeriod = 0 .TextFilePromptOnRefresh = False .TextFilePlatform = 850 .TextFileStartRow = 1 .TextFileParseType = xlDelimited .TextFileTextQualifier = xlTextQualifierDoubleQuote .TextFileConsecutiveDelimiter = False .TextFileTabDelimiter = True .TextFileSemicolonDelimiter = True .TextFileCommaDelimiter = False .TextFileSpaceDelimiter = False .TextFileColumnDataTypes = Array(1, 1, 1, 1) .TextFileTrailingMinusNumbers = True .Refresh BackgroundQuery:=False End With End Sub Thanks, Elina |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Imported text file to begin from the first empty cell
Thank you RyanH, this works, if I have 2 first rows filled for some reason.
Anyway I can use this. Cheers, Elina "RyanH" kirjoitti: Add this line to the top of your code: ' finds first empty cell in Col A, starting from top of sheet lngLastRow = ActiveSheet.Cells(1, "A").End(xlDown).Row + 1 Then change your destination line: Destination:=Range("A" & lngLastRow) -- Cheers, Ryan "p11p00" wrote: Hi I want to import a textfile to excel worksheet. Problem is, that I want the imported data to begin in the first empty cell in column A. How do I do this? So far the macro is: Sub data_import() With ActiveSheet.QueryTables.Add(Connection:="TEXT;C:\O pticon\Data\Data.txt" _ , Destination:=Range("A1")) .Name = "Data" .FieldNames = True .RowNumbers = False .FillAdjacentFormulas = False .PreserveFormatting = True .RefreshOnFileOpen = False .RefreshStyle = xlInsertDeleteCells .SavePassword = False .SaveData = True .AdjustColumnWidth = False .RefreshPeriod = 0 .TextFilePromptOnRefresh = False .TextFilePlatform = 850 .TextFileStartRow = 1 .TextFileParseType = xlDelimited .TextFileTextQualifier = xlTextQualifierDoubleQuote .TextFileConsecutiveDelimiter = False .TextFileTabDelimiter = True .TextFileSemicolonDelimiter = True .TextFileCommaDelimiter = False .TextFileSpaceDelimiter = False .TextFileColumnDataTypes = Array(1, 1, 1, 1) .TextFileTrailingMinusNumbers = True .Refresh BackgroundQuery:=False End With End Sub Thanks, Elina |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Imported text file to begin from the first empty cell
If the first two rows always have something in them use this.
lngLastRow = ActiveSheet.Cells(2, "A").End(xlDown).Row + 1 I hope this helps! If so, then click "YES" below. -- Cheers, Ryan "p11p00" wrote: Thank you RyanH, this works, if I have 2 first rows filled for some reason. Anyway I can use this. Cheers, Elina "RyanH" kirjoitti: Add this line to the top of your code: ' finds first empty cell in Col A, starting from top of sheet lngLastRow = ActiveSheet.Cells(1, "A").End(xlDown).Row + 1 Then change your destination line: Destination:=Range("A" & lngLastRow) -- Cheers, Ryan "p11p00" wrote: Hi I want to import a textfile to excel worksheet. Problem is, that I want the imported data to begin in the first empty cell in column A. How do I do this? So far the macro is: Sub data_import() With ActiveSheet.QueryTables.Add(Connection:="TEXT;C:\O pticon\Data\Data.txt" _ , Destination:=Range("A1")) .Name = "Data" .FieldNames = True .RowNumbers = False .FillAdjacentFormulas = False .PreserveFormatting = True .RefreshOnFileOpen = False .RefreshStyle = xlInsertDeleteCells .SavePassword = False .SaveData = True .AdjustColumnWidth = False .RefreshPeriod = 0 .TextFilePromptOnRefresh = False .TextFilePlatform = 850 .TextFileStartRow = 1 .TextFileParseType = xlDelimited .TextFileTextQualifier = xlTextQualifierDoubleQuote .TextFileConsecutiveDelimiter = False .TextFileTabDelimiter = True .TextFileSemicolonDelimiter = True .TextFileCommaDelimiter = False .TextFileSpaceDelimiter = False .TextFileColumnDataTypes = Array(1, 1, 1, 1) .TextFileTrailingMinusNumbers = True .Refresh BackgroundQuery:=False End With End Sub Thanks, Elina |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
formatting a text file imported into excel | Excel Worksheet Functions | |||
can I display the file name of the imported text file in another c | Excel Discussion (Misc queries) | |||
Parsing imported text file with macro... help! | Excel Programming | |||
Imported Text File Worksheet Tab Name | Excel Programming | |||
if then elseif on text file imported into excel | Excel Programming |