Access Query from MS Excel

In Microsoft Office 97, the default path for the Northwind database is \Program Files\Microsoft Office\Office\Samples\Northwind.mdb.
A QueryDef is created in Microsoft Access and saved with the database file. It consists of a query and may or may not include criteria.
NOTE: If you receive the "User-defined type not defined" error, activate a module sheet, click References on the Tools menu, and click Microsoft DAO 3.0 Object Library check box.
Retrieving the results of a QueryDef from Microsoft Access is a five step process as follows:

1. Establish a Database object.
   
2. Establish a QueryDef object.
   
3. Establish a Recordset Object.
   
4. Retrieve the Headers (if desired).
   
5. Retrieve the data from the table.
   

After the data is retrieved, you should close all the objects that you opened by issuing .Close commands.

 

Sub GetQueryDef()

'This sub will get data from an Existing QueryDef in the Northwind

'database and place the data on sheet2.

 

Dim Db As Database

Dim Qd As QueryDef

Dim Rs As Recordset

Dim Ws As Object

Dim i As Integer

Dim Path as String

 

'Set the Path to the database. This line is useful because

'if your database is in another location, you just need to change

'it here and the Path Variable will be used throughout the code.

'

'If you're using Microsoft Office 97, the line should read:

'

'Path = "C:\Program Files\Microsoft

'Office\Office\Samples\Northwind.mdb"

'

Path = "C:\Msoffice\Access\Samples\Northwind.mdb"

 

'Set Ws

Set Ws = Sheets("Sheet1")

 

'This set of code will activate Sheet1 and clear any existing data.

'After clearing the data, it will select cell A1.

Ws.Activate

Range("A1").Activate

Selection.CurrentRegion.Select

Selection.ClearContents

Range("A1").Select

 

'Set the Database and QueryDef. This QueryDef exists in the

'database.

Set Db = Workspaces(0).OpenDatabase(Path, ReadOnly:=True, _

Exclusive:=False)

Set Qd = Db.QueryDefs("Invoices")

 

'Create a new Recordset from the Query based on the stored

'QueryDef.

Set Rs = Qd.OpenRecordset()

 

'This loop will collect the field names and place them in the first

'row starting at "A1."

For i = 0 To Rs.Fields.Count - 1

Ws.Cells(1, i + 1).Value = Rs.Fields(i).Name

Next

 

'This line simply sets the font to bold for the headers.

Ws.Range(Ws.Cells(1, 1), Ws.Cells(1, Rs.Fields.Count)).Font.Bold _

=True

 

'The next line will get the data from the recordset and copy it

'into the Worksheet (Sheet1).

 

Ws.Range("A2").CopyFromRecordset Rs

 

'This next code set will just select the data region and auto-fit

'the columns

Sheets("Sheet1").Select

Range("A1").Select

Selection.CurrentRegion.Select

Selection.Columns.AutoFit

Range("A1").Select

 

Qd.Close

Rs.Close

Db.Close

 

End Sub

        

Source: http://support.microsoft.com/default.aspx?scid=http://support.microsoft.com:80/support/kb/ARTICLES/Q147/7/39.asp&NoWebContent=1

Comment Box Shapes and Picture

Bored with the same rectangle yellow comment boxes on your file? Here is a way to change them to shapes of your choice with picture on it (I am Using Excel 2007)

1. Select the cell with the comment
   
2. Press Shift+F2 to edit the comment box
   
3. Select any border of the comment box. You will notice that the box line has full of dots now.
   
4. Right click and select Format comment
   
5. Select "Color and Lines" tab
   
6. Now select "Fill effects" from the color drop down
   

   
   

7. Now from the Picture tab, select a picture from your local drive and add it to the comment box.
   
8. Now the background of your comment box changes to the picture you have selected
   

   
   

