Files Stored In An Array

Feb 17, 2010

I'm trying to open multiple files, pass them into an array and then work on them but the code below doesn't work (receive a Run-time error '9' message)

Public aFile As Variant

Sub MultipleFiles ()

MsgBox ("Please select multiple book files (holding CTRL as you click on flies) before you click open")

aFile = Application.GetOpenFilename(FileFilter:="Excel (*.xl*), *.xl*", _
Title:="Select required book level files", MultiSelect:=True)

Dim i As Integer

For i = LBound(aFile) to UBound(aFile)
Workbooks(aFile(i)).Activate
'Do something, then repeat with next file in array
Next i

End Sub

View 9 Replies


ADVERTISEMENT

Retrieving An Array Which Has Been Stored In A Workbook Name

Apr 7, 2007

I've successfully saved an array of string to a workbook name. How do I get it back out as an array of strings instead of as a "Variant/String"?

View 4 Replies View Related

Macro Will Not Work To Process Files Stored On Network Drive?

Aug 1, 2014

I have a macro that works just find on my local machine. but it need to run from a network drive as several different people may need to access it.

A brief description of the macro.

The macro looks in an directory called unprocessed for any file named "*.csv" if the files are found it opens the file in excel and then copies data from that workbook to my workbook. after this the .csv file is closed (unchanged) and then it is moved to the processed directory.

Sub files()
Dim directory As String, filename As String, sheet As Worksheet, i As Integer, j As Integer
Dim filetype As String
Application.ScreenUpdating = False

[Code].....

View 7 Replies View Related

Select Multiple Ranges Stored In Range Array

Nov 19, 2013

I am trying to do something that sounds fairly straightforward but have not been able to achieve the final result.

I have a sheet which has multiple range addresses stored in different cells and so far I have been able to pass the Range address to Range variables by looping through a range array. Now I would like to select these multiple ranges. My code looks as below;

VB:

Dim Cnt As Integer
Cnt = Sheets("Sheet1").(Range("1040").Value ' Cnt holds the the total number of ranges to be specified in Sheet1
Dim RStr(1 To 80) As String ' This will hold the range addresses specified in Sheet1 in cells C1041 to C1120. Upto 80 range addresses could be specified.
[SIZE=2] Dim Rng(1 To 80) As Range ' This will store the ranges based on the range addresses stored in RStr()
Dim i, j As Integer,

[Code]...

I am not sure on how to select multiple ranges using range array hence tried Union() however it is obviously not correct and throws an error.

View 2 Replies View Related

Array Calculation From 2 Files Without Opening Files?

Apr 2, 2014

I have 50 files each with 1000 numbers in column A.

I need to compare every list with every other list and calculate the Pearson function.

I am ok with the vb code to compare every file with everyother.

I can do this by opening each file then closing but it takes too long.

Each of the 50 csv file names is in my destination workbook

I would like to define an array using the file name, then extract 2 lists without opening the files then perform the pearson function and place the value in the destination workbook. (The pearson function just measures the strength of correlation between 2 sets of numbers)

View 4 Replies View Related

VLookup Table Array From Different Files?

Apr 9, 2012

I am making a report with product usages for a given week. There is a file for each week.

At the moment I am using vlookup function and manualy change path to source file every week.

=VLOOKUP($A$1,'A:FilingDataMatrix 2012Production Matrix[Production Matrix 2012-Week15.xlsm]Mon - Ingredients'!$E:$F,2,FALSE)

Now I would like to avoid the part with manual changing. Instead I would like to have an ability to take the file path from separate cell.

View 2 Replies View Related

MultiSelect Files - How To Properly Create Array

Aug 28, 2013

I got the following code:

Code:
Function DateiAuswaehlen() As Variant
Dim FileOpen As Variant, i As Integer
Dir Left(KonvPath, InStr(KonvPath, ""))

[Code]...

Basically, I want the function to return an array, but also work when the user does not choose any file.

View 1 Replies View Related

Count & Rename Files & Save Each To Array

Dec 12, 2006

I have used macro to ftped a couple of files to my desktop and save them in a folder but i have problems performing the following.

1) Count the number of files in this directory "C:Summary_Reports_from_VBA". Note that the number of files is not fixed every time.

2) Rename each files to .txt files. (intially files do not have any extension and we do not know the name of the file until it has been ftped over but all files will end with a term "sorts" eg renaming of "xxxsorts" to "xxxsorts.txt")

3) Save each renamed files in an Array such that i can "opentext" each file in an excel worksheet. So if there's multiple files, there will be multiple worksheets.

View 11 Replies View Related

Change Array To Not Return Errors If Files Are Not Found

Jul 27, 2008

