Import File Memory Leak Using QueryTables

Jan 11, 2013

I have a Workbook containing two worksheets "X" and "FILES". The worksheet "FILES" contains the name of each file in a directory on my desktop "C:UsersOwnerDesktopDATA-X" in cells (A1:A1000) (There are 1000 files in the folder "DATA-X". The worksheet "X" is a blank worksheet.

The following code loops 1000 times, each time importing the "k"th file from the folder "DATA-X" and storing it in the worksheet "X". After each loop, the worksheet "X" gets replaced with the data in the next file from the folder "DATA-X". As the program is running, it can be seen in the task manager that memory is accumulating in the Excel program. In fact, it cannot finish because it will use up all memory by then (I get a message "OUT OF MEMORY").

VB:
Sub MemoryTest()
Dim qt As QueryTable
Dim WSh As Worksheet
Sheets("X").Select

[Code]....

View 1 Replies


ADVERTISEMENT

ADO: Memory Leak

Oct 4, 2006

Regarding the Memory Leak problem with ADO, does anyone know of a workaround?

The workaround mentioned on Microsoft's website (http://support.microsoft.com/default...;en-us;Q319998) pertains to using ADO to retrieve a recordset from an Excel worksheet, not from an Access Database.

1) Does anyone know if those workarounds would also address retrieving a recordset from an Access Database?

2) If so, is the "SELECT INTO" workaround effective?

Is this Memory Leak issue relatively minor or only affecting Excel 2000? I'm guessing it must be one of those 2 things since the concensus is ADO is superior to MSQuery..

View 2 Replies View Related

Not Enough Memory On Data Import

Sep 9, 2008

I'm trying to import data into my sheet with formulae like this in row1(Columns A:I): ='F:Dir[File.xls]Sheet'!A2.

The files I'm importing the data from all have 50.000 rows and 9 columns, so I copy the formulae downward 50.000 times. This generates an error ('Not enough memory'), not surprisingly. I've also tried to do it in steps by way of VBA; first two thousand, copy and paste values, next two thousand etc. But that doesn't work either.

Does anyone know of a solution to this memory problem or a way to work around it (without opening the files with the data in them).

View 9 Replies View Related

Out Of Memory While Importing Data From CSV File To Create Pivot Cache

Dec 20, 2013

We are trying to create pivot cache from an external .csv file.

This works fine when the csv file is small (~500 mb).

Attaching sample code for reference -

Code:
Dim cConnection As ADODB.Connection
Dim rsRecordset As ADODB.Recordset
Dim pvtCache As PivotCache
Dim pvtTable As PivotTable
Dim SQL As String

''Creating ADODB connection object

[Code] ...........

View 5 Replies View Related

Locate CSV File With Open File Msgbox And Import Columns From CSV To Worksheet

Oct 28, 2011

Actually i've downloaded Tracking report of my SPO and this report is on 4-5 csv files (1 file per week). I have to gather data from these 4 files into one Worksheet. I have to do this with command button on my worksheet; with Open file dialogue box (i want to locate the csv files).

CSV files are something like that (I need only first 4 columns):

ABCDE1Name MSISDN Date Location MapLink 2M. Younus Safi "923***550577" "2011-10-07 20:36:18" "ACB Tower Badragah Chakwal. Badraga" "http://asdf.com" 3M. Younus Safi "923***550577" "2011-10-07 19:36:26" "ACB Tower Badragah Chakwal. Badraga" "http://asdf.com" 4M. Younus Safi "923***550577" "2011-10-07 16:39:58" "ACB Tower Badragah Chakwal. Badraga" "http://asdf.com" 5M. Younus Safi "923***550577" "2011-10-07 15:37:23" "ACB Tower Badragah Chakwal. Badraga" "http://asdf.com" 6M. Younus Safi "923***550577" "2011-10-07 14:37:05" "ACB Tower Badragah Chakwal. Badraga" "http://asdf.com"

and my Worksheet is like that (With a command Button):

I want to import first 3 columns of csv file on first 3 columns on my worksheet; leave 2 columns blank and then import the 4th column... now for example i have done importing data from 1st csv file and the data is on 50 rows.. i click the command button again, locate the 2nd csv file.. do the same thing (import 1st 3 columns, 2 blank, then 4th) --from 51st Row-- and so on...

