Menu
  • HOME
  • TAGS

Using excel as inputfile in vba (scripting - non MS OFFICE)

Tag: vba,scripting

I'm developing a script in VBA (inside a workspace, not MS office program) that needs to use a small excel file as input. I want to use the data in the excel file and load it into a 2D array so that I can make decisions within my script based on this data. I've tried to google this problem but generally I find problems within excel, but I haven't had any luck finding anything for vba scripting outside MS office.

Can I just use a .Xlsx as inputfile? How do I put this data into a 2d array? My file only has 11 rows & 2 columns.

Thank you for your time.

Best How To :

I think this will also work for Visual Basic [for Applications]; I tried it in Excel VBA and Word VBA:

In VBA Development Environment, select Tools, References. In the list that now appears, check the Object Model you need. In your case you will want to check the Microsoft Excel 12.0 Object Library.

You now have access to the complete Excel Object Model in your application and can open and manipulate an Excel spreadsheet file as you need.

The following code in VB for Word opens an Excel Spreadsheet and gets the value of the first cell:

Sub ExcelTestInWord()
    Dim oExcel As Object
    Dim wb As Object
    Dim v

    'Declare a variable as a FileDialog object.
    Dim fd As FileDialog
    Dim vrtSelectedItem As Variant

    Set oExcel = CreateObject("Excel.Application")

    'Create a FileDialog object as a File Picker dialog box.
    Set fd = Application.FileDialog(msoFileDialogFilePicker)
    If (fd.Show = -1) Then
        For Each vrtSelectedItem In fd.SelectedItems
            Set wb = oExcel.Workbooks.Open(vrtSelectedItem)
            v = wb.Sheets(1).Cells(1).Value
            wb.Close
        Next vrtSelectedItem
    End If

End Sub

How do you delete favorite folders in outlook using VBA

vba,outlook-2007

You have to use the Remove method of the NavigationFolders collection. It takes a NavigationFolder as the argument. There is no Delete method. Sub RemoveAllFavorites() Dim favGroup As NavigationGroup Dim favFldrs As NavigationFolders Set favGroup = Application.ActiveExplorer.NavigationPane.Modules.GetNavigationModule(olModuleMail).NavigationGroups.GetDefaultNavigationGroup(olFavoriteFoldersGroup) Set favFldrs = favGroup.NavigationFolders Do While favFldrs.Count > 0 favFldrs.Remove favFldrs.Item(1) Loop End...

Replace reference with its value in Excel VBA workbook

vba,excel-vba

Re-assign a cell's value to itself in VBA to overwrite the formula/link with the actual value. If NameExists(newSheet, "DelAddress") Then With newSheet.Range("DelAddress") .Value = .Value End With End If ...

Right function vba

vba,excel-vba

try with Mid instead: Mid(enclosedValue, InStr(1, enclosedValue, "*")) ...

Excel-VBA: create named Range in row until end of cell content

vba,excel-vba,range

Sure you can use this snippet to find the last filled cell in a column and use that row number to set your range.name - just replace the "A" with whatever column you'd like. Sub test() Dim lastrow As Integer lastrow = Cells(Rows.Count, "A").End(xlUp).Row Range("A2:A" & lastrow).Name = "RangeColA" End...

Excel Search VBA macro

excel,vba,excel-vba

I hope this can help more. This code may not work 100% but it should be good enough to guide you. Let me know if you have questions. Dim WS As Worksheet Dim Results(7, 1000000) As String ''Didn't know what is a good data type or how many possible results...

Finding position of a particular string containing cell in Excel

excel,vba

One way: Sub qwerty() lastrow = 10 For x = 2 To lastrow If InStr(Sheets("Sheet1").Cells(x, 3), "TFMODE") > 0 Then MsgBox Sheets("Sheet1").Cells(x, 3).Address(0, 0) End If Next x End Sub ...

embed object and display as icon in word document?

vba,ms-word,word-vba

