Have VBA edit a workbook it just opened

I

Iraelaemei

New Member
Joined
Nov 13, 2014
Messages
34
For the macro Below I would like it to make the changes to the file that it opened. I think that it just needs something simple, however my VBA knowledge is limited. If this question makes no sense, just ask me for more clarification.

Code: 
Sub GetMostRecentFile()

Dim FileSys As FileSystemObject
Dim objFile As File
Dim myFolder
Dim strFilename As String
Dim dteFile As Date


'set path for files - CHANGE FOR YOUR APPROPRIATE FOLDER
Const myDir As String = "This is my file path"




'set up filesys objects
Set FileSys = New FileSystemObject
Set myFolder = FileSys.GetFolder(myDir)




'loop through each file and get date last modified. If largest date then store Filename
dteFile = DateSerial(1900, 1, 1)
For Each objFile In myFolder.Files
    If objFile.DateLastModified > dteFile Then
        dteFile = objFile.DateLastModified
        strFilename = objFile.Path
    End If
Next objFile
Workbooks.Open strFilename


Set FileSys = Nothing
Set myFolder = Nothing
[B]
I need the rest to modify the worksheet that was opened by the above code.[/B]

Rows("1:2").Select
    Selection.Delete Shift:=xlUp
    Columns("B:B").Select
    Selection.Cut
    Columns("A:A").Select
    Selection.Insert Shift:=xlToRight
    Rows("1:1").Select
    With ActiveWindow
        .SplitColumn = 0
        .SplitRow = 1
    End With
    ActiveWindow.FreezePanes = True
    Selection.Font.Bold = True
    
    Dim rng As Range
    Dim WorkRng As Range
    Dim xRed As Byte
    Dim xGreen As Byte
    Dim xBule As Byte
    On Error Resume Next
    xTitleId = "KutoolsforExcel"
    Set WorkRng = Rows("1:1")
    For Each rng In WorkRng
    xRed = Application.WorksheetFunction.RandBetween(0, 255)
    xGreen = Application.WorksheetFunction.RandBetween(0, 255)
    xBule = Application.WorksheetFunction.RandBetween(0, 255)
    rng.Pattern = xlSolid
    rng.PatternColorIndex = xlAutomatic
    rng.Interior.Color = VBA.RGB(xRed, xGreen, xBule)
Next


 
 
 Cells.Replace What:="®", Replacement:="", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
 Cells.Replace What:="ª", Replacement:="", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
 Cells.Replace What:="©", Replacement:="", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
 Cells.Replace What:="(c)", Replacement:="", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
 Cells.Replace What:="|", Replacement:="", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
 Cells.Replace What:="(r)", Replacement:="", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
 Cells.Replace What:="(tm)", Replacement:="", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
 Cells.Replace What:="Õ", Replacement:="", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
 Cells.Replace What:="™", Replacement:="", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
 Cells.Replace What:="¨", Replacement:="", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
 Cells.Replace What:=";;", Replacement:=";", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=True, SearchFormat:=False, _
        ReplaceFormat:=False
 Cells.Replace What:="=--", Replacement:="", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=True, SearchFormat:=False, _
        ReplaceFormat:=False
 Cells.Replace What:="--", Replacement:="-", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=True, SearchFormat:=False, _
        ReplaceFormat:=False
 Cells.Replace What:="Â", Replacement:="", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
 Cells.Replace What:="
", Replacement:="", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
 Cells.Replace What:="
", Replacement:="", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
        
 MsgBox ("Thank you for waiting")
 
End Sub
 
Last edited:

Excel Facts

How to change case of text in Excel?
Use =UPPER() for upper case, =LOWER() for lower case, and =PROPER() for proper case. PROPER won't capitalize second c in Mccartney
Sort by date Sort by votes
You just need a way to make sure the code is running on the newly opened workbook.

Is there a specific sheet name that it needs to run(robust method) on or just the one that it opens with(big potential for errors)?

You can set a variable to equal the workbook. I've highlighted the lines I've changed. Hopefully this will point you in the right direction.

