smurray444


Dear all,

I have some VBA code which will import large files into Excel 2007 and upon reaching the bottom of the sheet, will continue onto the next. However, by default it imports starting in column A - yet if I have data in A already, I don't want this to be overwritten. Therefore I was wondering how the code would be adapted slightly so that I can dictate into which column to start the import. The code at present is:

'"Text Files (*.txt),*.txt
Option Explicit
Sub LargeFileImport()
Const MaxRows As Long = 1048576
'Dimension Variables
Dim ResultStr As String
Dim FileName As String
Dim FileNum As Integer
Dim Counter As Double
Dim num() As Variant
Dim v As Variant, i As Long, j As Long
Dim s As String, sChr As String
Dim rw As Long
'Ask User for File's Name
FileName = Application.GetOpenFilename( _
FileFilter:="Text Files (*.txt),*.txt")
'Check for no entry
If FileName = "" Then End
'Get Next Available File Handle Number
FileNum = FreeFile()
'Open Text File For Input
Open FileName For Input As #FileNum
'Turn Screen Updating Off
'Application.ScreenUpdating = False
'Create A New WorkBook With One Worksheet In It
Workbooks.Add template:=xlWorksheet
'Set The Counter to 1
Counter = 1
'Loop Until the End Of File Is Reached
s = ""
rw = 1
Do While Seek(FileNum) <= LOF(FileNum)
'Display Importing Row Number On Status Bar
' Application.StatusBar =
Debug.Print "Importing Row " & _
Counter & " of text file " & FileName
'Store One Line Of Text From File To Variable
ResultStr = Input(1000, #FileNum)
'Store Variable Data Into Active Cell
For i = 1 To Len(ResultStr)
sChr = Mid(ResultStr, i, 1)
If Asc(sChr) = 10 Then
If Len(Trim(s)) > 0 Then
v = Split(Application.Trim(s), " ")
ReDim num(LBound(v) To UBound(v))
For j = LBound(v) To UBound(v)
num(j) = v(j)
Next
Cells(rw, 1).Resize(1, _
UBound(v) - LBound(v) + 1) = num
rw = rw + 1
s = ""
Erase v
If rw > MaxRows Then
ActiveWorkbook.Sheets.Add
rw = 1
End If
End If
Else
s = s & sChr
End If
Next
'Increment the Counter By 1
Counter = Counter + 1
' If Counter > 1E+307 Then
' Exit Do
' End If
'Start Again At Top Of 'Do While' Statement
Loop
'Close The Open Text File
Close
If Len(Trim(s)) > 0 Then
v = Split(Application.Trim(s), " ")
ReDim num(LBound(v) To UBound(v))
For j = LBound(v) To UBound(v)
num(j) = CSng(v(j))
Next
Cells(rw, 1).Resize(1, _
UBound(v) - LBound(v) + 1) = num
rw = rw + 1
s = ""
Erase v
If rw > 1048576 Then
ActiveWorkbook.Sheets.Add
rw = 1
End If
End If

'Remove Message From Status Bar
Application.StatusBar = False
End Sub


Thanks for your time and advice,
smurray444



Re: VBA code tweak to import to different column

smurray444


One final point, and I really do hope then that I'm sorted, is that when I want to import data side by side (i.e. starting at a different column), the code opens up a new worksheet, meaning that the data are imported onto separate sheets and not next to each other.

I've tried removing the 'Workbooks.Add template:=xlWorksheet' row in the code - this solves the problem for the first sheet, however, when the data to be imported runs onto the next sheet, the code opens up a n new worksheet (as before).

So my question is, how can the code be tweaked so that it doesn't start a new worksheet upon reaching the end of the previous sheet during a large import - I want it to overspill onto a different worksheet as before, but the existing 'Sheet 2' as opposed to a newly created sheet.

Hope you are able to help once more.
Many thanks,
smurray444





Re: VBA code tweak to import to different column

Spidermans_DarkSide - MSP, VSIP

Hi,

This forum is for VB.Net questions only, sorry.

Please see.>>

http://forums.microsoft.com/MSDN/ShowPost.aspx PostID=551512&SiteID=1

Try the MAIN VBA forum please.>>

http://forums.microsoft.com/MSDN/ShowForum.aspx ForumID=74&SiteID=1

or this one.>>>>

http://www.programmersheaven.com/mb/vba/Board.aspx S=B20000

Regards,

S_DS