Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to return more data than will fit on a worksheet
How to return more data than will fit on a worksheet. For example, I need to import 170000 rows from MS Access to MS Excel -- Oleg ------------------------------------------------------------------------ Oleg's Profile: http://www.excelforum.com/member.php...fo&userid=4004 View this thread: http://www.excelforum.com/showthread...hreadid=488193 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to return more data than will fit on a worksheet
Oleg wrote:
How to return more data than will fit on a worksheet. For example, I need to import 170000 rows from MS Access to MS Excel 1) Import it in two chunks to two or more sheets, or 2) import it in two chunks to two or more different columns in one sheet, or 3) find another spreadsheet program that will hold more rows and still talk with MS Access. Bill |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to return more data than will fit on a worksheet
I had the same problem. Decided to go to a delimited text file. This was
more versitile as we found the end user was trying to import to MS Access anyway. "Oleg" wrote: How to return more data than will fit on a worksheet. For example, I need to import 170000 rows from MS Access to MS Excel -- Oleg ------------------------------------------------------------------------ Oleg's Profile: http://www.excelforum.com/member.php...fo&userid=4004 View this thread: http://www.excelforum.com/showthread...hreadid=488193 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to return more data than will fit on a worksheet
Give this a try:
Sub LargeFileImport() 'Bernie Deitrick's code for opening vary large text files in Excel 'Dimension Variables Dim ResultStr As String Dim FileName As String Dim FileNum As Integer Dim Counter As Double 'Ask User for File's Name FileName = Application.GetOpenFilename '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 Do While Seek(FileNum) <= LOF(FileNum) 'Display Importing Row Number On Status Bar Application.StatusBar = "Importing Row " & Counter & " of text file " _ & FileName 'Store One Line Of Text From File To Variable Line Input #FileNum, ResultStr 'Store Variable Data Into Active Cell If Left(ResultStr, 1) = "=" Then ActiveCell.Value = "'" & ResultStr Else ActiveCell.Value = ResultStr End If If ActiveCell.Row = 65536 Then 'If On The Last Row Then Add A New Sheet ActiveWorkbook.Sheets.Add Else 'If Not The Last Row Then Go One Cell Down ActiveCell.Offset(1, 0).Select End If 'Increment the Counter By 1 Counter = Counter + 1 'Start Again At Top Of 'Do While' Statement Loop 'Close The Open Text File Close 'Remove Message From Status Bar Application.StatusBar = False End Sub HTH "Oleg" wrote in message ... How to return more data than will fit on a worksheet. For example, I need to import 170000 rows from MS Access to MS Excel -- Oleg ------------------------------------------------------------------------ Oleg's Profile: http://www.excelforum.com/member.php...fo&userid=4004 View this thread: http://www.excelforum.com/showthread...hreadid=488193 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
SELECT large amount of data in a worksheet | Excel Discussion (Misc queries) | |||
Excel 2002 chart does not update when worksheet data changes | Charts and Charting in Excel | |||
Sort pages? | Excel Discussion (Misc queries) | |||
How do I collect data from an interactive web worksheet and impor. | Excel Discussion (Misc queries) | |||
Weekly Transaction Processing | Excel Worksheet Functions |