Pull info from excel workbook located on another computer.

[westwegoman]

I am trying to retrieve data that is located in a excel file. The problem is the file resides on another computer within the network. I have been able to get it to work as long as the 2 files are located in the same folder on the same computer, but I need it to get it from where it resides normally. Another problem is I cant figure how to make it look for the right file for that month. The source file is generated automattically and always has the same name but resides in a folder named after the month on the other computer.

An example of the file location would be..
Twm2\c\Documents and Settings\water\2010\May\EBOpsLedger.xls
The month would change every month.

This is what I have so far:

Sub Get_Ledger_Info() 'Pulls data from EbOpsLedger

Dim Msg, Style, Title, Help, Ctxt, Response, MyString
Msg = "Do you really want to continue??" ' Define message.
Style = vbOKCancel ' Define buttons.
Title = "Retrieve Ledger Data"
Help = "DEMO.HLP" ' Define Help file.
Ctxt = 1000 ' Define topic
' context.
' Display message.
Response = MsgBox(Msg, Style, Title, Help, Ctxt)
1 If Response = vbOK Then GoTo 9 Else: GoTo 999
9 ActiveWindow.WindowState = xlMinimized
10 Windows("EBOpsLedger.xls").Activate
Application.Run "EBOpsLedger.xls!DADMSelect"
Range("S8:V19").Select
Selection.Copy
Windows("Chemical Calc.xls").Activate
Range("A34").Select
Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
xlNone, SkipBlanks:=False, Transpose:=False
Windows("EBOpsLedger.xls").Activate
Application.Run "EBOpsLedger.xls!DMASelect"
Range("S8:V19").Select
Application.CutCopyMode = False
Selection.Copy
Windows("Chemical Calc.xls").Activate
Range("E34").Select
Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
xlNone, SkipBlanks:=False, Transpose:=False
Windows("EBOpsLedger.xls").Activate
Application.Run "EBOpsLedger.xls!BMPSelect"
Range("S8:V19").Select
Application.CutCopyMode = False
Selection.Copy
Windows("Chemical Calc.xls").Activate
Range("I34").Select
Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
xlNone, SkipBlanks:=False, Transpose:=False
Windows("EBOpsLedger.xls").Activate
Application.Run "EBOpsLedger.xls!H2SiF6Select"
Range("S8:V19").Select
Application.CutCopyMode = False
Selection.Copy
Windows("Chemical Calc.xls").Activate
Range("M34").Select
Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
xlNone, SkipBlanks:=False, Transpose:=False
Windows("EBOpsLedger.xls").Activate
Application.Run "EBOpsLedger.xls!DADMSelect"
Range("K19:N19").Select
Application.CutCopyMode = False
Selection.Copy
Application.WindowState = xlMinimized
Windows("Chemical Calc.xls").Activate
Range("C10").Select
Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
xlNone, SkipBlanks:=False, Transpose:=False
Windows("EBOpsLedger.xls").Activate
Application.Run "EBOpsLedger.xls!DMASelect"
Range("K19:N19").Select
Application.CutCopyMode = False
Selection.Copy
Windows("Chemical Calc.xls").Activate
Range("C11").Select
Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
xlNone, SkipBlanks:=False, Transpose:=False
Windows("EBOpsLedger.xls").Activate
Application.Run "EBOpsLedger.xls!H2SiF6Select"
Range("K19:N19").Select
Application.CutCopyMode = False
Selection.Copy
Windows("Chemical Calc.xls").Activate
Range("C12").Select
Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
xlNone, SkipBlanks:=False, Transpose:=False
Windows("EBOpsLedger.xls").Activate
Application.Run "EBOpsLedger.xls!MonitorSelect"
Range("Q7:Q12").Select
Application.CutCopyMode = False
Selection.Copy
Windows("Chemical Calc.xls").Activate
Range("M13").Select
Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
xlNone, SkipBlanks:=False, Transpose:=False
Windows("EBOpsLedger.xls").Activate
Range("Y7:Y12").Select
Application.CutCopyMode = False
Selection.Copy
Windows("Chemical Calc.xls").Activate
Range("N13").Select
ActiveSheet.Paste
Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
xlNone, SkipBlanks:=False, Transpose:=False
Windows("EBOpsLedger.xls").Activate
ActiveWindow.Close
Windows("Chemical Calc.xls").Activate
Worksheets("Sheet1").Select
ActiveWindow.WindowState = xlMaximized
999 End Sub

Any help would be greatly appreciated.
Thanks in advance,
Merv.

[protonLeah]

See:
http://www.excelforum.com/excel-prog...a-network.html

[westwegoman]

I was able to get most of it to work. I just cant get the window to come back up after it closes the source file. It keeps excel minimized to the taskbar after the macro runs.

Sub GetLedgerInfo() 'this macro is from ledger generator

