1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

update log worksheet to external workbook

Discussion in 'Business Applications' started by steve420, Feb 8, 2010.

Thread Status:
Not open for further replies.
Advertisement
  1. steve420

    steve420 Thread Starter

    Joined:
    Feb 8, 2010
    Messages:
    4
    I have a template form we use for creating job quotes. We've been using the updatelogworksheet macro below to add within the workbook itself, but the other macros that I had to create to reset the main form after every use and general lack of knowledge by other users made this not useful in that earlier logs would be overwritten or the form would not reset properly. Is there a way to create a log of history in a separate workbook to track this separately and maintain the blank template form? macro follows. Using Excel 2003 in XP. Steve Sub UpdateLogWorksheet() Dim historyWks As Worksheet Dim inputWks As Worksheet Dim nextRow As Long Dim oCol As Long Dim myRng As Range Dim myCopy As String Dim myCell As Range 'cells to copy from Input sheet - some contain formulas myCopy = "D12,D17,G14,G16,J48,J51" Set inputWks = Worksheets("ONLY") Set historyWks = Worksheets("sheet1") With historyWks nextRow = .Cells(.Rows.Count, "A").End(xlUp).Offset(1, 0).Row End With With inputWks Set myRng = .Range(myCopy) If Application.CountA(myRng) myRng.Cells.Count Then MsgBox "Please fill in all the cells!" Exit Sub End If End With With historyWks With .Cells(nextRow, "A") .Value = Now .NumberFormat = "mm/dd/yyyy hh:mm:ss" End With .Cells(nextRow, "B").Value = Application.UserName oCol = 3 For Each myCell In myRng.Cells historyWks.Cells(nextRow, oCol).Value = myCell.Value oCol = oCol + 1 Next myCell End With End Sub '
     
  2. Keebellah

    Keebellah Trusted Advisor

    Joined:
    Mar 27, 2008
    Messages:
    5,946
    Better send a workbook with dummy data and we'll take a look.

    The log sheet should of course be available to all using the template sheet.
     
  3. steve420

    steve420 Thread Starter

    Joined:
    Feb 8, 2010
    Messages:
    4
    Here is a sample of how the form is currently. The problem is with a template, the Quotes sheet stays the same every time the template is opened. The form is on a shared network drive, and my intent is to put a tracking list workbook on the same shared drive, so the template form is always the same for users and the tracking form is to check open quotes at a glance. I have tried adding open workbook commands to the macro, but I can't figure out how to get the macro to look at the other workbook. The macro limits itself to the current quotes workbook template or the tracking workbook.
     

    Attached Files:

  4. Keebellah

    Keebellah Trusted Advisor

    Joined:
    Mar 27, 2008
    Messages:
    5,946
    I think I understand you.
    What you should do in your vba module

    Public wba as workbook
    Pubic wbb as workbook

    this way you don't have to Dim them every time.

    Application.Workbooks.Open (<workbookname>), ReadOnly:=True

    where workbookname is the fullpath and filename
    Since you call it from your workbook you can access all te macros and do whatever you need just keeping in mind that you reference the correct workbook(s)

    wba.sheets("quotes")range("A2").value = wbb.sheets(whatever.Range("B3").value
    etc etc.

    I hope you get the idea.
     
  5. steve420

    steve420 Thread Starter

    Joined:
    Feb 8, 2010
    Messages:
    4
    Somewhat. Your last note about the wba.sheets range. Does that go in the string area of the macro.

    Also "wba" and "wbb" are replaced by the actual file names or am I doing that incorrectly?
     
  6. Keebellah

    Keebellah Trusted Advisor

    Joined:
    Mar 27, 2008
    Messages:
    5,946
    in the function, to reference the workbooks:

    set wba = activeworkbook.name
    set wbb = the name of the workbook you are about to open.xls or xlst or the extension it's saved with in offie 2007

    the all you have to do is refer to it

    wba.sheet("A").range("B3").value = wbb.sheet( "X").Range("f6").value

    just try things out.

    You can also record a macro once the other sheet is opened and then see how it's coded.
    That's how I started with macro's

    Bedtime for me now, its'00:16

    I'll read your post tomorrow.
    I have an example somewhere of something similar and I'll send it
     
  7. steve420

    steve420 Thread Starter

    Joined:
    Feb 8, 2010
    Messages:
    4
    I keep getting a type mismatch compile error trying to "set" the destination workbook or an object required runtime error 424. I have the two set as public now, and no trouble setting the active workbook. I have tried opening the destination workbook before set, or placing it after "set" does not seem to make any difference.

    Set wbb = quotetracking.xls.Name

    I have tried including the folders (as used in the open file command), with and w/o the extension, with and w/o the ".name"
     
  8. Keebellah

    Keebellah Trusted Advisor

    Joined:
    Mar 27, 2008
    Messages:
    5,946
    You got yoiur syntax wrong
    An example:

    Dim wkbData As Workbook
    Set wkbData = Workbooks.Open(filename:=ActiveWorkbook.Path & "\KPLTS\Kostenplaats.xls", ReadOnly:=True)
     
  9. Sponsor

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/901375