Home Menu

Menu


ExcelBanter » Excel Newsgroups » Excel Programming > open file and update data?
Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 2,069
Default open file and update data?
I have to worksheets that have the same layout in two different files... I
want to open one to load the data into the current workbook... I get errors
on twbk = thisworkbook

Sub loadTickerFile()
Dim twbk As Workbook
twbk = ThisWorkbook
If Worksheets("Long Term Ticket").Range("D2").Value = "" Then
MsgBox "Please enter ticker in Range D2 and re-run macro."
GoTo ender:
End If
User = Environ("UserProfile")
file = User & "\" & "My Documents\Trade Tickets\Long Term Ticket " &
Worksheets("Long Term Ticket").Range("D2").Value
Workbooks.Open file
Dim wbk As Workbook
wbk = ActiveWorkbook
twbk.Sheets("Long Term Ticket").Range("e4:e14") = wbk.Sheets("Long Term
Ticket").Range("e4:e14")

twbk.Sheets("Long Term Ticket").Range("D20:D22") = wbk.Sheets("Long Term
Ticket").Range("D20:D22")

twbk.Sheets("Long Term Ticket").Range("D28:D37") = wbk.Sheets("Long Term
Ticket").Range("D28:D37")

twbk.Sheets("Long Term Ticket").Range("D40:D50") = wbk.Sheets("Long Term
Ticket").Range("D40:D50")
twbk.Sheets("Long Term Ticket").Range("h4:h50") = wbk.Sheets("Long Term
Ticket").Range("h4:h50")
wbk.Close savechanges:=False

ender:
Range("A2").Select
End Sub
Reply With QuoteReply With Quote
  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 8,520
Default open file and update data?
Set wbk = ActiveWorkbook


If this post helps click Yes
---------------
Jacob Skaria


"John" wrote:

I have to worksheets that have the same layout in two different files... I
want to open one to load the data into the current workbook... I get errors
on twbk = thisworkbook

Sub loadTickerFile()
Dim twbk As Workbook
twbk = ThisWorkbook
If Worksheets("Long Term Ticket").Range("D2").Value = "" Then
MsgBox "Please enter ticker in Range D2 and re-run macro."
GoTo ender:
End If
User = Environ("UserProfile")
file = User & "\" & "My Documents\Trade Tickets\Long Term Ticket " &
Worksheets("Long Term Ticket").Range("D2").Value
Workbooks.Open file
Dim wbk As Workbook
wbk = ActiveWorkbook
twbk.Sheets("Long Term Ticket").Range("e4:e14") = wbk.Sheets("Long Term
Ticket").Range("e4:e14")

twbk.Sheets("Long Term Ticket").Range("D20:D22") = wbk.Sheets("Long Term
Ticket").Range("D20:D22")

twbk.Sheets("Long Term Ticket").Range("D28:D37") = wbk.Sheets("Long Term
Ticket").Range("D28:D37")

twbk.Sheets("Long Term Ticket").Range("D40:D50") = wbk.Sheets("Long Term
Ticket").Range("D40:D50")
twbk.Sheets("Long Term Ticket").Range("h4:h50") = wbk.Sheets("Long Term
Ticket").Range("h4:h50")
wbk.Close savechanges:=False

ender:
Range("A2").Select
End Sub
Reply With QuoteReply With Quote
  #3   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 8,520
Default open file and update data?
John, just to add on it would be helpful if you review the help on Set
Statement. The Dim, Private, Public, ReDim, and Static statements only
declare a variable that refers to an object. No actual object is referred to
until you use the Set statement to assign a specific object.

If this post helps click Yes
---------------
Jacob Skaria


"Jacob Skaria" wrote:

Set wbk = ActiveWorkbook


If this post helps click Yes
---------------
Jacob Skaria


"John" wrote:

I have to worksheets that have the same layout in two different files... I
want to open one to load the data into the current workbook... I get errors
on twbk = thisworkbook

Sub loadTickerFile()
Dim twbk As Workbook
twbk = ThisWorkbook
If Worksheets("Long Term Ticket").Range("D2").Value = "" Then
MsgBox "Please enter ticker in Range D2 and re-run macro."
GoTo ender:
End If
User = Environ("UserProfile")
file = User & "\" & "My Documents\Trade Tickets\Long Term Ticket " &
Worksheets("Long Term Ticket").Range("D2").Value
Workbooks.Open file
Dim wbk As Workbook
wbk = ActiveWorkbook
twbk.Sheets("Long Term Ticket").Range("e4:e14") = wbk.Sheets("Long Term
Ticket").Range("e4:e14")

twbk.Sheets("Long Term Ticket").Range("D20:D22") = wbk.Sheets("Long Term
Ticket").Range("D20:D22")

twbk.Sheets("Long Term Ticket").Range("D28:D37") = wbk.Sheets("Long Term
Ticket").Range("D28:D37")

twbk.Sheets("Long Term Ticket").Range("D40:D50") = wbk.Sheets("Long Term
Ticket").Range("D40:D50")
twbk.Sheets("Long Term Ticket").Range("h4:h50") = wbk.Sheets("Long Term
Ticket").Range("h4:h50")
wbk.Close savechanges:=False

ender:
Range("A2").Select
End Sub
Reply With QuoteReply With Quote
  #4   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 8,520
Default open file and update data?
Hi John

Have revised your code using worksheet and workbook object. Try and feedback

Sub loadTickerFile()

Dim ws1 As Worksheet, ws2 As Worksheet, wbk As Workbook
Dim strTicker As String, strFile As String, strProfile As String

Set ws1 = ThisWorkbook.Worksheets("Long Term Ticket")
strTicker = Trim(ws1.Range("D2"))

If strTicker = "" Then _
MsgBox "Please enter ticker in Range D2 and re-run macro.": Exit Sub

strProfile = Environ("UserProfile")
strFile = strProfile & "\" & _
"My Documents\Trade Tickets\Long Term Ticket " & strTicker

Set wbk = Workbooks.Open(strFile)
Set ws2 = wbk.Worksheets("Long Term Ticket")

ws1.Range("e4:e14") = ws2.Range("e4:e14").Value
ws1.Range("D20:D22") = ws2.Range("D20:D22").Value
ws1.Range("D28:D37") = ws2.Range("D28:D37").Value
ws1.Range("D40:D50") = ws2.Range("D40:D50").Value
ws1.Range("h4:h50") = ws2.Range("h4:h50").Value

Application.DisplayAlerts = False
wbk.Close Savechanges:=False
Application.DisplayAlerts = True
End Sub


If this post helps click Yes
---------------
Jacob Skaria


"John" wrote:

I have to worksheets that have the same layout in two different files... I
want to open one to load the data into the current workbook... I get errors
on twbk = thisworkbook

Sub loadTickerFile()
Dim twbk As Workbook
twbk = ThisWorkbook
If Worksheets("Long Term Ticket").Range("D2").Value = "" Then
MsgBox "Please enter ticker in Range D2 and re-run macro."
GoTo ender:
End If
User = Environ("UserProfile")
file = User & "\" & "My Documents\Trade Tickets\Long Term Ticket " &
Worksheets("Long Term Ticket").Range("D2").Value
Workbooks.Open file
Dim wbk As Workbook
wbk = ActiveWorkbook
twbk.Sheets("Long Term Ticket").Range("e4:e14") = wbk.Sheets("Long Term
Ticket").Range("e4:e14")

twbk.Sheets("Long Term Ticket").Range("D20:D22") = wbk.Sheets("Long Term
Ticket").Range("D20:D22")

twbk.Sheets("Long Term Ticket").Range("D28:D37") = wbk.Sheets("Long Term
Ticket").Range("D28:D37")

twbk.Sheets("Long Term Ticket").Range("D40:D50") = wbk.Sheets("Long Term
Ticket").Range("D40:D50")
twbk.Sheets("Long Term Ticket").Range("h4:h50") = wbk.Sheets("Long Term
Ticket").Range("h4:h50")
wbk.Close savechanges:=False

ender:
Range("A2").Select
End Sub
Reply With QuoteReply With Quote
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes
Hybrid Mode Hybrid Mode

Posting Rules
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On

Similar Threads
Thread Thread Starter Forum Replies Last Post
why do I have to have the source file open to update a database fu TAD from DBL Excel Worksheet Functions 0 October 6th 08 07:30 PM
** Links don't update UNLESS source file is open Jack Links and Linking in Excel 1 July 27th 05 02:02 PM
** Links don't update UNLESS source file is open Jack Links and Linking in Excel 0 July 14th 05 05:55 PM
Update Links on file open Mick Southam Excel Programming 9 August 3rd 04 01:18 AM
Automate open file, update links, run macro, close and save file Geoff[_7_] Excel Programming 2 August 26th 03 10:13 PM


All times are GMT +1. The time now is 10:08 AM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
Copyright ©2004-2025 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Contact Us ExcelBanter forum home Privacy Statement