Convert visa pdf statements to three columns in excel?
 

hi,

I would like to download data from a pdf visa statement into an excel spreadsheet. (My visa company will only download three months of statements into excel, but the statements I'm interested in are older than that.)

When I try cut and paste, the three fields of data end up in one column, I would like the data in three excel columns (date, company, amount).

Does anyone know of a program that will take the data and re-align it as three columns. Or another automated solution?

e.g. It looks like this:

12/08/15
Company A
$10
12/11/15
Company B
$20
12/14/15
Company C
$30

Instead of like this (in three columns):

12/08/15 Company A $10
12/11/15 Company B $20
12/14/15 Company C $30

Convert visa pdf statements to three columns in excel?
 

Hi Imogen,

Am Tue, 15 Dec 2015 23:16:49 -0800 (PST) schrieb imogen b:

12/08/15
Company A
$10
12/11/15
Company B
$20
12/14/15
Company C
$30

Instead of like this (in three columns):

12/08/15 Company A $10
12/11/15 Company B $20
12/14/15 Company C $30

try following macro in a copy of your sheet:

Sub PDFtoExcel()
Dim varData As Variant, varOut() As Variant
Dim LRow As Long, i As Long, j As Long, n As Long

With Sheets("Sheet1")
LRow = .Cells(Rows.Count, 1).End(xlUp).Row
varData = .Range("A1:A" & LRow)
ReDim Preserve varOut(UBound(varData) / 3 - 1, 2)
For i = LBound(varData) To UBound(varData) Step 3
For j = 0 To 2
varOut(n, j) = varData(i + j, 1)
Next j
n = n + 1
Next i
.Range("A1:A" & LRow).ClearContents
.Range("A1").Resize(UBound(varOut) + 1, 3) = varOut
End With
End Sub


Regards
Claus B.
--
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional

Convert visa pdf statements to three columns in excel?
 

Hi Imogen,

Am Tue, 15 Dec 2015 23:16:49 -0800 (PST) schrieb imogen b:

12/08/15
Company A
$10
12/11/15
Company B
$20
12/14/15
Company C
$30

Instead of like this (in three columns):

12/08/15 Company A $10
12/11/15 Company B $20
12/14/15 Company C $30

look he
https://onedrive.live.com/redir?resi...=folder%2cxlsm
for "PDFtoExcel" and download the file because macros are disabled in
OneDrive.
In the workbook are two suggestions. One with VBA and one with formula.


Regards
Claus B.
--
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional