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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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