Copy/paste info from one file into another

[Abdullah]

Hi Guys,

I'm struggling with an issue. I've got 2 documents,

1)Word file containing a table
2)Notepad file containing info

Basically the table in word has info in it...one column includes names.
The notepad file contains additional info....one field also being names.

I have a macro that creates a table. After the table is completed, I would like a macro to go back and

-scroll down column 1, getting the name of the file in row X (Variable amount of rows),
-search for that same file name in notepad,
-Copy the following 3 lines in notepad
-Paste it below the file name in the word document Column 1 Row X

Any help on getting a macro to do this would be GREATLY appreciated

[Abdullah]

Even help with, if I copied the notepad info and pasted it below the table in the same doc...(as a quick fix). So that way the info would be contained in one file.

Going through the column and then searching/copying/pasting into the correct spot.

Ultimately I would like the keep the files seperate and save the step of copying/pasting into one doc...

But any help would be greatly appreciated

[OTWarrior]

What do you mean by "scroll down column 1, getting the name of the file in row X"...what file name are you referring to?

I don't follow exactly what you are trying to do. Do you have an example of both document for us to see?

[Abdullah]

Sorry for the confusion...lets see if I can explain better...

File 1 (word document [.doc] with table in it) Table example below


File Name 1019001.JPG





PICTURE
File Name 1019002.JPG





PICTURE
File Name 1019003.JPG





PICTURE



File 2 (notepad document [.txt]) with info in it


File Name 1019001.JPG
Camera Model Name DMC-FX50
Shooting Date/Time 10/19/2007 10:09:20 AM
File Size 1074KB
----------------------------------------------------------------------
File Name 1019002.JPG
Camera Model Name DMC-FX50
Shooting Date/Time 10/19/2007 10:09:20 AM
File Size 1000KB
----------------------------------------------------------------------
File Name 1019003.JPG
Camera Model Name DMC-FX50
Shooting Date/Time 10/19/2007 10:09:21 AM
File Size 1153KB


So, I would like to have a macro that copies the three lines below the file name (camera model name, shooting date/time, and file size) from the .txt file and pastes it into the .doc table in the appropriate place.

The table will be of variable legnth, but the number of entries in the table will always match the number of entries in the .txt file.

I want to have the macro search the .txt file for file name, in case the order gets skrewed up. So that if lets say entry 2 and 3 get swapped, it won't skrew up the entire final product.

...I have 3 tables currently...one with 153 entries, one with 271 entries, and the third with 89 entries. So you can imagine how much easier my life would be if I could end up with a macro to help me out.

Hope this explains things better...let me know I need to clarify more

[Abdullah]

The cells didn't translate properly in the above post...file 1 looks more like this

Column 1 (file name) | Column 2 (blank) |Column 3 (picture)
| |
File Name 1019001.JPG | | PICTURE
| |
File Name 1019002.JPG | | PICTURE
| |
File Name 1019003.JPG | | PICTURE

[OTWarrior]

If you were to load the txt file into word, then more options would be available to you.

are the "tables" in the txt document all the same size and will always be the same position?

if so then you could use:

Public Sub cmdCopy()
On Error GoTo error
Set Currentdoc = Word.Application.Documents(1)
With Dialogs(wdDialogFileOpen)
If .Display <> -1 Then
Exit Sub
Else
Set BrowseFile = Word.Application.Documents.Open(WordBasic.FileNameInfo$(.Name, 1), , , , , , , , , , , False)
End If

if (Currentdoc.Tables(1).Cell(1, 1)) = mid(browsefile.Paragraphs(1), 1, 20) then
Currentdoc.Tables(1).Cell(2, 1) = mid(browsefile.Paragraphs(2), 1, 20)
Currentdoc.Tables(1).Cell(3, 1) = mid(browsefile.Paragraphs(3), 1, 20)
else
end if
msgbox "import Successful"

exitsub
error:
msgbox "import error"
end sub

this is untested, but based on code that i have used recently. You would be better off using a case statement (along with making this code more dynamic with what it copies, as the above will only copy 20 characters in each line), but this should give you a head start.

[Abdullah]

