Require VB Code to send update info to 2nd workbook in excel

[Randy11]

Would like some assistance with VB coding to do the following with excel (2007 version):
Have two spreadsheets:
1) Tracker
2) Investigations (there will be many of these that will be named differently)
In the 2nd (workbook(s)) Would like to embed VB code with a User button to indicate when they are complete an investigation.
Would like to send the following information to the 1st Tracker spreadsheet:
File Name Updated, Date of Update, User Name who updated the investigations file.
Tracker spreadsheet has been set up with an append flag that can be used to determine which row can be used to drop info in. Column C contains this flag. Columns D, E, F will take the info needed
Would like this to occur without opening the Tracker workbook & with out the screen updating flash issues.
Appreciate any assistance you can provide

 

[Gruuuu]

Why not have the Investigations write to a text file that the Tracker file reads upon opening?

private sub btnFinish_Click()
Dim fileSys As Object
Dim txtStream As Object
Dim logFile As String

logFile = "X:\NetworkDrivePath\target.csv"

Set fileSys = CreateObject("scripting.filesystemobject")
Set txtStream = fileSys.OpenTextFile(Filename:=logFile, iomode:=8, create:=True, Format:=-2)

txtStream.WriteLine (ActiveWorkbook.Name & "," & Format(Now(), "mm/dd/yyyy") & "," & Application.Username)

Then set up your tracker to Import External Data

 

[SkipVought]

Hi,

Investigations (there will be many of these that will be named differently)

Do you intend to make this workbook a TEMPLATE?

Tracker spreadsheet has been set up with an append flag that can be used to determine which row can be used to drop info in.

WHY? Would it not be the next available row in the table?

Would like this to occur without opening the Tracker workbook & with out the screen updating flash issues.

The workbook must be opened in some way for this to happen. There are ways to keep the "flashing" from occuring.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Randy11]

2nd workbook is essentially a locked down workbook and will be used as a template, yes this will have mutiple names (123 HRS, 345 RSW etc). As you suggest Skip, wanting this to put the information in the next available row. The solution indicated by Gruuu does work for CSV format but ideally would like direct to a excel workbook. In this workbook there are some lookups etc to complete additional info inportant to person responsible for tracking updates.
1st workbook (Tracker) can open as long as user of 2nd workbook can not see this & saves and closes again.
Appreciate your help.

 

[SkipVought]

application.screenupdating = false

Copy the data you want to transfer

Open the first workbook SET to an object variable wb

wb.Sheets("Tracker").[A1].End(xldown).offset(1).pastespecial xlpasteall

Save wb
Close wb

application.screenupdating = true



Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Randy11]

Sorry not that great with VB. This is the info I am looking to drop into Tracking WB from 2nd WB. Are you are to spell out a bit more for me the code required. There is an apend flag colum in the Tracker workbook so it can be determine next line available for an entry. Info below would be placed in columns 4,5,6 & 7. Flag is in column 3. Appreciate your help Skip and others.

ActiveWorkbook.Sheets("Reference").Cells(ActiveWorkbook.Sheets("Reference").Cells(1, 1).Value, 4) = ActiveWorkbook.Name

ActiveWorkbook.Sheets("Reference").Cells(ActiveWorkbook.Sheets("Reference").Cells(1, 1).Value, 5) = Now()

ActiveWorkbook.Sheets("Reference").Cells(ActiveWorkbook.Sheets("Reference").Cells(1, 1).Value, 6) = Application.UserName

ActiveWorkbook.Sheets("Reference").Cells(ActiveWorkbook.Sheets("Reference").Cells(1, 1).Value, 7) = ActiveWorkbook.Name

 

[SkipVought]

Here's the code

    Application.ScreenUpdating = False

'Copy the data you want to transfer
    Range(YourCopyRange).Copy
    
'Open the first workbook SET to an object variable wsREF
    
    With Workbooks("Tracker.xls")
        .Sheets("Reference").[A1].End(xlDown).Offset(1).PasteSpecial xlPasteAll

        .Save
        
        .Close
    End With
    
    Application.ScreenUpdating = True

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}