ABCDEF1NameMSISDN DatedBlankBlank Location2

3

4

5

6

7

View 4 Replies View Related

Import Text File Into File Where Rows Exceed Excel Row Limit

Jun 3, 2009

I have a workbook with many spreadsheet named Sheet1, Sheet2 and so on. Each sheet is filled completely upto 65536 rows. This data is being picked up from a CSV file. In this file there are sites with each site there is a assciated set of data. What happens is the data that extracts data does not differntiate between sites and when it reaches to the end of worksheet it splits the data into next sheet. So I am trying to create a macro to check each spreadsheet starting with the last sheet in the work book for example last sheet in the workbook is seven it should go to sheet6 and if there is a blank row after row 64000 it should cut all the rows and move them to sheet7. Then it should goto sheet5 and do the same and keep on doing it until it reaches sheet1.

View 9 Replies View Related

QueryTables - Add

May 20, 2003

I recorded setting up a query to get information off our Data Warehouse about a Tracking Number that the user enters into the spreadsheet and got this little piece of gold.

Sub DataDump()

Dim sqlFromText, sqlWhereTxt, sqlSelectTxt
Dim qt As QueryTable

For Each qt In Worksheets("DW Data Dump").QueryTables
qt.Delete
Next qt

On Error GoTo err_Label

The problem is that now when I try to run it I get a general ODBC error and the debugging line that is highlighted is .Refresh BackgroundQuery:=True.

View 9 Replies View Related

Import CSV File Using Cell As File Name & Active Workbook Path

Nov 3, 2009

I am trying to import some csv files so I can combine them, but am having probs with the filename and location.

Sub test()
Dim wsName As String
wsName = ActiveCell
Sheets("Data").Select
With ActiveSheet.UsedRange
LastRow = .SpecialCells(11).Row
End With

With ActiveSheet.QueryTables.Add(Connection:="TEXT; &thisWorkbook.Path &" " & wsName &", Destination:= Range("A" & LastRow))
.Name = wsName
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.RefreshStyle = xlInsertDeleteCells.....................

View 5 Replies View Related

Web Query With QueryTables

May 30, 2009

I am having problems getting to work a web query in a VBA macro. The goal is simply to get a value retrieved from a web query into an excel cell.

The below code works well with standard html pages (like replacing the url with www.google.com). However, the page I want to retreive (a query at geonames.org that retreives an altitude value for given lattitude/longitude values) does not contain any HTML headers, but just a number in plain text. In this case the below query does not returns anything. I tried different formating options for the query but up to nownothing works.

View 3 Replies View Related

ActiveSheet.QueryTables.Add Connection:

Jun 13, 2006

Sub WebQuery()
Dim strSearch As String
strSearch = "abc"

With ActiveSheet.QueryTables.Add(Connection:= _
"URL;http://www.xyz.com/&=" & strSearch & "", _
Destination:= Range("A1"))
.Name = "search?hl=en&ie=UTF-8&oe=UTF-8&q=" & strSearch & ""
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = False
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.WebSelectionType = xlAllTables
.WebFormatting = xlWebFormattingAll
.WebPreFormattedTextToColumns = True
.WebConsecutiveDelimitersAsOne = True
.WebSingleBlockTextImport = False
.WebDisableDateRecognition = False
.Refresh BackgroundQuery:=False
End With

End Sub...

View 6 Replies View Related

String Limit In Array - Querytables - Error 13

Jun 23, 2007

I need understanding what is the maximum length of string data type. I read that is 63K characters... but, in my case... maximum value is 253. I don't know why happens this. here is some code from one of my buttons.

Private Sub btn_procesare_Click()
Dim intUnit As Integer
Dim strBuffer As String
Dim contor, var_else, cale, dest, dest_final, masca, filtru, **** As String
Dim vntbuf As Variant
Dim sql As Variant
For i = 0 To Me.ListBox2.ListCount - 1
Application.Worksheets(1). Range("A" & i + 2).Value = Me.ListBox2.List(i, 0)
Next...........................

when watch for x, the value is maximum 253 characters. I get error Data Type Mismatch - Error 13 with this.

View 4 Replies View Related

How To Import TXT File

Jun 22, 2014

import .txt file to excel.

I have below information in txt file