I could easily load the txt document into a word file, just select all copy and paste. Would that make much of a difference in the code?

I'm not sure I know what you mean by the "tables" in the txt document being the same size and same position. Its not a table per say...its just a lot of lines with a line of "-" seperating the different entries.

The table in the word document (with the pictures) is an actual table. The number of entries in that table will always be the same as the number of entries in the txt file (that I will copy into a word doc). They entries in both will usually be in the same order (i.e. 1, 2, 3, 4, 5 from top to bottom) But I cannot guarantee this will always be the case (1, 2, 3, 4, 5 in the one but could be 1, 3, 4, 2, 5 in the other).

Unfortunately when I tried running the program, it said import successful, and then import error (I think because the "import error" msgbox is at the end)...however it didn't actually paste anything. I believe the macro ran w/o an actual error.

I put the info from the txt file into a word file, but again, its not in a table, its just a lot of info. I even put some of the info into a table in an attempt to get the macro to run, unfortunately it didn't copy any info over.



Also, I had a question about your one if statement

If (Currentdoc.Tables(1).Cell(1, 1)) = mid(browsefile.Paragraphs(1), 1, 20) Then
Currentdoc.Tables(1).Cell(2, 1) = mid(browsefile.Paragraphs(2), 1, 20)
Currentdoc.Tables(1).Cell(3, 1) = mid(browsefile.Paragraphs(3), 1, 20)

All the info needs to be copied into 1 cell (same cell as, just under the file name).
Currentdoc.Tables(1).Cell([1-3], 1) I was afraid would try and paste a line into cell 1, cell 2, and cell 3...when all 3 lines copied need to be in the same cell.

Just trying to get a few lines from one word file (in an attempt to make things easier) into a cell of a table in another.

[OTWarrior]

when i said "Tables" I meant your txt file lines. I now you can't have tables in a txt file, but the way you laid them out kind of looks like them.

anyway, the code above has a mistake, which should actually be:
msgbox "import Successful"

exit sub
error:
msgbox "import error"
End Sub

i forgot to put a space in. sorry about that

however, if you want the three lines from the txt file to be in the same cell in the word file, try using:

If (Currentdoc.Tables(1).Cell(1, 1)) = mid(browsefile.Paragraphs(1), 1, 20) Then
Currentdoc.Tables(1).Cell(2, 1) = browsefile.Paragraphs(2) & vbcrlf _
& browsefile.Paragraphs(3) & vbcrlf & browsefile.Paragraphs(4)
end if


that way you can combine all three lines into one cell.
the mid function is to take the "middle" of a string value and return it, ie:

string = "Hello there"
a = mid(string, 1 , 5)

in this case a would be:
Hello

as far as I am aware you are not able to use:
Currentdoc.Tables(1).Cell([1-3], 1)

but would have to use:

for a = 1 to 3
Currentdoc.Tables(1).Cell(a, 1)
next a


hopefully that is clearer for you

[Abdullah]

OTWarrior,

I used the new IF statement, but unfortunately it still is not pasting anything. I run the macro and it prompts me to point it at the txt file...which I do and it returns with the import error text box. Reguardless of if I point it to the txt file or to the copied and pasted word file.

I couldn't get it to run without inserting an End With statement at the bottom. Don't know if that matters, or skrewed anything up.

[OTWarrior]

i am sorry about that (problem when i try to code of the top of my head into a non vba envirnoment ;)) yes the end with is needed

public BrowseFile As Word.Document
Public CurrentDoc As Word.Document


Public Sub cmdCopy()

On Error GoTo error




Set CurrentDoc = Word.Application.Documents(1)

With Dialogs(wdDialogFileOpen)
If .Display <> -1 Then
Exit Sub
Else
Set BrowseFile = Word.Application.Documents.Open(WordBasic.FileNameInfo$(.Name, 1))
End If


If (CurrentDoc.Tables(1).Cell(1, 1).Range) = Mid(BrowseFile.Paragraphs(1).Range, 1, 20) Then
CurrentDoc.Tables(1).Cell(2, 1).Range = BrowseFile.Paragraphs(2).Range & vbCrLf _
& BrowseFile.Paragraphs(3).Range & vbCrLf & BrowseFile.Paragraphs(4).Range
End If
MsgBox "import Successful"
End With
Exit Sub
error:
MsgBox "import error"


