#1
|
|||
|
|||
Macro: Exporting Data to a LEGIBLE Excel Spreadsheet
** Warning: I am a Newbie without programming experience **
I am regularly sent documents like the word document attached: "04172012 Results.docx". My job is to copy and paste each line in the word doc into the attached excel spreadsheet: "04172012 Results Copied.xlsx" However, i KNOW there's a better way than this. I've tried to search/replace the spaces with various characters, then saving it as a text doc, and lastly then importing it into excel, but I just can't get the right combination of characters to make it work. If someone can help me figure out how to get this done in such a way that I can automate, I would be very grateful. Your help for this will save me 30-60 minutes every time i have to do it. I would prefer some sort of macro but being told the steps to do it manually (aside from copy and paste) would be super. Thanks so much!! -Jeff |
#2
|
|||
|
|||
This page of Graham Mayor's may be a place to start.
http://www.gmayor.com/ExtractDataFromForms.htm |
#3
|
|||
|
|||
Quote:
Quote:
|
#4
|
|||
|
|||
This worked (got help from another website
Code:
Sub Export2XL() Dim app As Object Dim wbk As Object Dim wsh As Object Dim r As Long Dim p As Long Dim n As Long Dim i As Long Dim arr As Variant Dim strLine As String ' Start Excel On Error Resume Next Set app = GetObject(Class:="Excel.Application") If app Is Nothing Then Set app = CreateObject(Class:="Excel.Application") If app Is Nothing Then MsgBox "Can't start Excel!", vbExclamation Exit Sub End If End If On Error GoTo ErrHandler ' Create workbook with one worksheet app.ScreenUpdating = False Set wbk = app.Workbooks.Add(Template:=-4167) ' xlWBATWorksheet Set wsh = wbk.Worksheets(1) r = 1 wsh.Cells(r, 1) = "PTF" wsh.Cells(r, 2) = "DEF" wsh.Cells(r, 3) = "PURCHASER" wsh.Cells(r, 4) = "ADDRESS" wsh.Cells(r, 5) = "AMOUNT" ' Date strLine = ActiveDocument.Paragraphs(1).Range.Text p = InStr(strLine, vbTab) strLine = Mid(strLine, p + 1, Len(strLine) - p) wsh.Name = strLine ' Loop n = ActiveDocument.Paragraphs.Count For i = 2 To n strLine = ActiveDocument.Paragraphs(i).Range.Text strLine = Left(strLine, Len(strLine) - 1) arr = Split(strLine, vbTab) ' Determine type If arr(2) = "PTF" Then r = r + 1 wsh.Cells(r, 1) = arr(3) ElseIf arr(1) = "DEF" Then wsh.Cells(r, 2) = arr(2) ElseIf arr(1) = "COUNT" And UBound(arr) >= 3 Then wsh.Cells(r, 3) = arr(3) ElseIf arr(1) = "ADDRESS" Then wsh.Cells(r, 4) = arr(2) wsh.Cells(r, 5) = arr(4) Else ' Footer - ignore End If Next i ExitHandler: If Not app Is Nothing Then wsh.Range("A1:E1").EntireColumn.AutoFit app.ScreenUpdating = True app.Visible = True End If Exit Sub ErrHandler: MsgBox Err.Description, vbExclamation Resume ExitHandler End Sub |
#5
|
||||
|
||||
Since you've evidently cross-posted your query at another forum, please provide a link. For cross-posting etiquette, please also read: http://www.excelguru.ca/content.php?184
__________________
Cheers, Paul Edstein [Fmr MS MVP - Word] |
#6
|
||||
|
||||
Cross-posted at: http://social.technet.microsoft.com/...e-305ef48e7ff4
__________________
Cheers, Paul Edstein [Fmr MS MVP - Word] |
#7
|
|||
|
|||
Quote:
|
Tags |
exporting to exel |
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
* Exporting Access Data to Excel | djreyrey | Excel Programming | 1 | 03-23-2012 10:03 PM |
Importing data from excel using a macro | soma104 | Word | 1 | 04-14-2011 05:10 PM |
Exporting Timline Milstone Data | OTPM | Project | 0 | 04-06-2011 02:34 AM |
Exporting PST data | metfuel | Outlook | 2 | 01-19-2011 04:46 PM |
Exporting data | dsmithers | Outlook | 2 | 06-24-2009 09:58 AM |