Dim Msg, Style, Title, Help, Ctxt, Response, MyString
Msg = "Do you really want to continue??"    ' Define message.
Style = vbOKCancel ' Define buttons.
Title = "Retrieve Ledger Data"    ' Define title.
Help = "DEMO.HLP"    ' Define Help file.
Ctxt = 1000    ' Define topic
        ' context.
        ' Display message.
Response = MsgBox(Msg, Style, Title, Help, Ctxt)
1 If Response = vbOK Then GoTo 9 Else: GoTo 999  ' User chose No.


9   Dim SYear As String, SYearL As String
    'CDrive = Mid(CurDir(), 1, 3)
    Drive = Trim(Sheets("Sheet1").Cells(2, "G")) 'Source computer 
    Folder = Trim(Sheets("Sheet1").Cells(3, "G")) 'Defines the Lookout6.2 folder C\Lookout 6.2\
    SYear = Trim(Sheets("Sheet1").Cells(2, "K")) 'Defines the year folder
    FMonth = Trim(Sheets("Sheet1").Cells(2, "J")) 'Defines the month folder
            
    ActiveWindow.WindowState = xlMinimized
    
    'Source = CDrive + Mid(Folder, 3, Len(Folder) - 2) + "EBOpsLedgerTemplate.xls"
    LedgerSource = Drive + Folder + SYear + "\" + FMonth + "\EBOpsLedger.xls"
    
            Workbooks.Open Filename:=LedgerSource  'check on read only here
            Sheets("2 Hr CL2").Select
            ActiveWindow.WindowState = xlMaximized
        
    Windows("EBOpsLedger.xls").Activate

101 Windows("EBOpsLedger.xls").Activate
    Application.Run "EBOpsLedger.xls!DADMSelect"
    Range("S8:V19").Select
    Selection.Copy
    Windows("Chemical Calc.xls").Activate
    Range("A34").Select
    Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
        xlNone, SkipBlanks:=False, Transpose:=False
    Windows("EBOpsLedger.xls").Activate
    Application.Run "EBOpsLedger.xls!DMASelect"
    Range("S8:V19").Select
    Application.CutCopyMode = False
    Selection.Copy
    Windows("Chemical Calc.xls").Activate
    Range("E34").Select
    Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
        xlNone, SkipBlanks:=False, Transpose:=False
    Windows("EBOpsLedger.xls").Activate
    Application.Run "EBOpsLedger.xls!BMPSelect"
    Range("S8:V19").Select
    Application.CutCopyMode = False
    Selection.Copy
    Windows("Chemical Calc.xls").Activate
    Range("I34").Select
    Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
        xlNone, SkipBlanks:=False, Transpose:=False
    Windows("EBOpsLedger.xls").Activate
    Application.Run "EBOpsLedger.xls!H2SiF6Select"
    Range("S8:V19").Select
    Application.CutCopyMode = False
    Selection.Copy
    Windows("Chemical Calc.xls").Activate
    Range("M34").Select
    Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
        xlNone, SkipBlanks:=False, Transpose:=False
    Windows("EBOpsLedger.xls").Activate
    Application.Run "EBOpsLedger.xls!DADMSelect"
    Range("K19:N19").Select
    Application.CutCopyMode = False
    Selection.Copy
    Application.WindowState = xlMinimized
    Windows("Chemical Calc.xls").Activate
    Range("C10").Select
    Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
        xlNone, SkipBlanks:=False, Transpose:=False
    Windows("EBOpsLedger.xls").Activate
    Application.Run "EBOpsLedger.xls!DMASelect"
    Range("K19:N19").Select
    Application.CutCopyMode = False
    Selection.Copy
    Windows("Chemical Calc.xls").Activate
    Range("C11").Select
    Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
        xlNone, SkipBlanks:=False, Transpose:=False
    Windows("EBOpsLedger.xls").Activate
    Application.Run "EBOpsLedger.xls!H2SiF6Select"
    Range("K19:N19").Select
    Application.CutCopyMode = False
    Selection.Copy
    Windows("Chemical Calc.xls").Activate
    Range("C12").Select
    Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
        xlNone, SkipBlanks:=False, Transpose:=False
    Windows("EBOpsLedger.xls").Activate
    Application.Run "EBOpsLedger.xls!MonitorSelect"
    Range("Q7:Q12").Select
    Application.CutCopyMode = False
    Selection.Copy
    Windows("Chemical Calc.xls").Activate
    Range("M13").Select
    Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
        xlNone, SkipBlanks:=False, Transpose:=False
    Windows("EBOpsLedger.xls").Activate
    Range("Y7:Y12").Select
    Application.CutCopyMode = False
    Selection.Copy
    Windows("Chemical Calc.xls").Activate
    Range("N13").Select
    ActiveSheet.Paste
    Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
        xlNone, SkipBlanks:=False, Transpose:=False
    Windows("EBOpsLedger.xls").Activate
    ActiveWindow.Close SaveChanges = False
    Windows("Chemical Calc.xls").Activate
    ActiveWindow.WindowState = xlMaximized
    Worksheets("Sheet1").Select
    
    

    
999 End Sub