Microsoft (R) Windows Script Host Version 5.8Copyright (C) Microsoft Corporation. All rights reserved.

------------------------------------------
System Details
------------------------------------------
Host Name: XYZ
CPU COUNT : 2
Total RAM: 4 GB
=================
Disk 1
=================
Disk 1DeviceID: C:
Disk 1Disk Size: 112GB
=================

***** script completed - exit code: 0 *****

------------------------------------------
System Details
------------------------------------------
Host Name: XYZ1
CPU COUNT : 2
Total RAM: 4 GB
=================
Disk 1
=================
Disk 1DeviceID: C:
Disk 1Disk Size: 112GB
=================
Disk 2
=================
Disk 2DeviceID: D:
Disk 2Disk Size: 500GB
=================

***** script completed - exit code: 0 *****

required excel format.

Host Name:| CPU COUNT: |Total RAM: |Disk 1 DeviceID:| Disk 1 Disk Size: | Disk 2 DeviceID: |Disk 2 Disk Size:
XYZ| 2 | 4 GB| C | 112GB | |
XYZ1| 2 | 4 GB|C | 112GB | D | 500GB

View 2 Replies View Related

Import Txt File

Jan 28, 2009

I am having a problem importing data as a text file into excel such as

bills company,
12 street rd,
canada,
sss@sss.com,
www.ssss.com,
,
franks company,
12 road sr,
brasil,
ddd@ddd.com,
www.ddd.com,

would like it to be displayed in columns as
bills company 12 street rd canada sss@sss.com, www.ssss.com,
franks company 12 road sr brasil ddd@ddd.com www.ddd.com,

the , is stripped away when I try to open the txt file and set it up for excell
the spaces are not where I want them, I have set the delimiter options correctly

I have a large data set and preferably want an quick solution

View 9 Replies View Related

Import Bas File

May 20, 2009

How do you import a bas file.

My code is

Sub AddCode()
Application.ThisWorkbook.VBProject.VBComponents.Import Filename:="C:UsersStrengDocumentsExcel Spreadsheetsmycode.bas"
End Sub

I am getting a Run Time Error '1004'
Method 'VBProject' of object'_ Workbook' failed

View 9 Replies View Related

Import A File From A Notepad

Mar 16, 2009

I am trying to import a file from a notepad to excel. I am trying to copy and paste in to excel. When I paste, all the info is in the same cell, although it looks that there is tabs between columns. Is there a way for me import those numbers into a spread sheet in different columns?......

View 3 Replies View Related

How To Import Text File With Vba

Mar 11, 2014

I have a lot of txt. files you would like to imported automatically via VBA code text file is always the same need only certain data in these two rows which is the red just want this data

it is copied from txt. files in Excel so that I had with the click of a button you obtain all the information and through this button you looking for the folder in which the data next would have 2 lines one would be the computer name of one folder and the user can more This would also be the folder in which the data were computer name to the main folder subfolder, users, and wanted to let me read the particulars of which are in the name of the computer and the user. for example. computer name of the user

i have in excel what i need

View 5 Replies View Related

Import XML File Macro

Jan 29, 2014

I wish to click on a button in a worksheet which then looks in a particular folder on the local drive. Any file with a .xml extension import into the worksheet.

What if 2 or more xml files are present? The xml file filename ends with the date & time the file was generated, eg BPSEvent_10033_Tam_20140116124216

Can a code look for the oldest file to import based on the end of the filenaming format - yyyymmddhhmmss

Code to then delete the last xml file imported would be good too.

View 14 Replies View Related

Import Any Text File

Mar 27, 2009

I recorded the following macro but it only imports files with a specific name. Can someone change the code to allow it open the specific file loaction where I may choose which .txt file I want to import.

View 3 Replies View Related

Import Text File

Sep 27, 2009

I've a large text file which I need to import selected information only.

Below is the sample text file which showing profile for 3 item.

View 14 Replies View Related

Import CSV File To Excel

Jun 14, 2013

I have a CSV file which I want to import in excel. There are many text entries after the comma that starts with = and +.

When I imported this csv to excel then excel uses its mathematical logic that and gives a result #Name?

I tried reformatting these cells to treat as Text field only but that did not work.

I would like to maintain these text fields that starts with = and + .

View 3 Replies View Related