Okay here is start Option Explicit Sub AddFileIcon() SendKeys "%F{TAB 4} {TAB 5}" Dialogs(wdDialogInsertObject).Show End Sub Now you Create Button :-) and call the Macro Here is help full Links VBA SendKeys Word Dialog Boxes ...

Creating a Range in VBA

vba,range

if you asking about Cells multiple range then you can use this: Sub test1() Dim nStart&, nEnd& Dim Rng As Range nStart = 5: nEnd = 9 Set Rng = Range("A" & nStart) While nStart <> nEnd Set Rng = Union(Rng, Range("A" & nStart + 1)) nStart = nStart +...

Excel VBA User-Defined Function: Get Cell in Sheet Function was Called In

excel,vba,excel-vba,user-defined-functions

You need to use Application.Caller. This will return the value in cell A1 of the sheet the function is entered to: Public Function DisplayCaller() As String DisplayCaller = Application.Caller.Parent.Cells(1, 1) End Function This will return the name of the calling sheet: Public Function DisplayCaller() As String DisplayCaller = Application.Caller.Parent.Name End...

Hiding #DIV/0! Errors Using IF and COUNTIF in Excel 2010

excel,vba,if-statement

Please consider the following formula to resolve your issue: =IFERROR((COUNTIF('Sheet 3'!B4:F4,"N"))/(COUNTIF('Sheet 3'!B4:F4,"Y")+(COUNTIF('Sheet3'!B4:F4,"N"))),0) Regards,...

VBA data type to store Range().Characters()

vba

You need to set the variable and the index is 1 based not 0 Dim chars As Characters Set chars = Range("A2").Characters(1, 4) chars.Font.Color = vbRed ...

Compare two data sets in different sheet and print difference in other sheet using excel VBA?

excel,vba

Here is the code that will compare sheet1 and sheet2(corresponding cells ) and according wite the correct value or Mismatch based upon the result into sheet3. Sheet1 and sheet2 wil have same number of rows and columns and the headers be same so you can keep them as it is...

Looping variables

perl,scripting

As per my comment, most of the functionality of your program is provided by the Math::Vector::Real module that you're already using It looks like you want the angle in degrees between successive pairs of 3D vectors in your file. This code creates vectors from each line in the file until...

Exit Sub And Call another Sub

vba,excel-vba,call

but you're so close! sub1 . . If x=y Then Call sub2 Exit Sub End If . . End Sub ...

Label linked to a text box value

excel,vba,textbox,label

put your code under the textbox_change event Following works fine Private Sub TextBox1_Change() If Me.TextBox1.Value < 0 Then Me.Label1.Visible = True Else Me.Label1.Visible = False End If End Sub ...

Sort multiple columns of Excel in VBA given the top-left and lowest-right cell

excel,vba,excel-vba,sorting

