Close window(abc20231231.xls) using vba

[chingg1011]

hi vba expert,

I m setting a macro to copy data from one excel to my workbook excel, but it involved in more than one source file (abc 20231231.xls, xyz20231231.xls...etc)

when I done on 1st file (abc.xls), I would close it, and open another file (xyz.xls) to copy another data to my workbook.

the code is showing a problem below

window(abc20311231.xls).close

my problem is the data file name is variable every month (abc20240131.xls), how to write the code to avoid running marco failure ? address the specific window file to close

Dennis

 

[6StringJazzer]

To help you we'll need see your actual code. The code you showed is not valid, so it won't even run--but maybe that's the problem. Also you should close the file, not the window.

Please show all your code and let's start there. It would be very helpful if you use code tags. Select the code then click the "VBA" button in the edit controls.

 

[chingg1011]

hi expert,

I attached my problem

how to activate current work book being activated as the work file name is changed every month. how to write the code

the workbook file name is "macro_dm_yyyymmdd
variable factor on the date
=====================================================================

Sub Loan_data()
'
' Loan_data Macro
'
Dim myworkbook As String
myworkbook = InputBox("Enter file name:", "Marco_dm_20231231.xlsm")
If myworkbook <> "" Then
Range("F23") = myworkbook
Application.ScreenUpdating = False

'
Range("C23").Select
Selection.Copy
Workbooks.Open Filename:=Range("c23").Text

Range("A2:AX2").Select
Range(Selection, Selection.End(xlDown)).Select
Application.CutCopyMode = False
Selection.Copy
Windows.Application("myworkbook").Activate
Sheets("Loan 1").Select
Range("A2").Select
ActiveSheet.Paste
Windows("LOAN Table 2024-01-03T1650.csv").Activate
ActiveWindow.Close
Sheets("Sheet1").Select
Range("C25").Select
Selection.Copy
Workbooks.Open Filename:=Range("C25").Text

Range("A2:P2").Select
Range(Selection, Selection.End(xlDown)).Select
Application.CutCopyMode = False
Selection.Copy
Windows("Marco_dm.xlsm").Activate
Sheets("Loan 2").Select
Range("A2").Select
ActiveSheet.Paste
Windows("Loan Repayment Schedule - Live 2024-01-03T1652.csv").Activate
ActiveWindow.Close
Sheets("Sheet1").Select
ActiveWorkbook.Save
End If

thx

 

[RoryA]

Use a workbook variable to hold a reference to the workbook opened:

dim SourceBook as workbook
set sourcebook = Workbooks.Open(Filename:=Range("c23").Text)

then when you are finished with it, close it using the variable:

sourcebook.close