i have this code which looks in a folder and moves all .csv files from folder into workbook. The code uses an Array.

If the code does not find a file in the folder (from the array) it returns an error. How can i modify this so that (if a file is not found from the array) then the code still continues without generating errors?

This is the ....

View 9 Replies View Related

Open Multiple Files From Array Of Filenames If Not Already Open VBA

Sep 15, 2014

I'm trying to open multiple files based on an array of WBnames that are on a 'Dashboard' tab, but I only want to open them if they are not already opened. The code below doesn't seem to check if they are open and just opens everything....

VB:
Sub OpenWorkbooks()
Dim WorkbookOpen()
Dim WBnames() As String 'Array of WorkBooks to be Open
Dim WorkbookCnt As Integer

[Code] .....

View 4 Replies View Related

Where Are Macros Stored

Jun 30, 2006

Where are Macros stored? Are they stored just in the worksheet, or are they stored locally on the machine?

View 3 Replies View Related

Stored Procedures Within Excel

Oct 3, 2013

MS Query can only hold 16 joins and some other restrictive requirements. Has utilized Stored Procedures to execute a query and have the results returned to Excel? Query in Excel seems very limited.

View 2 Replies View Related

CALL STORED PROCEDURE FROM VB

Feb 12, 2009

I need to call a Stored Procedure in a SQL Server from VB, but do not have the minimun Idea about doing it....Could you help me ?...I made a lot of attemps copiying code from other treads and scratch it, but so far results = none.

Server name: VAIO/SERVIDOR
DataBase: CEDRO
I´m using Excel XP.

View 9 Replies View Related

Call Stored Macro

Jul 6, 2009

Any way to actually execute a macro stored as a text file?

Without being long winded - I want to create different macro and physically save them as text files. Once saved - I could call them from a list.

View 9 Replies View Related

Using Workbook Name Stored As A Variable

Jul 27, 2006

I am opening a workbook using a macro. I need to run a macro in the workbook I am opening when it opens. I have created a variable containing the workbook name but don't know how to use it to run the macro.

Dim WBName
WBName = ActiveWorkbook.Name
Application.Run _
"'DIST_91094_EDTABS_DIABETES CARE_07072006.xls'!aStartProcess"

'DIST_91094_EDTABS_DIABETES CARE_07072006.xls'! needs to be replaced by the name stored in WBName

aStartProcess is the name of the macro in that workbook I want to run.

View 9 Replies View Related

SUMIFS And Numbers Stored As Text

May 5, 2014

I do have the following problem:

