Fail Export CSV

When I open the CSV file I exported, all data appear in 1 column instead of several

It may happen that your spreadsheet editor (Excel for example), when you open the CSV file you just exported from BeeBole, will show the data in one single column instead of nicely splitting them in several columns.

The reason behind this behavior is that CSV files (Comma Separated Values) are splitting data into columns using " , " as separator by default. But in certain countries, the default value used as separator is " ; " instead.

So, what should you do if your computer expect " ; " instead of " , "?

1. Go to your Settings page clicking on the link in the top-right corner of your screen (You have to be an admin)
2. In the Localization module, change the "List separator for exports" field from "coma ( , )" to "semicolon ( ; )"

Alternatively, you might want to change the Regional Settings of your computer if it happens for several app you use and not only BeeBole

Then, in Windows for example:

Go to Start>Settings>Control Panel>Regional and Language Options.
Click “Customize”.
Enter “,” for “List Separator”

source : http://help.beebole.com/home/faq/csv-format-does-not-work-in-excel

Excel: excel Cannot shift object off sheet

Warning : excel Cannot shift object off sheet

Sub Test()
Dim s As Shape
On Error Resume Next
For Each s In ActiveSheet.Shapes
s.Placement = xlMoveAndSize
Next
End Sub

Display Date in two digit Excel to mySQL

=YEAR(L5)&"-"&IF(LEN(MONTH(L5)) = 1,CONCATENATE("0",MONTH(L5)),CONCATENATE("",MONTH(L5)))&"-"&IF(LEN(DAY(L5)) = 1,CONCATENATE("0",DAY(L5)),CONCATENATE("",DAY(L5)))

Toolbar Enable

Sub reset()

Application.CommandBars("Cell").Reset

Application.CommandBars("Cell").enabled = true

End Sub

Function get URL in Excel

Create in macro like function below:

Function GetURL(rng As Range) As String
On Error Resume Next
GetURL = rng.Hyperlinks(1).Address
End Function

then open excel, type in a cell "=GetURL(A1)" for link/url you will capture


after that, you can correction the URL using macro below.

For URL = 6 To 329
AddressName = Cells(URL, 23).value
Range("W" & URL).Select

Selection.Copy
Range("L" & URL).Select
ActiveSheet.Paste
Application.CutCopyMode = False
AddressURL = Cells(URL, 27).value

ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:=AddressURL, _
TextToDisplay:=AddressName
Next

Cannot shift objects off sheet

If you find problem when insert column appears: "Cannot shift objects off sheet"
please click button CTRL+End, then delete the column.

Menghapus Spasi di Excel

Gunakan =TRIM(CELL) untuk menghapus spasi di Excel

Maximum Line

maximum_lines = Range("A1").End(xlDown).Row

Excel to MySQL

Panduan:
http://www.automateexcel.com/2005/11/01/connect_excel_to_mysql_database/
http://apacheguide.org/myodbc.php

Tools:
download myODBC
http://dev.mysql.com/downloads/mirror.php?id=376347#mirrors

VBA Macro for Excel 2003 Export of Text File with Comma and Quote Delimiters.

VBA Macro for Excel 2003 Export of Text File with Comma and Quote Delimiters.
This code is a sample from the MSDN script library at http://msdn.microsoft.com/library/default.asp?url=/library/en-us/odc_xl2003_ta/html/odc_XL_Samples.asp

1. Open a new workbook.
2. On the Tools menu, point to Macro, and then click Visual Basic Editor (or simply press ALT+F11). In the Visual Basic Editor, click the Insert menu and then Module.
3. Type or paste the below sample code into the module.
4. Before running the macro, select the data that you want to export, and then point to Macros on the Tools menu and click Macro.
5. Select the QuoteCommaExport macro, and click Run.
Sample code:
Sub QuoteCommaExport()
Dim DestFile As String
Dim FileNum As Integer
Dim ColumnCount As Integer
Dim RowCount As Integer

' Prompt user for destination file name.
DestFile = InputBox("Enter the destination filename" & _
Chr(10) & "(with complete path and extension):", _
"Quote-Comma Exporter")
' Obtain next free file handle number.
FileNum = FreeFile()

' Turn error checking off.
On Error Resume Next

' Attempt to open destination file for output.
Open DestFile For Output As #FileNum
' If an error occurs report it and end.
If Err <> 0 Then
MsgBox "Cannot open filename " & DestFile
End
End If

' Turn error checking on.
On Error GoTo 0

' Loop for each row in selection.
For RowCount = 1 To Selection.Rows.Count
' Loop for each column in selection.
For ColumnCount = 1 To Selection.Columns.Count

' Write current cell's text to file with quotation marks.
Print #FileNum, """" & Selection.Cells(RowCount, _
ColumnCount).Text & """";
' Check if cell is in last column.
If ColumnCount = Selection.Columns.Count Then
' If so, then write a blank line.
Print #FileNum,
Else
' Otherwise, write a comma.
Print #FileNum, ",";
End If
' Start next iteration of ColumnCount loop.
Next ColumnCount
' Start next iteration of RowCount loop.
Next RowCount

' Close destination file.
Close #FileNum
End Sub

Rumus Dalam Visual Basic

Jika ingin memasukkan rumus dalam visual basic, gunakan sintak berikut ini:

ActiveCell.FormulaR1C1 = "=IF(ISNA(MATCH(R1C[-1],MASTERDATA!R[1]C[" & Read_urut_sheet_master & "]:R[200]C[" & Read_urut_sheet_master & "],0)),0,MATCH(R1C[-1],MASTERDATA!R[1]C[" & Read_urut_sheet_master & "]:R[200]C[" & Read_urut_sheet_master & "],0))"

Create Unique List from Data Entry

How to create unique list from data entry in excel?

1. In Excel, Go to Data > Filter > Advanced Filter
2. Choose under Action : Copy to another location
   
3. Select your data entry where you want to create unique in list range
4. Choose Copy to in column B
5. Check list Unique records only, then click OK
   

Have Fun! :)

Excel Can't Right Click

You can reset the right-click menu.

Hit ALT + F11 to go to the VBEditor.

Then View>Immediate Window.

Copy/paste this line into the window and hit

Application.CommandBars("Cell").Enabled = True

OR copy/paste this sub to a module and run it.

Sub reset()
Application.CommandBars("Cell").Enabled = True
End Sub

The Insert and Format not being available could be because the worksheet or
workbook is protected.....I would vote for worksheet.

Tools>Protection>UnProtect Worksheet.

Source:
http://www.wiredbox.net/Forum/Thread327814_i_cannot_use_the_mouse_right_click_in_excel-_how_to_rectify.aspx

Menampilkan Huruf pada urutan keterangan tertentu

misalnya pada teks suatu sel di Excel A2 tertulis: Jabatan(NAMA)
Bagaimana agar hanya NAMA-nya saja yang tampil?
Gunakan sintak =FIND("(", A2, 35)

PR: wait... I: wait... L: wait... LD: wait... I: wait... wait... Rank: wait... Traffic: wait... Price: wait... C: wait...

Question About Excel

http://www.mrexcel.com/qa.shtml

PR: wait... I: wait... L: wait... LD: wait... I: wait... wait... Rank: wait... Traffic: wait... Price: wait... C: wait...

Mapping Color Index

Mapping Color Index
http://www.mvps.org/dmcritchie/excel/colors.htm

PR: wait... I: wait... L: wait... LD: wait... I: wait... wait... Rank: wait... Traffic: wait... Price: wait... C: wait...

Cell di Excel tidak bisa klik kanan

Saya menemukan kasus, ketika klik kanan di suatu cell di excel tidak bisa tampil menunya.
Langkahnya:
1. tekan ALT + F11, dan akan tampil VBEditor.
2. klik menu View>Immediate Window.
3. ketikkan : Application.CommandBars("Cell").Enabled = True
4. kemudian jalankan
5. Coba apakah menu klik kanan sekarang sudah bisa Anda lakukan? Semoga berhasil :)

PR: wait... I: wait... L: wait... LD: wait... I: wait... wait... Rank: wait... Traffic: wait... Price: wait... C: wait...

Menghitung Sub Total

Untuk menghitung subtotal dalam excel, gunakan rumus seperti contoh berikut:
=SUBTOTAL(9,$Y$4:$Y$10000)

PR: wait... I: wait... L: wait... LD: wait... I: wait... wait... Rank: wait... Traffic: wait... Price: wait... C: wait...