End Sub






i have tested this, and it works by creating a new document with the text from the txt file (ie: it doesn't work how you want it to).


but it is a step closer :)

[OTWarrior]

or this may be even closer.....

Public BrowseFile As Word.Document
Public CurrentDoc As Word.Document
Public Sub cmdCopy()
On Error GoTo error
Set CurrentDoc = Word.Application.Documents(1)
With Dialogs(wdDialogFileOpen)
If .Display <> -1 Then
Exit Sub
Else
Set BrowseFile = Word.Application.Documents.Open(WordBasic.FileNameInfo$(.Name, 1))
End If

If Trim((CurrentDoc.Tables(1).Cell(1, 1).Range)) = Mid(BrowseFile.Paragraphs(1).Range, 1, 20) Then
CurrentDoc.Tables(1).Cell(2, 1).Range = BrowseFile.Paragraphs(2).Range & vbCrLf _
& BrowseFile.Paragraphs(3).Range & vbCrLf & BrowseFile.Paragraphs(4).Range
Else
CurrentDoc.Tables(1).Cell(2, 1).Range = BrowseFile.Paragraphs(2).Range & vbCrLf _
& BrowseFile.Paragraphs(3).Range & vbCrLf & BrowseFile.Paragraphs(4).Range
End If
End With
BrowseFile.Close
MsgBox "import Successful"
Exit Sub
error:
BrowseFile.Close
MsgBox Err.Description
End Sub

This is only for the one instance, but proves that the code can do the import successfully, the problem is making sure it can read the "Filename" written in the txt file.

[Abdullah]

OTWarrior,

Thanks for the code, it is a lot closer to what I'm trying to do than what I had. One thing I noticed, is it deletes the file name that was in cell 1,1 and pastes all the other info. I tried adjusting it to take the file name from where it gets the other info, by adding

BrowseFile.Paragraphs(1).Range & vbCrLf _

to the if statement

If Trim((CurrentDoc.Tables(1).Cell(1, 1).Range)) = Mid(BrowseFile.Paragraphs(1).Range, 1, 20) Then
CurrentDoc.Tables(1).Cell(2, 1).Range = BrowseFile.Paragraphs(2).Range & vbCrLf _
& BrowseFile.Paragraphs(3).Range & vbCrLf & BrowseFile.Paragraphs(4).Range

It did not work.

I was wondering how to adjust it to either take the file name from where it takes everything else or to not delete the file name from the original cell?

Also, if its not too much trouble, would you mind throwing a couple comments in the code for my benefit? Don't mean to be a bother.

[Abdullah]

I guess I needed to add it to the Else statement instead of the IF

[OTWarrior]

it shouldn't be removing the filename from the cell, as it only reads it.

comments added to code as requested:



Public BrowseFile As Word.Document
Public CurrentDoc As Word.Document
Public Sub cmdCopy()
On Error Goto error
Set CurrentDoc = Word.Application.Documents(1) 'set the name of the file you are importing to
With Dialogs(wdDialogFileOpen)
If .Display <> -1 Then
Exit Sub
Else
Set BrowseFile = Word.Application.Documents.Open(WordBasic.FileNameInfo$(.Name, 1)) 'set the name of the file to be imported, and save for future reference
End If


If Trim((CurrentDoc.Tables(1).Cell(1, 1).Range)) = Mid(BrowseFile.Paragraphs(1).Range, 1, 20) Then 'read the contents of both files in their relevant sections
CurrentDoc.Tables(1).Cell(2, 1).Range = BrowseFile.Paragraphs(2).Range & vbCrLf _
& BrowseFile.Paragraphs(3).Range & vbCrLf & BrowseFile.Paragraphs(4).Range
Else 'should the contents not match, this will be carried out (in your version, make sure it is somehting different)
CurrentDoc.Tables(1).Cell(2, 1).Range = BrowseFile.Paragraphs(2).Range & vbCrLf _
& BrowseFile.Paragraphs(3).Range & vbCrLf & BrowseFile.Paragraphs(4).Range
End If
End With
BrowseFile.Close
MsgBox "import Successful"
Exit Sub 'makes sure the proceedure ends when completed rather than continuing to the error. is a must when using an error handler