Data Import From File?

Jul 11, 2014

I have several files from different departments that I have to extract sections of data from and compile into one central file for reporting.

I am very new to VBA but would like to set up a button so that I can import data from closed files in different departmental directories. I have recorded a macro and got it to work if the file is open but I can't seem to get it to work if the file is closed.

View 2 Replies View Related

Macro To Import .DAT File

Dec 20, 2006

I would like to have a macro that will allow me to browse to the desired file and import the data into columns. I have the macro working to the point of allowing me to browse, select the file, and pasting the contents into the active sheet.

The things that I need help with are:

1. Getting the data to paste into a specific cell instead of A1. Currently the code is

CELLS.SELECT
ACTIVESHEET.PASTE

I would like the data to start in A7.


2. Getting the file parsed into columns. The format of the .DAT file is static and only has 4 columns. Currently when it imports, the entire record (row) all of the data is in the same column. I would like it separated into individual columns if possible.

View 9 Replies View Related

Import A .bas File Into A Specified Workbook

Jan 9, 2007

At work I have a folder full of .bas files containing useful UDF's.
At the moment I am the only person in my team that makes use of these files as importing a .bas file is beyond the rest of the team (i.e. then know it contains code so they get scared).

What I'd like to do is create a workbook where they can select one of the files (each file contains a single UDF), maybe see what it does (I put an explanation in the Comments section of the properties for each file) and then have it automatically imported into the workbook of their choice so they can make use of these functions.

View 9 Replies View Related

Import Data From Another File

Jan 31, 2007

My task is to make an excelfile that import data from other excelfiles. These data should then be summarized. There's not always same files. I should be able to add more files.

View 4 Replies View Related

Import Log File With Macro

Mar 13, 2007

I did a macro the import a log file from a specific directory and now i want that the user could enter the path of the directory that contain the file Is there a way to do it?

ub ALL_Logs_files()

'
' Logssss Macro
' Macro recorded 3/4/2007 by dzaitoun
'

Sheets.Add
ActiveSheet. Name = "Foresight FP Data"

With ActiveSheet.QueryTables.Add(Connection:= _
"TEXT;C:Documents and SettingsdzaitounDesktopLog out filesforesight.fp.out" _ , Destination:=Range("A1"))
.Name = "foresight.fp"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True ..............

View 4 Replies View Related

Import .txt File With Wildcard

Sep 10, 2007

I am trying to import a text file into an existing worksheet using wild cards (the file name changes because it has time stamp info in it). I would like to be able to import the Heavy Water file even when the time stamp info changes. The code I have so far is:

Sub Macro3()
With ActiveSheet.QueryTables.Add(Connection:= _
"TEXT;C:UsersapatoGrandeDocumentsEVElogsMarketlogsThe Forge-Heavy Water-2007.09.09 184540.txt" _
, Destination:=Range("$A$1"))
.Name = "The Forge-Heavy Water-2007.09.09 184540_2"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.TextFilePromptOnRefresh = False.................

View 8 Replies View Related

File Has Too Many Columns For Import

Jan 29, 2008

I've made the mistake of building an online survey without regard for the number of questions and how that might relate to import. (Excel gives the error, "File Not Completely Loaded.") As a result, I have a .csv raw data file which exceeds the number of columns in Excel's upper limit. Would anyone know of a utility that will break that file into sections so it can be manipulated in Excel?

View 3 Replies View Related

Row Separation In Text File Import?

Aug 31, 2010

I have imported a tab delimited text file into excel. One of the columns consists of a string of text - a comment section. Within this column, there are periods and for some reason it seems that Excel is treating these periods as row separators. how I can prevent Excel from separating the text into rows?

View 3 Replies View Related

Partial Import From Text File

Jun 18, 2014

I have about 100 text files from which I need to import specific sets of numbers into excel. The part in the file looks like this

Mg24(MR)1917.4198.5
Mg25(MR)250.453.6
Mg26(MR)264.464.2

I need to import the numbers in the 1st row but they need to be transposed. and than of cause I need to do that for the other 99 files I have.

View 14 Replies View Related

Advanced Text File Import

Apr 21, 2009

I am a noob to VBA, and not much of a programmer either.
I know how to import a simple text file into excel, but this time I have something more complicated.

View 6 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved