Import External Worksheet

Aug 20, 2008

I want to be able to import an external worksheet (say sheet1) to some data structure such as a 2d array or a dictionary. I don't want to store imported data temporarily in a local worksheet - I want to import external worksheet straight to array!

I have code that can import to a given worksheet name ... but I don't know how I can modify it such that it dumps it to an array or a dictionary.

The code I have is:


Sub read_in_workbook(sheet_name As String, source_dir As String, file_name As String, Optional append_from_y As Integer)
Dim rst As ADODB.Recordset
Set rst = New ADODB.Recordset
Dim stCon As String, stSQL As String
Dim fsoObj As Scripting.FileSystemObject
Dim fsoFolder As Scripting.Folder
Set fsoObj = New Scripting.FileSystemObject
Set fsoFolder = fsoObj.GetFolder(source_dir)

If append_from_y = 0 Then append_from_y = 1
stCon = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & fsoFolder & "" & file_name & ";" & _
"Extended Properties='Excel 8.0;HDR=No'"
stSQL = "SELECT * From [Sheet1$]"
rst.Open stSQL, stCon, adOpenForwardOnly, adLockReadOnly, adCmdText

Worksheets(sheet_name).Cells(append_from_y, 1).CopyFromRecordset rst

'###Instead of it dumping to a given sheet name, can I turn this routine to a function
'###and have it return a populated array or dictionary object?
End Sub

View 9 Replies


ADVERTISEMENT

Import External Data

Dec 26, 2007

I'm trying to pull in data from another Excel workbook which gets updated on a regular basis. Using the Import External Data fucntion in Excel I created a query which pulls the data from the original workbook into a tab in the workbook I'm using. The issue I'm having is that not all of the column headers get pulled in when using the Import External Data function. I tried typing the column headers in myself but they disappear again after refreshing the data set. Any ideas what is causing the issue?

View 9 Replies View Related

External Data Import

Jul 15, 2008

I am trying to import the data on the url below. When I go to the external data inport the page is loaded and I click the indicator to load the central table but when the data loads the page is severally corrupted. I have found this on a few pages but some pages load correctly.

View 9 Replies View Related

Import Of External Data

Apr 18, 2009

I’ve got a question regarding import of external data in excel. The workbook I am conceiving at the moment does have a database query from another excelfile(a report from my business system) via the standard Excel functionality and it works perfectly fine.
What I can’t figure out is how to re-direct my query to any file/path I choose.

At the moment I just replace my original file with a new freshly updated one but I am stuck with the original path and filename or else it won’t work.

View 9 Replies View Related

Batch Import External Data

Jan 29, 2007

I have several thousand text files, each with three columns of numbers. My goal is to convert each of these to a dbf file with three distinct columns (which is why simply renaming each with .dbf at the end doesn't work.) I can do this for an individual file by using the "Import External Data" Tool in Excel, selecting the "Fixed Width" option (which divides the figures into three distinct columns), and then saving the file as a dbf. I need to do this for several thousand files, however, so I can't do each by hand.

View 14 Replies View Related

Import External Data. Update Link For New Database

Dec 12, 2006

In an Excel sheet i have set up a link to an Access database query (select query) using the Import External Data method. The data is store sales by week, arranged in a pivot table layout (stores as rows, weeks as columns). As time goes by, the database will be updated for new weeks sales, and i would like to refresh the data and the new weeks automatically appear in the Excel sheet, in subsequent columns.

At the moment, the only way i can seem to get this to work is to open up the link in MSQuery and manually select the new weeks from the Access query. i have set the parameters to overwrite cells with new data rather than insert new data. i'm sure there is a way to do this, as it feels like it would be a common thing to want, but i can't seem to find an answer anywhere!

View 5 Replies View Related

External Data In Spreadsheet Without Having To Import All The Data

Aug 6, 2009

I'm wondering if I can use external data in an excel spreadsheet without having to import all the data. I basically need a persistent connection with the external data and I want the excel spreadsheet to get the item descriptions by using the item number as the user types in the item number. Writing out the descriptions is killing me and I have all my products in an excel spreadsheet already. I really need someway to link two separate excel files together. One with data and one with user input that searches that data as I type in the item number.

View 2 Replies View Related

Can't Consolidate Data To External Worksheet

Jan 31, 2013

Every month I have to do a report that takes information from four other excel spreadsheets. The spreadsheets are all the same format and contain information on community events. I have to run a report that shows what events are happening in the following month. Currently I copy and past from all the relevant data from each spreadsheet into one. Is there and easy way of making all this information to come together every month.

View 4 Replies View Related

Lock External Worksheet References

Aug 3, 2006

I have an excel workbook we use for quoting jobs. All our pricing references are in another workbook on our network. I have reference in the 'quoting' workbook that refer to the 'pricing' workbook.

The trouble is, when some users open the 'quoting' workbook, and have another Excel file open, some references to the 'pricing' workbook change to the other file they have open.

I suppose there is some sort of replicable condition that is happening, but I can't figure out what is going on or why.

So my question is: Can I ' lock' an in-cell reference to an external workbook so that it NEVER changes?

I tried hand entering the reference (network location+workbookname+sheetname) into a cell and then referencing that cell, but I couldn't get that to work.

View 9 Replies View Related

External Worksheet References Conditional Sum

Apr 17, 2007

I am trying to use a conditional sum formula where the data resides in and external workbook. This is fine until I close the external workbook and the formula no longer shows the external reference. Is there a way of getting excel to use conditional sums in this way (or what is the best way of achieving this?)

View 4 Replies View Related

Filling A Worksheet With Data From Many External Files

Sep 11, 2009

I'd be very grateful if s.o. help me find solution for the following task:

I want to fill certain worksheet with data from external .tsv files. The .tsv files are with a almost a common name (something_date.tsv), located in folders for each month.

What I'm usually doing and want to automate:

- I'm opening the first .tsv file from the monthly folder;

- Creating AutoFilter on the first row;

- Selecting a custom criteria from the AutoFilter;

- Copying the cells matching this AutoFilter criteria;

- Pasting in a predefined worksheet (with AutoFilter on first row);

*All of the copied cells are not being altered in the predefined worksheet, i.e. the first rows of the .tsv file and the worksheet are the same.

- Doing exactly the same with the next file in the monthly folder (but pasting below the already copied cells in the workbook).

- Etc.;

- Etc.;

View 14 Replies View Related

Excel 2010 :: Locate And Eliminate Links To External Sources Referenced By Worksheet

Feb 15, 2013

Upon opening after "Enable" is selected the workbook attempts to locate several nonexistent pieces of data, either internet based files or network based files. Requested data appears to be about 11 years old and would not be applicable it located.

Edit Links shows the location of the requested files, i.e., E:filename but does not show the location within the document that causes this request. A search for "E:" does not locate text in any worksheets.

The question is how to delete or turn off this problem which slows opening, saving, and recalculation of a large multiple worksheet workbook.

View 1 Replies View Related

Import Worksheet Add-in?

Oct 7, 2008

I found this script on the net which I think is very good for importing multiple workbooks/worksheets into a current workbook.

It works fine when running as a module, but when I change it become an add-in it gets a bit a confused when trying to move sheets.

I'm pretty sure its something to do with the way thisworkbook is referenced. From some of the suggestions I see it could fixed by setting-up a class module, but it sounds like i'd have to do that for every workbook i wish to import worksheets into.

View 2 Replies View Related

Import Appropriate Data To Worksheet

Nov 11, 2009

I have a workbook that contains an 'AllData' sheet. That sheet has 6 columns titled "category, keyword, bid, advertisers, search vol, online". Each category has its own worksheet and i want to put the data for each category onto the corresponding worksheet. I am having a difficult time describing this so I have attached a brief example. I have to do this for 145 categories so I would much prefer to do it automatically rather than manually.

View 14 Replies View Related

Import Worksheet Without Specifying Path

Aug 17, 2009

I am trying to do, I have a master workbook (Formatter.xls) which contains a macro 'Import'.

When run, I want to open a series of workbooks - the filenames are listed in cells B3:B20, and the workbooks are located in the same folder as Formatter.xls

I am trying to open each of the workbooks listed in B3:B20, copy the entire contents of Sheet1 to a new sheet in Formatter.xls. The names of the new sheets are listed in cells C3:C20 in Formatter.xls.

The biggest problem is that I don't want to have to specify a path to the files to be imported. Since they are all in the same folder will this be a problem? Alternatively (and I have absolutely no idea how to acomplish this), would it be possible to have an input box to browse to the folder containing all of the files?

The reason that I can't specify the path is because it will be run on different computers and the locations / paths will change all of the time.

View 9 Replies View Related

Excel 2010 :: Import CSV Into Worksheet

Dec 11, 2012

Excel 2010 / Win 7.

I have some code that imports a csv file into a workbook. This works fine when i use a file named .csv. I was hoping to use a random file name (in this case .bmhs) so that we can determine which files we need to import.

The problem i have is when importing a '.bmhs' file the data comes in column A and is a comma seperated list. If i use .csv then the commas are used as the column seperators (which i need).

impFle = Application.GetOpenFilename(filefilter:="BMHS Files, *.bmhs", Title:="Select Import File")

would give me 1,2,3,4 all in column A

impFle = Application.GetOpenFilename(filefilter:="CSV Files, *.csv", Title:="Select Import File")

would give me 1 in column A, 2 in column B, 3 in column C and 4 in column D.

Is there any way that i can maintain using my own file name (.bmhs) but have excel treat it as a csv file.

View 2 Replies View Related

Import Text File Into One Worksheet?

Jun 5, 2013

The Workbooks.OpenText works for me, but I need to import the text file into one worksheet of an existing workbook. Is there a method to do this?

View 1 Replies View Related

Import As Sheet Form The Same Folder As The Worksheet

Jul 27, 2007

i want to import a file called "export" into my work sheet, I recorded this macro

Sheets.Add
ActiveSheet.OLEObjects.Add(Filename:= _
"C:Documents and SettingsmattDesktopexport.csv", Link:=False, _
DisplayAsIcon:=False).Select

which works as long as it is on matt's pc and on my desktop, what i need is to import the file from the same folder the work sheet is stored in, that way it will work on any pc?

View 14 Replies View Related

Auto-Run Macro - Import CSV Data Into Specified Worksheet

Jun 27, 2013

I've created an Auto_Run macro that imports a CSV file into a specified worksheet. That worksheet is referenced by VlookUp in another worksheet within the workbook. Everything works perfectly EXCEPT, in the midst of running the macro, the user is prompted to (RE)choose the csv file already specified in the macro. Neither the folder nor the filename ever changes, so I'm not sure why I have to keep indicating the file every time the macro runs.

PHP Code:

 'Sub Auto_Run() ' ' Auto_Run Macro ' '    Sheets("CSVData").Select    Cells.Select    
Selection.Delete Shift:=xlUp    With ActiveSheet.QueryTables.Add(Connection:= _  "TEXT;
G:TestProjectsWorkOrderTest.csv", Destination:=Range("$A$1"))        
.Name = "WorkOrderTest"        .FieldNames = True        .RowNumbers = False        

[Code] .......

View 7 Replies View Related

Import Last Row Of Data Of Multiple CSV File To One Worksheet?

Aug 21, 2013

I'm looking for a macro that will import the last row of data of each .csv files from a specific folder, i.e., c: emp, into one new worksheet.

View 1 Replies View Related

MS Access Data Import Into An Excel Worksheet.

Oct 7, 2007

I currently have a userform, and on commandbutton_click, it performs
the following code that adds a column in a access table to a combobox(cbList)


Private Sub CmdName_Click()
Dim rstName As ADODB.Recordset
Dim strClientDatabase As String, strConnectionString As String
strClientDatabase = ActiveWorkbook.Path & "9001.mdb"......

After its listed on the combo box, upon commandbutton_click on another button, i wish to extract a particular record, based on the selection made on the combobox(cbList). This is where im having problems caused i have no idea how to do so. I just want it to extract the particular record row, based on cbList, and insert it into range A100:D100 in a particular worksheet. It is then updated and added to a listbox which ive already done the coding for, and with another button click it would add the details in the listbox into the appropriate location i wish to.. The only place im stuck is with extracting the data from access into a A100:D100 range in any worksheet. what ive come up with, but is incomplete is :


Private Sub CmdImport_Click()
Dim adoRS As ADODB.Recordset
Dim strSQL As String
Dim strClientDatabase As String, strConnectionString As String
strClientDatabase = ActiveWorkbook.Path & "9001mdb"
.........

View 9 Replies View Related

Import Folder Structure Into Worksheet & Hyperlink

Sep 7, 2006

I have found the code shown below that imports Folder Contents into a Worksheet in Excel, however i need to import a folder tree structure for a set of folders into a worksheet not the folder contents. Is it possible to do this?

Could the code i found below be altered to list the folders not the folder contents?

The code is:

Sub Print_Dir_Contents()
Dim Input_Dir, Print_File As String
Input_Dir = InputBox("Input the path containing the files you " & _
"want to list on your worksheet" & Chr(13) & Chr(13) & _
"for example:C:My Documents*.*")
If Input_Dir = "" Then Exit Sub
' If you want only to print a specific file type, you can
' substitute the "*.*" with "*.xl*"
' (for Excel files only) for the directory specified in the
' InputBox above.
If Application.OperatingSystem Like "*Win*" Then
Print_File = Dir(Input_Dir) & "*.*"
End If

View 9 Replies View Related

Import Multiple Text Files To 1 Worksheet

Oct 25, 2007

I am trying to convert multiple tab-separated text files into one worksheet. All the text files have the same format and file structure. I have been copying and pasting these files into an Excel spreadsheet but this is time consuming. I need to find out if anyone has any ideas on how to accomplish this task using a VBA macro.

View 3 Replies View Related

Read / Import Text File Into Worksheet

Jan 23, 2008

I want to read data from Notepad into Excel. I found this code on the net and have been trying to modify it for my needs:

Sub ImportText(FileName As String)
Dim X As Long
Dim FileNum As Long
Dim TotalFile As String
Dim Lines() As String
Const DataRowStart As Long = 1
Const DataColStart As Long = 1
FileNum = FreeFile
Open "C:...data.txt" For Binary As #FileNum.................

This pastes the contents of each line into 1 cell but I want to paste each value into a seperate cell. So in the attached data.txt there are 5 records each of 2 lines. For example in the 1st record I want to paste 05-693-1900 into 1 cell then 0040 in the cell to the right of that, Town A into another cell, 000000 into another cell....and so on. For the 2nd line it should be 000000000033 into one cell, AA28816 into the adjacent cell...and each remaining number into a seperate cell. I also want to leave 3 blank lines before going to the next record.data.txt

View 7 Replies View Related

Query/Import Specific CSV File Into Worksheet

Apr 6, 2008

I have been using the attached to pull multiple csv files into seperate sheets. This takes all files from a user specified directory and dumps them into a workbook. I only need three specific files. How do I request specific files instead of a wild card.

View 3 Replies View Related

Import Data From Selected Excel File Into A Specified Worksheet

Mar 17, 2009

I have a workbook which, amongst several worksheets, also includes 12 blank worksheets (one for each month of the year).

Is it possible to have a macro that will (a) first prompt the user to select an excel file to import (the excel files to import are provided each month and only have one tab); then (b) select which worksheet to import the data (values only) from the selected file into.

View 6 Replies View Related

Open File And Import Values Current Worksheet

Sep 26, 2011

I'm a novice at Excel trying to create some forms for work. My goal is to have a working workbook that holds all my values from multiple jobs that gets updated daily with additional worksheets that gets sent to me. I'd like to be able to hit a macro in my workbook that allows me to select a file that has been emailed to me and import their values into mine. For example:

While using my active workbook I want to select a file. Then take cells A1:A15 from selected file and paste to cells F1:F15 in my active workbook.

View 2 Replies View Related

Import Set Range From Multiple Files Into Single Worksheet

Jul 30, 2009

I've got a single folder for the year 2009 that contains multiple files, identical in nature but updated for each business-day. In other words, the same report is generated daily with updated info. The naming convention is the same for every file (i.e. Daily Net Debt Report 02.2.2009.xls)

My ultimate goal is to have a macro that is dynamic enough that if its run on any given day of the year, it pulls the data (specifically from the worksheet "Detailed Cash" cells C1:E26) from every file included in the folder and places it in a single worksheet in the master file. So, for example, data from 1/2/09 would go into cells A1:C26, data from 1/3/09 would go into cells D1:F26, etc...

View 7 Replies View Related

VBA - How To Import Selected Text File To Temporary Worksheet Using Macro

Aug 5, 2013

Below is the code I currently use to do the following which creates service reports.

When you click on the command button you can choose any XML file you want to import. This file is copied to a temporary worksheet where i selectively choose cells to populate textbox's which later populate my reports. What I want to do is the same exact thing but with a text file. Not all text files are the same length so it needs to be able to have a range. Here's my working code.

For Each SheetName In Array("Maintenance Data Sheet")
With Sheets(SheetName)
.Range("B127") = ""
.Range("U127") = ""
.Range("AJ127") = ""
.Range("AV127") = ""
.Range("AY124") = ""

[Code]...

Cancel:

MsgBox "No XML Selected"
End Sub

View 1 Replies View Related

Import CSV Into Worksheet Split Into Columns Then Copy Rows By Criteria

May 26, 2009

I have requirement to extact data into a spreadsheet. This data is extracted from CSV file which is huge normally over 7MB. I have found a macro on the internet which I have included with this post.

Sub split()
Dim ResultStr As String
Dim FileName As String
Dim FileNum As Integer
Dim Counter As Double
'Ask User for File's Name
FileName = InputBox("Please enter the Text File's name, e.g. test.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 .................

I have also included reult data that I get after running the macro. I have cut down on data due to attachement restrictions. But usually there are multiple spreadsheets named "Sheet1, Sheet2, Sheet3 and so on". I have numerous requirements. First of all I want to be able to spreate data into columns. For now how I do that is by going to data -> text to columns and then selcting delmited and then selecting comma as my delimiter. Second thing I want to do is is only extract range of data from this output into a new worksheet. I am only interest in the name like for example ALBANY-Serial0/0/0 and data that is in the range of 8:00 AM to 6:00PM. So the new sheet should only have name and for that name data in the range of 8:00AM to 6:00PM for all the sheets "sheet1, sheet2 etc" until all data has been extracted.

View 7 Replies View Related







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