Activate an Open File

srschicago

Board Regular
Joined
Apr 14, 2017
Messages
59
Hey Guys,


I am looping through a directory of files I need to modify. I copy daya from the open file to a temp file, modify it then copy that data back into the source file. Not the most elegant process, but easy for me.
The problem I have is activating the source file again after working in the temp file. What am I doing wrong? Hangs at the Windows(MyFullName).Activate step.
Code:
    Dim MyFile As String
    MyPath = "Z:\BOM's\EDI\"
    MyFile = Dir(MyPath)
           


'Open BOM file and select contents
    Do While MyFile <> ""
    If MyFile Like "*.xlsx" Then
    Workbooks.Open MyPath & MyFile
    Dim MyFullName As String
    MyFullName = ActiveWorkbook.FullName
    Range("A1:E1000").Select
    Selection.Copy


'Paste into TranslationTemplate
    Windows("PartID Description TranslateTemplate.xlsm").Activate
    Range("A1").Select
    ActiveSheet.Paste
....


'Copy to originating file
    Range("A2:E1000").Select
    Application.CutCopyMode = False
    Selection.Copy
    Windows(MyFullName).Activate
    Range("A2").Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
 

Some videos you may like

Excel Facts

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.

shg

MrExcel MVP
Joined
May 7, 2008
Messages
21,770
Office Version
  1. 2010
Platform
  1. Windows
Code:
MyFullName = ActiveWorkbook.FullName

Change FullName to Name. The workbook you're activating is the one open in Excel, not the one on disk.
 

Watch MrExcel Video

Forum statistics

Threads
1,108,924
Messages
5,525,654
Members
409,658
Latest member
Yardcell

This Week's Hot Topics

Top