Excel

VBA

Search last used row/column

Range("a1").Select
totalrows = Selection.CurrentRegion.Rows.Count
totalcolumns = Selection.CurrentRegion.Columns.Count

or
mWSheet1 = (Microsoft.Office.Interop.Excel.Worksheet)mWorkSheets.get_Item("Sheet1");
Microsoft.Office.Interop.Excel.Range range= mWSheet1.UsedRange;
int colCount = range.Columns.Count;
int rowCount= range.Rows.Count;

Reset used range

The used range can be reset by calling the CurrentRegion.Rows.Count:

Private Sub ResetRange
  x = Application.ActiveSheet.UsedRange.Rows.Count
End Sub

Use Excel functions

myvar = Application.WorksheetFunction.Sum(myrange)

Selecting cells

http://support.microsoft.com/kb/291308

Export from Access to Excel

DoCmd.TransferSpreadsheet

Save as XML

Sub ConvertToXML()
  Dim totalRows, totalColumns As Integer
  Dim columnNames(30) As String
  Dim rowCounter, colCounter As Integer
  ' Get rows and columns
  Range("a1").Select
  totalRows = Selection.CurrentRegion.Rows.Count
  totalColumns = Selection.CurrentRegion.Columns.Count

  ' Get fieldnames
  For colCounter = 1 To totalColumns
    columnNames(colCounter) = Cells(1, colCounter)
  Next

  ' Write XML
  Open "C:\Result.xml" For Output As 1
  Print #1, "<?xml version=" & Chr(34) & "1.0"; Chr(34) & " encoding=" & Chr(34) & "utf-8" & Chr(34) & "?>"
  Print #1, "<Records>"
  For rowCounter = 2 To totalRows
    Print #1, "  <Record>"
    For colCounter = 1 To totalColumns
      Print #1, "    <" & columnNames(colCounter) & ">" & Cells(rowCounter, colCounter) & "</" & columnNames(colCounter) & ">"
    Next
    Print #1, "  </Record>"
  Next
  Print #1, "</Records>"
  Close #1
End Sub

Add-ins

Save the file with the code as AddIn (.xla).
To add the program to the Excel-menu, add this VBA code under ThisWorkbook:

Private Sub Workbook_AddinInstall()
  'executed when add-in is installed
  AddVeraMenu
End Sub
Private Sub Workbook_AddinUninstall()
  ' executed when add-in is de-installed
  RemoveVeraMenu
End Sub
Private Const m_strNewMenuName As String = "&Vera"
Private Const m_strNewMenuOption As String = "Maak draaitabel personeel"

Public Sub AddVeraMenu()
  ' adds a VOSA menu item to the menu bar with one sub item to start conversion
  Dim cMenu1 As CommandBarControl
  Dim cbMainMenuBar As CommandBar
  Dim iHelpMenu As Integer
  Dim cbcCutomMenu As CommandBarControl
  RemoveVeraMenu
  '(2)Set a CommandBar variable to Worksheet menu bar
  Set cbMainMenuBar = Application.CommandBars("Worksheet Menu Bar")
  '(3)Return the Index number of the Help menu. We can then use this to place a custom menu before.
  iHelpMenu = cbMainMenuBar.Controls("Help").Index
  '(4)Add a Control to the "Worksheet Menu Bar" before Help.
  'Set a CommandBarControl variable to it
  Set cbcCutomMenu = cbMainMenuBar.Controls.Add(Type:=msoControlPopup, Before:=iHelpMenu)
  ' (5)Give the control a caption
  cbcCutomMenu.Caption = m_strNewMenuName
  '(6)Working with our new Control, add a sub control and give it a Caption and tell it which macro to run (OnAction).
  With cbcCutomMenu.Controls.Add(Type:=msoControlButton)
                   .Caption = m_strNewMenuOption
                   .OnAction = "MainVera"
  End With
End Sub

Public Sub RemoveVeraMenu()
  'removes VOSA item from menu
  '(1)Delete any existing one. We must use On Error Resume next in case it does not exist.
  On Error Resume Next
    Application.CommandBars("Worksheet Menu Bar").Controls(m_strNewMenuName).Delete
  On Error GoTo 0
End Sub

Windows XP:
Save the file in the folder C:\Documents and Settings\Username\Application Data\Microsoft\Addins.
Start Excel, go to Extra - Addins / Invoegtoepassingen and select the Addin.

Windows 7:
Save the file in the folder C:\Users\username\AppData\Roaming\Microsoft\Addins.
Start Excel, go to Bestand - Opties - Invoegtoepassing and select the Addin.

Starting from Excel 2007:
You have to add the AddIn via File - Options - AddIns

Using Excel from VB

Opening Excel

Imports Excel = Microsoft.Office.Interop.Excel
Dim xlsApp As New Excel.Application
Dim xlsWB As Excel.Workbook
Dim xlsSheet As Excel.Worksheet
xlsApp.Visible = False
xlsWB = xlsApp.Workbooks.Open("P:\My Documents\MakeSQL.xls")
xlsSheet = DirectCast(xlsWB.Worksheets(1), Excel.Worksheet)
Dim numberOfColumns As Integer = xlsApp.Selection.CurrentRegion.Columns.Count
Dim numberOfRows As Integer = xlsApp.Selection.CurrentRegion.Rows.Count
Dim count As Integer = 1
Do Until xlsApp.Cells(1, count).value Is Nothing
  dgvExcel.Rows.Add(xlsApp.Cells(1, count).value)
  count += 1
Loop
xlsWB.Close()
xlsApp = Nothing
Unless otherwise stated, the content of this page is licensed under Creative Commons Attribution-ShareAlike 3.0 License