Set other workbooks while writing to ThisWorkbook

[Nicole Seibert]

Hi,
I am trying to create a log with a hyperlink to saved data extracts from a
SQL server upon refresh of the data. I refresh the data upon opening the
workbook, so the macro is located/written on This Workbook. However, the log
is in another workbook. I am having trouble setting the log workbook in the
'upon open' macro -- if that makes any sense.

Now this is a problem only because I want to find the last line of the log,
of course. Whenever, and it doesn't matter if I write a separate macro and
call that macro into the 'upon open' macro, I request a count of the rows of
the log workbook I get the row count for the data extract workbook.

Any suggestions?

Thanks,
N

 

[comparini3000]

i don't know how to do this, but this might help:

http://www.j-walk.com/ss/excel/tips/tip82.htm

 

[Jim Thomlinson]

This should be close

dim wbkLog as workbook
dim rngLastlog as range

on error resume next
set wbkLog = workbooks('Log.xls')
on error goto 0

if wbklog is nothing then _
set wbklog = workbooks.open("C:\Log.xls")

set rnglastLog = wbklog.sheets("Sheet1").cells(rows.count, "A").end(xlup)
msgbox rnglastlog.row

 

[Nicole Seibert]

I am posting all of my code here. You can see I am trying to set three
different workbooks within this macro. And count the rows for two the other
workbooks. I am a little over half way finished with this macro, so be
patient with the code.

Further, there is another error that someone might help me with. I can't
figure out how to set the password to refresh the data. This macro will be
running at 3am so, no, I won't be here to input the password.


Option Explicit

Private Sub Workbook_Open()
'Upon open this should refresh data

Dim i As Integer, t As Integer
Dim wbname As Variant
Dim wb As Workbook, data As Workbook
Dim qrow As Integer

Set data = Workbooks("TER Master Data Extraction")

'Refresh Data
'Sheets("Source Data").Select
'Cells.Select
'Selection.QueryTable.Refresh BackgroundQuery:=True

Range("A1").Select

Application.CutCopyMode = False
ActiveSheet.UsedRange.Select
Selection.Copy
Workbooks.Add
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone,
SkipBlanks:= _
False, Transpose:=False

wbname = "TERData " & Format(Date, "mm-dd-yyyy") & " " & Format(Time,
"h.mm.AM/PM")

ChDir "D:\documents and settings\nseib\Desktop\Time Exception Reports"
Workbooks.Open Filename:= _
"D:\documents and settings\nseib\Desktop\Time Exception Reports\Log
TER Data Extraction.xls"
Range("O1").Select
ActiveCell.Value = wbname
ActiveWorkbook.Save
ActiveWorkbook.Close

Application.CutCopyMode = False
ActiveWorkbook.SaveAs Filename:= _
"D:\TERData\" & wbname & ".xls", FileFormat:=xlNormal, Password:="", _
WriteResPassword:="", ReadOnlyRecommended:=False, CreateBackup:=False

Set wb = Workbooks(wbname)

Call LogDataExtraction

wb.Activate

Range("A1").Select
qrow = wb.UsedRange.Rows.Count
MsgBox qrow

Rows("1:1").RowHeight = 24.75
Rows("1:1").Select
With Selection
.HorizontalAlignment = xlGeneral
.VerticalAlignment = xlBottom
.WrapText = True
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Range("A2").Select

Columns("A:A").ColumnWidth = 19.29
Columns("B:B").ColumnWidth = 17.71
Columns("C:C").ColumnWidth = 15.71
Columns("D").ColumnWidth = 23.43
Columns("E:E").ColumnWidth = 21
Columns("F:F").ColumnWidth = 19.57
Columns("G:G").ColumnWidth = 25.86
Columns("H:H").ColumnWidth = 25.86
Columns("I:I").ColumnWidth = 23.43
Columns("J:J").ColumnWidth = 56.14
Columns("K:K").ColumnWidth = 46
Columns("L:L").ColumnWidth = 22.71
Range("J1").Select
Selection.AutoFilter
Range("A11209").Sort Key1:=Range("J1"), Order1:=xlAscending, Header:= _
xlGuess, OrderCustom:=1, MatchCase:=False,
Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal

'ActiveWorkbook.Save
'ActiveWorkbook.SendMail Array("(e-mail address removed)", "
(e-mail address removed)"), _
"Time Exception Data"
'ActiveWorkbook.Close
End Sub

Private Sub LogDataExtraction()
'Updates Refresh Log
'Time and Date stamp will come from this page
Dim log As Workbook
Dim qrow As Integer
Dim wbname As String

ChDir "D:\documents and settings\nseib\Desktop\Time Exception Reports"
Workbooks.Open Filename:= _
"D:\documents and settings\nseib\Desktop\Time Exception Reports\Log
TER Data Extraction.xls"

Set log = Workbooks("Log TER Data Extraction")

log.Activate
Range("A1").Select
qrow = Workbooks("Log TER Data Extraction").Sheets("Refresh
Log").Cells(Rows.Count, 1).End(xlUp).Row
MsgBox qrow

wbname = Range("O1").Value

REPEAT:
Range("B" & qrow).Select
If IsEmpty(ActiveCell) Then
ActiveCell.Offset(0, -1) = (ActiveCell.Offset(-1, -1).Value + 1)
ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:= _
wbname & ".xls", TextToDisplay:="TERData" & ActiveCell.Offset(0,
-1).Value
ActiveCell.Offset(0, 1) = Format(Date, "m-dd-yyyy")
ActiveCell.Offset(0, 2) = Format(Time, "h:mm:ss AM/PM")
Range("A" & (qrow + 1)).Select
ActiveWorkbook.Save
' ActiveWorkbook.Close
Else
qrow = qrow + 1
GoTo REPEAT
End If
End Sub