error:
BrowseFile.Close
MsgBox Err.Description
End Sub


Hope that makes it easier to understand

NB: the above code asumes that you want to have the filename in cell 1, and the imported text in cell 2. if you want all of the imported info to overwrite the 1 cell then change it to....



If Trim((CurrentDoc.Tables(1).Cell(1, 1).Range)) = Mid(BrowseFile.Paragraphs(1).Range, 1, 20) Then
CurrentDoc.Tables(1).Cell(1, 1).Range = BrowseFile.Paragraphs(1).Range & vbCrLf _
BrowseFile.Paragraphs(2).Range & vbCrLf & BrowseFile.Paragraphs(3).Range & _
vbCrLf & BrowseFile.Paragraphs(4).Range
'rest of code

[figment]

your flipping your cell designations they go Cell(row# , column #)



Public BrowseFile As Word.Document
Public CurrentDoc As Word.Document
Public Sub cmdCopy()
Dim a As Long, b As Long
On Error GoTo error
Set CurrentDoc = Word.Application.Documents(1) 'set the name of the file you are importing to
With Dialogs(wdDialogFileOpen)
If .Display <> -1 Then
Exit Sub
Else
Set BrowseFile = Word.Application.Documents.Open(WordBasic.FileNameInfo$(.Name, 1)) 'set the name of the file to be imported, and save for future reference
End If
End With

For a = 1 To CurrentDoc.tabls(1).Rows.Count
b = 1
While b < BrowseFile.Paragraphs.Count
With CurrentDoc.Tables(1)
If Trim((.Cell(a, 1).Range)) = Left(BrowseFile.Paragraphs(b).Range, Length(.Cell(a, 1).Range)) Then 'read the contents of both files in their relevant sections
.Cell(a, 2).Range = BrowseFile.Paragraphs(b + 1).Range & vbCrLf _
& BrowseFile.Paragraphs(b + 2).Range & vbCrLf & BrowseFile.Paragraphs(b + 3).Range
ElseIf .Cell(2, a) = "" Then 'should the contents not match, this will be carried out (in your version, make sure it is somehting different)
.Cell(a, 2).Range = "No Data"
End If
End With
b = b + 4
Wend
Next

BrowseFile.Close
MsgBox "import Successful"
Exit Sub 'makes sure the proceedure ends when completed rather than continuing to the error. is a must when using an error handler
error:
BrowseFile.Close
MsgBox Err.Description
End Sub
i like the code, i would have taken the problem into excel my self and its interesting to see other ways to do it. along with changing the cell designation i add looping, so that it will do the entire table, i also switched the Mid statment, for the Left stament.

this code is untested so that might be an error or two

[OTWarrior]

First, I haven't got the row and column mixed up, since the filename is above the information in the txt file. hence the row below the filename being where the imported text ends up

Also, I did mention about using a case statement, (i didn't mention that a loop would be needed, as that was going to be later on), but I just wanted to start Abdullah off with the basics, and get it to work first.

[figment]

First, I haven't got the row and column mixed up, since the filename is above the information in the txt file. hence the row below the filename being where the imported text ends up

ah yes sorry i missed that. i had assumed the data would go in the blank column between the file name and the pic

Abdullah dose your table have a blank row at the top like the example shows? if it dose then that is the reason that the macro is overwriteing your file name. you need to index the row number by one to acount for the gap.

[Abdullah]

The table doesn't have a blank row...the first row is the title line, saying what each column is. OTWarrior was correct in assuming that I wanted to start import of info in cell 2.

I sorted out the file name thing, I actually pulled the file name from the txt file in addition to the rest of the info copied from it. It was easier since the text file has that info already.

The code that OTWarrior posted was a huge help, once I saw how he got it working for one row, I put a loop in to fill in the info to the rest of the table. I put a counter into the table creation to get the number of rows and then used a for loop.

Thanks for all the help

[OTWarrior]

Glad to be of help :)