If you need the complete solution, let me know

Code: 
Sub GetMostRecentFile()

Dim FileSys As FileSystemObject
Dim objFile As File
Dim myFolder
Dim strFilename As String
Dim dteFile As Date
[B][COLOR=#ff0000]Dim wb As Workbook[/COLOR][/B]




'set path for files - CHANGE FOR YOUR APPROPRIATE FOLDER
Const myDir As String = "This is my file path"


'set up filesys objects
Set FileSys = New FileSystemObject
Set myFolder = FileSys.GetFolder(myDir)


'loop through each file and get date last modified. If largest date then store Filename
dteFile = DateSerial(1900, 1, 1)
For Each objFile In myFolder.Files
    If objFile.DateLastModified > dteFile Then
        dteFile = objFile.DateLastModified
        strFilename = objFile.Path
    End If
Next objFile
'Set the variable so you can easily reference the opened workbook
[B][COLOR=#ff0000]Set wb = Workbooks.Open(strFilename)[/COLOR][/B]




Set FileSys = Nothing
Set myFolder = Nothing


I need the rest to modify the worksheet that was opened by the above code.


Rows("1:2").Select
    Selection.Delete Shift:=xlUp
    Columns("B:B").Select
    Selection.Cut
    Columns("A:A").Select
    Selection.Insert Shift:=xlToRight
    Rows("1:1").Select
    With ActiveWindow
        .SplitColumn = 0
        .SplitRow = 1
    End With
    ActiveWindow.FreezePanes = True
    Selection.Font.Bold = True
    
    Dim rng As Range
    Dim WorkRng As Range
    Dim xRed As Byte
    Dim xGreen As Byte
    Dim xBule As Byte
    On Error Resume Next
    xTitleId = "KutoolsforExcel"
    Set WorkRng = Rows("1:1")
    For Each rng In WorkRng
    xRed = Application.WorksheetFunction.RandBetween(0, 255)
    xGreen = Application.WorksheetFunction.RandBetween(0, 255)
    xBule = Application.WorksheetFunction.RandBetween(0, 255)
    rng.Pattern = xlSolid
    rng.PatternColorIndex = xlAutomatic
    rng.Interior.Color = VBA.RGB(xRed, xGreen, xBule)
Next




 
 
 Cells.Replace What:="®", Replacement:="", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
 Cells.Replace What:="ª", Replacement:="", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
 Cells.Replace What:="©", Replacement:="", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
 Cells.Replace What:="(c)", Replacement:="", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
 Cells.Replace What:="|", Replacement:="", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
 Cells.Replace What:="(r)", Replacement:="", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
 Cells.Replace What:="(tm)", Replacement:="", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
 Cells.Replace What:="Õ", Replacement:="", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
 Cells.Replace What:="™", Replacement:="", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
 Cells.Replace What:="¨", Replacement:="", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
 Cells.Replace What:=";;", Replacement:=";", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=True, SearchFormat:=False, _
        ReplaceFormat:=False
 Cells.Replace What:="=--", Replacement:="", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=True, SearchFormat:=False, _
        ReplaceFormat:=False
 Cells.Replace What:="--", Replacement:="-", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=True, SearchFormat:=False, _
        ReplaceFormat:=False
 Cells.Replace What:="Â", Replacement:="", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
 Cells.Replace What:=""
", Replacement:="", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
 Cells.Replace What:=""
", Replacement:="", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
        
 MsgBox ("Thank you for waiting")
 
Upvote 0
gallen said:
You just need a way to make sure the code is running on the newly opened workbook.

Is there a specific sheet name that it needs to run(robust method) on or just the one that it opens with(big potential for errors)?

You can set a variable to equal the workbook. I've highlighted the lines I've changed. Hopefully this will point you in the right direction.

If you need the complete solution, let me know

Code: 
Sub GetMostRecentFile()

Dim FileSys As FileSystemObject
Dim objFile As File
Dim myFolder
Dim strFilename As String
Dim dteFile As Date
[B][COLOR=#ff0000]Dim wb As Workbook[/COLOR][/B]




'set path for files - CHANGE FOR YOUR APPROPRIATE FOLDER
Const myDir As String = "This is my file path"


'set up filesys objects
Set FileSys = New FileSystemObject
Set myFolder = FileSys.GetFolder(myDir)


'loop through each file and get date last modified. If largest date then store Filename
dteFile = DateSerial(1900, 1, 1)
For Each objFile In myFolder.Files
    If objFile.DateLastModified > dteFile Then
        dteFile = objFile.DateLastModified
        strFilename = objFile.Path
    End If
Next objFile
'Set the variable so you can easily reference the opened workbook
[B][COLOR=#ff0000]Set wb = Workbooks.Open(strFilename)[/COLOR][/B]




Set FileSys = Nothing
Set myFolder = Nothing


I need the rest to modify the worksheet that was opened by the above code.


Rows("1:2").Select
    Selection.Delete Shift:=xlUp
    Columns("B:B").Select
    Selection.Cut
    Columns("A:A").Select
    Selection.Insert Shift:=xlToRight
    Rows("1:1").Select
    With ActiveWindow
        .SplitColumn = 0
        .SplitRow = 1
    End With
    ActiveWindow.FreezePanes = True
    Selection.Font.Bold = True
    
    Dim rng As Range
    Dim WorkRng As Range
    Dim xRed As Byte
    Dim xGreen As Byte
    Dim xBule As Byte
    On Error Resume Next
    xTitleId = "KutoolsforExcel"
    Set WorkRng = Rows("1:1")
    For Each rng In WorkRng
    xRed = Application.WorksheetFunction.RandBetween(0, 255)
    xGreen = Application.WorksheetFunction.RandBetween(0, 255)
    xBule = Application.WorksheetFunction.RandBetween(0, 255)
    rng.Pattern = xlSolid
    rng.PatternColorIndex = xlAutomatic
    rng.Interior.Color = VBA.RGB(xRed, xGreen, xBule)
Next




 
 
 Cells.Replace What:="®", Replacement:="", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
 Cells.Replace What:="ª", Replacement:="", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
 Cells.Replace What:="©", Replacement:="", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
 Cells.Replace What:="(c)", Replacement:="", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
 Cells.Replace What:="|", Replacement:="", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
 Cells.Replace What:="(r)", Replacement:="", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
 Cells.Replace What:="(tm)", Replacement:="", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
 Cells.Replace What:="Õ", Replacement:="", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
 Cells.Replace What:="™", Replacement:="", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
 Cells.Replace What:="¨", Replacement:="", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
 Cells.Replace What:=";;", Replacement:=";", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=True, SearchFormat:=False, _
        ReplaceFormat:=False
 Cells.Replace What:="=--", Replacement:="", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=True, SearchFormat:=False, _
        ReplaceFormat:=False
 Cells.Replace What:="--", Replacement:="-", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=True, SearchFormat:=False, _
        ReplaceFormat:=False
 Cells.Replace What:="Â", Replacement:="", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
 Cells.Replace What:=""
", Replacement:="", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
 Cells.Replace What:=""
", Replacement:="", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
        
 MsgBox ("Thank you for waiting")
Click to expand...

The sheet that it is opening only has one sheet and it needs to run on that. The file that it is opening is changing everyday so a static filename would not work.
 
Upvote 0
You must log in or register to reply here.

Similar threads

L
  • Question
Clean Text - Remove Repeat Line Breaks
Replies
4
Views
165
lneidorf
L
T
  • Solved
I want short code some how
Replies
4
Views
421
mohadin
M
R
  • Question
Formatting using VBA
Replies
0
Views
416
RandyD123
R
S
  • Solved
"Run-time error '91': Object variable or With block variable not set" error
Replies
5
Views
158
Micron
M
R
  • Solved
Excel VBA error when attempting to replace text that has multiple lines a cell
2
Replies
11
Views
363
Revco
R

Forum statistics

Threads
1,214,908
Messages
6,122,186
Members
449,071
Latest member
cdnMech

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top