I got the solution after going through many tutorials and hence posting here for reference of any one who needs help. Sub testSort() Dim CurrentSheet As Worksheet Set CurrentSheet = ActiveSheet lastRows = CurrentSheet.Cells(Rows.Count, 1).End(xlUp).Row lastCols = CurrentSheet.Cells(1, Columns.Count).End(xlToLeft).Column Set sortA = CurrentSheet.Range(Cells(2, 1), Cells(lastRows, lastCols)) CurrentSheet.Sort.SortFields.Clear CurrentSheet.Sort.SortFields.Add Key:=Range(Cells(2, 2),...

Converting ADODB Loop into DAO

excel,vba,ms-access,ado,dao

DAO might be a little faster, but not materially. Instead, use an IN clause in your SQL Statement so you only have to do it once. Sub test() Dim vaIds As Variant Dim sSql As String vaIds = Split("1 2 4 7 200 205 654", Space(1)) sSql = "SELECT [Sales]...

Copying sheet to last row of a sheet from another workbook

excel,vba,excel-vba

This is what I mentioned in my comment Note: in future, you can using for loop to go through the column index. Option Explicit Dim WB1 As Workbook Dim ws1 As Worksheet Private Sub copylog3() Dim lRow As Long Dim NextRow As Long, a As Long Dim i As Integer...

VBA - Unable to pass value from Private to Public Sub

excel,vba,excel-vba

In your calling code: ThinksCommerciallyInt = 1 should be ThinksCommerciallyInt := 1 similarly for the other parameters...

Extract All Named Ranges Into A Class

vba,excel-vba

Get Named Range by String Why not a simple procedure like so: Function GetNR(namedRange as String) as Range Set GetNR = ActiveWorkbook.Names(namedRange).RefersToRange End Function Then simply get the named range like so: Sub Example() Debug.Print GetNR("NAME").Value End Sub Named Range Suggestion in VBA Project Alternatively if you want the names...

Removing Alert When Using DeleteFile API

vb.net,vba,api,delete

There are several SHFILEOPSTRUCT.fFlags options you'll want to consider. You are asking for FOF_NOCONFIRMATION, &H10. You probably want some more, like FOF_ALLOWUNDO, FOF_SILENT, FOF_NOERRORUI, it isn't clear from the question. Check the docs.

If cell value starts with a specific set of numbers, replace data

excel,vba,excel-vba

Use the LEFT() function, as shown below: lastRow = Range("A" & Rows.Count).End(xlUp).Row colNum = WorksheetFunction.Match("Number", Range("A1:CC1"), 0) For Each c In Range(Cells(2, colNum), Cells(lastRow, colNum)) If LEFT(c.Value,3) = "614" _ Or LEFT(c.Value,3) = "626" _ Or LEFT(c.Value,3) = "618" _ Or LEFT(c.Value,3) = "609" _ Or LEFT(c.Value,3) = "605" Then...

Specifying a range to be applied to all subs in a module?

excel,vba

Create a special sub to perform the initialization and run it first: Dim DataSheet As Range Sub RunMeFirst() Set DataSheet = ThisWorkbook.Sheets(1).Range("A3:FU5002") End Sub ...

Finding the average of a column excluding certain rows using AWK

linux,bash,awk,scripting

Through awk, $ awk '$5!="99999"{sum+=$5}END{print sum}' file 227.5 Explanation: $5!="99999" if 5th column does not contain 99999, then do {sum+=$5} adding the value of 5th column to the variable sum. Likewise it keeps adding the value of 5th column when awk see's the record which satisfies the given condition. Finally...

Copying a Range from Excel and Pasting it into Powerpoint NOT as a metafile but as a table which you can edit in PPP

vba,excel-vba,powerpoint-vba

There appears to be no corresponding method in the PowerPoint object model. The only way to do this is to call the ribbon button itself: ActiveSheet.Range("d51:d57").Copy newPowerPoint.CommandBars.ExecuteMso("PasteExcelTableSourceFormatting") BTW: To find the list of ribbon buttons, search for "Office 2010 Control IDs"....

How do I get a cell's position within a range?

excel,vba,excel-vba

updated using variant provided by lori_m But I wonder if there are any native functions ... use this Sub test() Dim r As Range, c As Range With Sheet1 Set r = .[B2:E10] Set c = .[C2] End With If Not Intersect(r, c) Is Nothing Then Debug.Print "Column in sheet:...

Find column with unique values move to the first column and sort worksheets

excel,vba,excel-vba,sorting

To find and move the "ID" column like 1th column Sub movecolumn() Dim sht As Worksheet Dim keySrc As String Dim lastcol As Long, cutCol As Long Dim arrcol As Variant Set sht = ThisWorkbook.Worksheets("Sheet1") keySrc = "ID" lastcol = sht.Cells(1, sht.Columns.Count).End(xlToLeft).Column 'find the last Headers columns arrcol = Range(Cells(1,...

Userform is not unloading once command button is pressed

vba,excel-vba

There are a couple problems with the code you posted. After the If ComboBox1 = "ROW" Then ... Else block of code you've got an End Sub but no End If. You definitely need to add the End If and I suspect you should remove the End Sub. You've got...

Range, Select, Change contents, Allignment or Offset?

excel,vba,excel-vba

This can be simplified pretty easily to affect the cells from 2 to the current row, six columns to the right- Sub tester2() Dim col As Integer col = ActiveCell.Column + 6 Dim row As Integer row = ActiveCell.row Dim rng As Range Set rng = Range(Cells(2, col), Cells(row, col))...

Parsing the text file line-by-line using batch script (batch file)

windows,batch-file,scripting,cmd

you don't need to parse the file line by line. @echo off :START cls echo. set /p "cho=Enter a word: -> " findstr /i "\<%cho%\>" yourwords.txt >nul 2>&1 if %errorlevel%==0 ( echo. echo Sorry! that word is repeated word. echo. ) else ( echo. echo That is a new word....

VBA - .find printing wrong value

excel,vba,excel-vba

SOLUTION: needed to change the function for finding the values under the headers '(8) 'Get the Values from columns with specified headers Function GetValues(ch As Range, Optional vSplit As Variant) As Scripting.Dictionary Dim dict As Scripting.Dictionary Dim dataRange As Range Dim cell As Range Dim theValue As String Dim splitValues...

VBA how to initialize vCPath

vba,excel-vba,excel-2010

In your original code you've got this block: ' Open the file dialog With Application.FileDialog(msoFileDialogOpen) .AllowMultiSelect = True .Show ' Display paths of each file selected For lngCount = 1 To .SelectedItems.Count Next lngCount For Each strFilename In .SelectedItems MsgBox strFilename Next End With Which already does what you want....

Excel VBA Program Code by Using Randomize Timer

vba

Your second ElseIf statement can never become true. First you check if num1 is bigger or equal than 50: If num1 >= 50 Then grade = "B" Cells(1, 2).Value = grade Imagine if num1 equals 49, then the next ElseIf will get executed. This checks if num1 is smaller or...

Using VLOOKUP formula or other function to compare two columns

mysql,excel,vba,date

If data in your first table starts at A2, and your other column starts at D2, then use in E2 =VLOOKUP(D2,$A$2:$B$17,2,0) Copy down as needed....

Errors 91 and 424 when iterating over ranges in Excel VBA

excel,vba,excel-vba,range

I think the error is because, as mentioned in the comments, that your "for each" isn't being used correctly. Try this: Dim cel Set nonZeroes = Range(Cells(1, 1), Cells(10, 1)) ' You need to set the range to search through here. For Each cel In nonZeroes question = isTouching(cel.Value, firstfeat)...

Interface Controls for DoEvent in Excel

excel,vba,excel-vba,loops,doevents

How about changing your 'do until' loop to a 'for next' loop? Something like?... Sub rowinput() Dim lngInputStartRow As Long Dim lngInputEndRow As Long Dim row_number As Long lngInputStartRow = Range("A1").Value 'specify your input cell here lngInputEndRow = Range("A2").Value For row_number = lngInputStartRow To lngInputEndRow DoEvents Next row_number 'Then a...

How to create a date using strings in vba?

vba,ms-access,access-vba,ms-access-2003

DateSerial should make this easier. Give it values for year, month, and day in that order, and it will give you back the corresponding date as a Date/Time value. for dt = DateSerial(Year(Date), 1, 1) to DateSerial(rs!dan, 2, 2) msgbox dt Next ...

Formatting specific part of text string in VBA

excel,vba,excel-vba,outlook,format

HTML tags do work. I don't know why you say they don't. sBody = "All,<br /><br />Please Approve attached request for " & rType & ".<br /><br /><strong>Customer:</strong> " & customer & "<br />" then instead of the .Body property, use .HTMLBody .HTMLBody = sBody ...

How do I do to count rows in a sheets with filters? With a suppress lines

excel,vba,filter

Try the following which uses the SpecialCells() method to select only cells that are currently visible on screen (i.e. not filtered out). count = Application.WorksheetFunction.CountA(Range("A:A").SpecialCells(xlCellTypeVisible)) ...

ByRef Argument type mismatch on just changing the variable position in declaration

vba,byref

It is as simple as this : Dim ArrayLength As Integer, _ IDvariable As Integer, _ PreArrayLength As Integer, _ IDComparisonResult As Integer Because when you use Dim, you have to specify for each variable what type is it, the previous code was declaring the first three as Variant and...

Using date in CreateQueryDef

vba,date,ms-access

Dates in Access needs to be surrounded by the # tags so that it recognizes the date you have passed. The other important factor to consider is that JET requires the date format to be mm/dd/yyyy as opposed to the normal dd/mm/yyyy. So your problem is because you are using...

Referencing a new inserted column Excel VBA

excel,vba,excel-vba

You can use Indirect() For example 'AUA Summary'!$D$9 can be written as INDIRECT("'AUA Summary'!$D$9") This way even when the columns move, it will refer to the same cell. The other way is to use Index For example D9 in Excel 2007+ can be written as INDEX(1:1048576,9,4) or INDEX(INDIRECT("1:" & ROWS(A:A)),9,4)...

adding variables into another variable vba

excel,vba,excel-vba

I can't see anything wrong with the code, as long as your text is in column C, and the values are in column H I've also taken the liberty of rewriting the code to make it clearer: Sub test() Dim x As Long Dim y As Long Dim TotalValue As...

Excel 2013 Add a Connector Between Arbitrary Points on Two Different Groups

excel,vba,excel-vba

You explained everything very well, and the images you uploaded helped What your code is doing seems to be correct, but the error is complaining about one of the parameters, and it could be the 2nd one: .BeginConnect ConnectedShape:=firstRect, ConnectionSite:=1 ConnectionSite: "A connection site on the shape specified by ConnectedShape....

Activecell not in Array

vba

You have fallen victim to the odd behavior of WorksheetFunction.Match when it cannot find a match. Instead of returning the error, it throws a run time error which gums up the works. Since the premise of this question is searching for whether or not something is in a list, you...

VBA “Compile Error: Statement invalid outside Type Block”

excel,vba,excel-vba,excel-2010

You have: Dim RangeNOut as Double Dim RangeNOut as Integer While the IF statements in there are a nice idea, VBA will not allow you to do that. It doesn't do conditional 'compilation' since it isn't a compiled language. When VBA runs your code, all your variables are declared (no...

Run various queries based on a combo box selection

vba,ms-access,drop-down-menu,access-vba

Create a query which retrieves the QryName values from rows whose SubscriptionID matches the dropdown selection ... a query something like this: SELECT QryName FROM tbl_subcription WHERE SubscriptionID = [dropdown] ORDER BY QrySequence; Then you can open a DAO.Recordset based on that query, move through the recordset rows, and execute...

EXCEL VBA: How to manupulate next cell's (same row) value if cell.value=“WORD” in a range

excel,vba,excel-vba

If cell.Value2 = "FOUND THE CELL" Then cell.Offset(0, 1).Value2 = "changed the next right side cell" cell.Offset(0, 2).Value2 = "changed the second right side cell" End If ...

I need help setting the RecordSource of a Report within a VBA Function

vba,ms-access,access-vba

You need to shuffle the order: Function PrintMod() Dim Source As String Select Case Forms![Search Form]!Subform1.SourceObject Case "Query.SearchQuery" Source = "SELECT * FROM SearchQuery" Case "Query.Part Number Query" Source = "SELECT * FROM [Part Number Query]" Case "Query.Keyword Query" Source = "SELECT * FROM [Keyword Query]" Case "Query.ROP Query" Source...

excel search engine using vba and filters?

excel,vba

In order to filter for "any" column, you could combine a Find result and Filter like this: Sub DateFilter() Dim nRow As Range Dim toSearch As Range 'hide dialogs Application.ScreenUpdating = False 'filter for records that have June 11, 2012 in column 3 Set toSearch = Range("A1:C4") 'detect row that...