9. Before you proceed to change the shape of this comment box, make sure that you have "Change Shape" toolbar activated in the "Quick Access Toolbar". To do this, follow these steps:
   
   1. Select Office button on the top
      
   2. Select Excel Options
      
   3. Select Customize option and select All command from the drop down on the top of this window.
      
   4. Scroll down till you find "Change Shape" command. Add it to the Quick Access toolbar but selecting it and clicking on Add button
      

      
      

   5. Once you are done with it, you will be able to locate "Change shape" icon on the top left near the office button.
      

      
      

10. Follow Steps 1 to 3
    
11. Select a shape from the "Change Shape" options (remember which we added just now).
    
12. You will notice that the comment box shape is changed now.
    

    
    

 

Get rid of GETPIVOTDATA Option

If you want to disable the Generate GetPivotData setting, follow these steps, as appropriate for the version of Excel that you are running.

Microsoft Excel 2003

1. On the Tools menu, click Customize.
   
2. Click the Toolbars tab, and then click to select the PivotTable check box. The PivotTable toolbar is displayed.
   
3. Click Close.
   
4. On the PivotTable toolbar, click the Toolbar Options arrow. This arrow appears on the right end of the toolbar.
   
5. Click Add or Remove Buttons, click PivotTable, and then select Generate GetPivotData.
   
6. Click in the worksheet.
   
7. Click the Generate GetPivotData button that now appears on the PivotTable toolbar. When you click this button, it is turned on or off. If the button is selected, the GETPIVOTDATA formula is automatically generated.
   

Excel 2007

1. Click the Microsoft Office Button, click Excel Options, and then click the Formulas tab.
   
2. Make sure that the Use GetPivotData functions for PivotTable references check box is selected, and then click OK.
   
3. Click in the worksheet.
   
4. Click the Options tab, click Options in the PivotTable group, and then click to clear the Generate GetPivotData check box.
   

Code to export chart as Image to local drive

You can export the chart to your desired location through following code:

Sub exportChart()
Dim cht as chart
Set Cht = Worksheets("Charts").ChartObjects(1).Chart Cht.Export Filename:="C:\Temp\MyChart.gif", FilterName:="GIF"
End Sub
---------------------
I use this code to export chart and load them as image in Userform which looks pretty good for presentation.

Hide all worksheets except Activeworksheet

Sometimes back I posted how to Unhide all the worksheets.
Here is the code to Hide all the sheets except the one which is Active...
--------------------------------------------------------
Sub Hide()
'Hide all sheets macro by Saalim
Dim wkst As Worksheet
Dim sht1 As Stringsht1 = ActiveSheet.Name
For Each wkst In Worksheets
wkst.Activate
If ActiveSheet.Name <> sht1 Then
wkst.Visible = False
End If
Next wkst
End Sub
-------------------------------------------------

Row Counter for Excel

I always wondered if there is a way to count the number of rows that contains information leaving blanks. I was able to do it with following code:

Assume that your data starts from Cell A6. Run the following code:

Sub rowCount()

Range("A6").Select

rowcounter = 6

ActiveCell.Offset(1, 0).Select

Do Until ActiveCell = ""

rowcounter = rowcounter + 1

ActiveCell.Offset(1, 0).Select

Loop

MsgBox ("There are " & rowcounter & " number of rows")

End Sub

Hope this will help

How to write amounts in Lakhs and Crores (Indian Format)?

Have you ever wondered as to how to write amounts in lakhs and crores instead of millions and billions ie 10,00,00,000 format.
Its quite simple :
Right Click the cell you want to format >> format >> Number >> Click Custome (last item on the left hand side Menu).Now type one of the below syntax formats in the "Type:" Text box >> Click OK.
Without decimal : 99,99,99,999[>=10000000]#","##","##","###;[>=100000]#","##","###;#,###
With decimal : 99,99,99,999.00[>=10000000]#","##","##","##0.00;[>=100000]#","##","##0.00;#,##0.00
And now you get amounts in Indian currency format.