When I use the SUMIF(S) function and a criteria range that has numbers stored as text (eg: '394) the unequal operator (<>) doesn't work.

=SUMIFS(B:B,C:C,"<>493") doesn't work

I'm able to get the salary of employee 493 by using "493" instead of "<>493", but it seems that with the unequal operator Excel starts treating this ID as number. The only work-around I found so far is to use a wildcard e.g. "<>*493" or "<>493*". Excel then treats it as text again, but this is dirty workaround. What happens if an employee with an ID e.g. 1493 shows up!?

"<>'493" or something doesn't work unfortuantely..

Employee ID

Salary

'250
10000

'493
13000

View 14 Replies View Related

Edit Stored Procedure Query

Jan 30, 2009

I am using the following code to run a Stored Procedure Query. It works perfect bringing back 4 columns of data. But is it possible to make it so that it only brings back 2 columns of data. I need the 2nd and 4th, which are named "DateOf" and "Gen", respectively. Someway to include a select command?

I posted here, without luck: http://www.mrexcel.com/forum/showthread.php?t=367809

View 2 Replies View Related

Delete A Row Based On A Date Stored Within It

Jan 19, 2012

I have a table that extends from A1 to S630 (the number of rows changes as records are added and deleted).

The function to add rows based on the date is fine and I have implemented, but I cannot figure out how to delete a row based on a date.

The date is entered on a sheet called Calendar in Cell J20

so Sheets("Calendar").Range("J20")

and the date is in column B of the table, which has been made using the Table function

Is there a way of scanning through the whole table deleting all the rows with the date entered into J20 on the calendar sheet.

View 5 Replies View Related

Converting Number Stored As Text

Mar 12, 2012

I have a spreadsheet which has over 30,000 records. One column is a row of numbers stored in a General format. Some of these numbers are however stored as Text and have the following Error Check:

"The number in this cell is stored as Text or preceded by an apostrophe"

As a result, when I try to import this sheet into Access, the numbers are not imported and appear as blank cells in the Table.

Is there a way either manually or programatically, format the column so all cells in that column are stored as a number and do not have the issue of being stored as text?

View 4 Replies View Related

SQL Server Stored Procedure Via VBA And ADODB

Feb 13, 2013

I'm creating my first stored procedure ever and it looks relatively good so far. The problem is with passing the parameters, more exactly:

Arguments are of wrong type, are out of acceptable range, or are in confilict with one another

The essential part of the stored procedure looks like:

Code:
CREATE PROC GetUserAuthForApp
@User varchar(7),
@application int
AS

while the essential code calling it from VBA looks like:

Code:
Dim strConn As String 'Connection string to SQL Server
Dim strSQLtoExecute As String 'SQL query string to execute
Dim oConn As ADODB.Connection 'Object for connecting
Dim rs As ADODB.Recordset 'Object for recordset
Dim cmd As ADODB.Command
Dim prmUser As ADODB.Parameter
Dim prmApplication As ADODB.Parameter
Dim stProcName As String 'Stored Procedure name

[code]....

and in that rs.Open comes that error.

What have I done wrong, how do I fix it?

View 9 Replies View Related

Launching A Macro That's Stored In A To Be Opened

Nov 15, 2007

Sub test12()

Set wbABC = Workbooks.Open("C:ABCwithMacro.xls")
Call abcMacro 'this macro is stored in ABCwithMacro.xls
'error - sub or function not defined on 'call abcMacro'

End Sub

View 9 Replies View Related

Find Spaces In Stored Data

Aug 13, 2008

I have a spreadsheet with data and I need to find text with spaces in one of the columns. How do I do that?

View 9 Replies View Related

Sort Numbers Stored As Text Not Working?

Sep 17, 2013

In a spreadsheet I have a text column with entries such as:
2012
10 Times
10.5 Times
101 Times
25 Times
A Pure Text Entry

When I sort by this column I expect to get:
10 Times
10.5 Times
101 Times
2012
25 Times
A Pure Text Entry

Instead I get the 1st order. Why? I've selected 'Sort numbers stored as text' separately so it shouldn't be treating 2012 as a number. Besides I've checked & the cell formatting is Text.

View 3 Replies View Related

Using Data Stored In A Variable To Draw A Shape

Jun 18, 2014

I have some code, in one part of it I am storing a number (between 1 and 8) as Integer. Is it possible to reference this variable to draw some rectangles on a sheet, sort of

"If MonitorDetail (this is the name of the variable) .value = 2 then

Here I place the code to draw the rectangle on the page. The macro currently stops at Monitordetail with a error telling me its a "Invalid Qualifier".

View 1 Replies View Related

Excel VBA Input Box Incorrect Data Being Stored?

Aug 1, 2014

I just want to ask the user to enter the date in a mm/dd/yy format, however, the date received is some how changing.

For example I execute the following code and get 8/01/14 instead of 8/21/14

[Code].....

I have even tried pulling the date from a formatted cell with no luck.

View 5 Replies View Related

Calling Stored Procedure And Pass Through A Paramter?

Apr 27, 2007

Is it possible to call a stored proc from excel and pass through a paramter?

If it is would anyone have a clue how to do this?

View 11 Replies View Related

Excel 2010 :: Numbered Stored As Text?

Dec 6, 2011

I received one excel spreadsheet office 2010 every week from one on the vendor. it has over 55000 rows. I got columns N, O, P & Q. these columns has some value which I use for my pivot tables. However the values in this columns are stores as a text. I select the little small box (information box ) appears on the right side of the cell and right click on that where I get an option to change it to number. This is working well.

However I have over 55000 rows , it takes a lot of times to change all these cells in four columns ( almost over 220,000 cells). some times more than an hours.

I use excel 2010.

View 3 Replies View Related

Using EXCEL Formulas With Data Stored On MS ACCESS?

Jun 5, 2013

I am trying to find out if it possible to directly "access" (no play on words here) data stored on MS ACCESS by using Excel's formulas.

I have a set of tables stored on a Microsoft ACCESS 2010 database, and I want to use Excel to analyze that data, using formulas, such as SUMIF.

Is it possible, once I have established an Jet OLEDB connection from Excel to Access, to then directly exploit the data stored on Access without having to copy the data to Excel, and then use it.

I would like Excel to solely act as a tool to analyze, not to store data.

View 7 Replies View Related

VBA To Import Access Database Stored On SharePoint

Jun 14, 2013

How to import data from an access database, specific tables, to excel using vba; the trick being the database is stored on SharePoint Server 2010.

View 2 Replies View Related

Returning Header Row From Access Stored Query?

May 5, 2014

this example runs a named query in Access but it does not return the header row, just the data.

Code:
Sub RunAccessStoredQuery()
Dim objMyConn As New ADODB.Connection
Dim objMyRecordSet As New ADODB.Recordset
Dim strSQL As String

[Code].....

View 1 Replies View Related







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