Excel Spreadsheet Macro

I am somewhat new to VBA in Excel.  I know enough to read and understand what is going on.  But I have been given a work sheet in Excel with a button with the following code.  I was wondering if I could get help fully understanding what is taking place.  I am so far able to see that they are prompting the user to select a word file, then when they do it appears that it converts the word file to text.  From that point on I am questioning if they are saving as text and then parsing the data to import back into an excel spreadsheet.  Could you help me understand this as to what is taking place?  Once it gets imported and saved as text I am unsure exactly what they are doing to parse the data from the word document to extract the text.  Help would be greatly appreciated on this code.  Thanks.

Sub TV_FileImport()

    Dim filetoopen As String
    Dim strDate As String
    Dim strFileName As String
    Dim LastRow As Integer
   
    Application.ScreenUpdating = False
    Application.DisplayAlerts = False
   
    'Creates an open Dialog box for .docx, or .doc
    ChDrive "Q:\"
    ChDir "Q:\dir\tv test\"
       
    filetoopen = Application.GetOpenFilename("Word Documents , *.docx; *.doc")
   
    'Adds the current Date and Time to File Name(excluded the Seconds "ss")
    strDate = Format(Date, "DD MMM YY") & Format(TimeSerial(Hour(Now()), _
        Minute(Now()), Second(Now())), " hhmm AM/PM")
   
    'Saves File in same working directory as selected word document from the _
       open dialog box with TimeStamp
    strFileName = ("Q:\dir\tv_test\test\" & "\" & strDate & ".txt")
                 
    On Error Resume Next
    Set wdApp = GetObject(, "Word.Application")
    If Err.Number <> 0 Then 'Word isn't already running
    Set wdApp = CreateObject("Word.Application")
    End If
    On Error GoTo 0
   
    Set wdDoc = wdApp.Documents.Open(filetoopen)
    wdApp.Visible = False
    wdDoc.Activate
   
    'Saves the file as Plain Text **Note: All Word Formatting Options must be Defined _
            i.e. (wdFormatText should be FileFormat:=2)Thanks David
                       
    wdDoc.SaveAs2 Filename:=strFileName, FileFormat:=2, _
            LockComments:=False, Password:="", AddToRecentFiles:=True, _
            WritePassword:="", ReadOnlyRecommended:=False, EmbedTrueTypeFonts:=False, _
            SaveNativePictureFormat:=False, SaveFormsData:=False, SaveAsAOCELetter:= _
            False, Encoding:=1252, InsertLineBreaks:=False, AllowSubstitutions:=False, _
            LineEnding:=0, CompatibilityMode:=0
    wdDoc.Close
   
       
   
   
    Dim sourceworkbook As Workbook
    Set sourceworkbook = Application.Workbooks.Open(strFileName)
   
   
   
    sourceworkbook.Activate
    Columns("A:A").Select
    Selection.Copy
   
   
    Windows("TV Tracking 2015_1.xlsm").Activate
    Sheets("File Import").Select
    Range("A:A").Select
    ActiveSheet.Paste
    Sheets(1).Activate
    Application.CutCopyMode = False
   
    sourceworkbook.Close SaveChanges:=False
    Kill (strFileName)
   
    Application.Calculation = xlCalculationManual
   
    For i = 1 To 1000
        Sheets("File Import").Range("B" & CStr(i)).Formula = "=trim(a" & CStr(i) & ")"
    Next i
   
    Calculate
   
    Application.Calculation = xlCalculationAutomatic
   
    LastRow = Sheets(1).Cells(Sheets(1).Rows.Count, "B").End(xlUp).Row
   
    Sheets("File Import").Range("C2:L2").Copy
    Sheets(1).Range("B" & CStr(LastRow + 1) & ":K" & CStr(LastRow + 1)).Select
   
    Selection.PasteSpecial Paste:=xlPasteValues
    Selection.NumberFormat = "General"
   
    Columns("K:K").NumberFormat = "m/d/yyyy"
   
    Application.ScreenUpdating = True
    Application.DisplayAlerts = True

End Sub
gwlanksAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

rspahitzCommented:
It looks to me like it's renaming Word docs by adding a timestamp to the name, then loading a text file in Excel and putting a bunch of formulas into column B of a tab called File Import, which removes extra spaces from whatever was copied into column A from another sheet.

What I would suggest is to learn about setting breakpoints.
To do this, go into the VB code and right-click then Toggle | Breakpoint (or click in the gray area to the left of the text window.  You should see a red "stop-light" appear. (Only some lines allow breakpoints.)

When you run the macro, it will stop and wait for more directions.  F5 will resume to the end; F8 will proceed to the next line.  Check the Debug menu for more choices.
gwlanksAuthor Commented:
Yes in the mean time I have gotten all the way through what is being accomplished on the word open and save portion.  I did set a breakpoint actually a few minutes ago to get a copy of the txt file that is being imported.  I have a question on the import of the text document and how they are grabbing each line of text to import.  I am making the assumption that is where the integer is coming into play to count out the lines in the text file and extract those lines of text.  Let me set more break points and see if I can figure out exactly what is taking place.  I will post a follow up question as I go through this but I will try to answer the questions myself before taking your time.  Thanks.
gwlanksAuthor Commented:
Ok I have a question on this portion of code and I think I have digested the remainder:

Sheets("File Import").Range("C2:L2").Copy
    Sheets(1).Range("B" & CStr(LastRow + 1) & ":K" & CStr(LastRow + 1)).Select
   
    Selection.PasteSpecial Paste:=xlPasteValues
    Selection.NumberFormat = "General"
   
    Columns("K:K").NumberFormat = "m/d/yyyy"
   
    Application.ScreenUpdating = True
    Application.DisplayAlerts = True

I look at this and see it is pasting the values and setting the number format to General.  And it is setting column K:K to a date field format.  But I am struggling with how does the data get copied from the "File Import" tab and pasted over to the next blank line on the "TV Tracking Sheet 2015"?
Become a Microsoft Certified Solutions Expert

This course teaches how to install and configure Windows Server 2012 R2.  It is the first step on your path to becoming a Microsoft Certified Solutions Expert (MCSE).

rspahitzCommented:
Looking a bit more, I see that it is opening the document in Word then saving as a text file, then opening that text file in Excel, copying the contents and pasting into another open workbook (TV Tracking 2015_1.xlsm) then deleting (Kill) the file.

Dissecting with end-of-line comments:
Set sourceworkbook = Application.Workbooks.Open(strFileName) ' open the text file
    
    sourceworkbook.Activate ' show this workbook (the text file)
    Columns("A:A").Select' select column A
    Selection.Copy' copy it
    
    Windows("TV Tracking 2015_1.xlsm").Activate' activate another open workbook
    Sheets("File Import").Select' select the File Import tab
    Range("A:A").Select' select column A
    ActiveSheet.Paste' paste it into wherever the active sheet is
    Sheets(1).Activate' now activate the first sheet
    Application.CutCopyMode = False
    
    sourceworkbook.Close SaveChanges:=False' close the text file without saving
    Kill (strFileName)' delete it
    
    Application.Calculation = xlCalculationManual
    
' create 1000 formulas in column B of File Import tab: =trim(A1), =trim(A2), ... = trim(A1000)
    For i = 1 To 1000
        Sheets("File Import").Range("B" & CStr(i)).Formula = "=trim(a" & CStr(i) & ")"
    Next i
    
    Calculate' force a recalculation
    
    Application.Calculation = xlCalculationAutomatic
    
    LastRow = Sheets(1).Cells(Sheets(1).Rows.Count, "B").End(xlUp).Row' determine the last row of column B
    
    Sheets("File Import").Range("C2:L2").Copy' copy cells C2 thru L2 to the clipboard
    Sheets(1).Range("B" & CStr(LastRow + 1) & ":K" & CStr(LastRow + 1)).Select' select first blank row of B across to K
    
    Selection.PasteSpecial Paste:=xlPasteValues
    Selection.NumberFormat = "General"' apply Excel general formatting to the selected cells
    
    Columns("K:K").NumberFormat = "m/d/yyyy"; format column K as a date
    
    Application.ScreenUpdating = True
    Application.DisplayAlerts = True

Open in new window


Hope some of that helps.
BTW...sometimes the debugger interrupts results so you may not always see what occurs when running without the debugger.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
rspahitzCommented:
>pasted over to the next blank line
(see explanation below)

LastRow = Sheets(1).Cells(Sheets(1).Rows.Count, "B").End(xlUp).Row' determine the last row of column B
 ...
Sheets("File Import").Range("C2:L2").Copy
    Sheets(1).Range("B" & CStr(LastRow + 1) & ":K" & CStr(LastRow + 1)).Select
    
    Selection.PasteSpecial Paste:=xlPasteValues

Open in new window

------
Determine the last row of sheet 1 by going to sheet1, counting the number of of rows in column B, using the End(xlUp) function (basically, going to the bottom of column B then pressing Ctrl+UpArrow)) and checking what Row it's on.

then copy cells C2-L2 on the File Import tab

Then on Sheet 1, using the range of B-K, select the last row previously calculated
then paste into the selection.

--
does that help?
gwlanksAuthor Commented:
Excellent feedback I greatly appreciate the help being new to the VBA world and growing my skill level.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.