So here’s the story: I use LaTeX for creating documents almost exclusively, but making tables are a huge pain. I use Excel all the time for number crunching but I prefer to display results in LaTeX when I’m finished. After doing a bit of googling, I didn’t find anything that I found all that useful, Read MoreRead More
So here’s the story: I use LaTeX for creating documents almost exclusively, but making tables are a huge pain. I use Excel all the time for number crunching but I prefer to display results in LaTeX when I’m finished. After doing a bit of googling, I didn’t find anything that I found all that useful, so I decided to write some VBA to convert Excel tables to LaTeX code. I spent about 15 minutes working on a subroutine and this is what I came up with. (It’s still a work in progress… I’ll post later when I think it’s “complete”) To use it, copy and paste the code into Excel’s VBA Editor, highlight the table you want to convert, run the macro, and your output will be in the VBA Editor’s Immediate Window (View > Immediate Window).
% put this in your preamble \usepackage{booktabs} \usepackage{ctable} % I used this to make the look of captions stand out \usepackage[hang,small,bf]{caption}
Sub Table2Tabular() Dim Xn As Integer Dim Yn As Integer Dim Xi As Integer Dim Yi As Integer Dim strCells() As String Xn = Selection.Rows.Count Yn = Selection.Columns.Count ReDim strCells(Xn, Yn) As String Dim maxLength As Integer For Xi = 0 To Xn - 1 For Yi = 0 To Yn - 1 strCells(Xi, Yi) = Cells(Selection.Row + Xi, Selection.Column + Yi) If maxLength < Len(strCells(Xi, Yi)) + 9 Then maxLength = Len(strCells(Xi, Yi)) + 9 Next Next Dim line As String Debug.Print "\begin{table}[tp]" Debug.Print vbTab & "\label{tab:Type label here.} \centering" line = vbTab & "\begin{tabular}{" For Xi = 1 To Xn If Selection.Columns(Xi).HorizontalAlignment = xlHAlignRight Then line = line & "r" ElseIf Selection.Columns(Xi).HorizontalAlignment = xlHAlignLeft Then line = line & "l" Else line = line & "c" End If Next line = line & "}" Debug.Print line line = "" Debug.Print vbTab & "\toprule" For Xi = 0 To Xn - 1 For Yi = 0 To Yn - 1 If Cells(Selection.Row + Xi, Selection.Column + Yi).Font.Bold = True Then If Yi = 0 Then line = vbTab & vbTab & PadString(" \textbf{" & strCells(Xi, Yi) & "}", maxLength) & " &" ElseIf Yi = Yn - 1 Then line = line & PadString(" \textbf{" & strCells(Xi, Yi) & "}", maxLength) & " \\" Else line = line & PadString(" \textbf{" & strCells(Xi, Yi) & "}", maxLength) & " &" End If Else If Yi = 0 Then line = vbTab & vbTab & " " & PadString(strCells(Xi, Yi), maxLength) & " &" ElseIf Yi = Yn - 1 Then line = line & " " & PadString(strCells(Xi, Yi), maxLength) & " \\" Else line = line & " " & PadString(strCells(Xi, Yi), maxLength) & " &" End If End If Next If Xi = 0 Then line = line & " \toprule" ElseIf Xi = Xn - 1 Then line = line & " \bottomrule" Else line = line & " \midrule" End If Debug.Print line line = "" Next Debug.Print vbTab & "\end{tabular}" Debug.Print vbTab & "\caption{Type caption here.}" Debug.Print "\end{table}" End Sub Function PadString(str As String, length As Integer) As String Dim padLength As Integer padLength = length - Len(str) Dim X As Integer For X = 1 To padLength str = str & " " Next PadString = str End Function
