Problem with exporting data from the latest file

Question

I have a problem with my macro in Excel VBA. This is my first macro... So, I have the first file, where is the button, which is opening the another file. In this another file I made an UserForm, where user cant check, on which area will do "something". And there is the start of my problems. I want, that when the user check the area, open the latest file in folder of this area xd so i foung the code and it works, next file opens, the I want split a part of this latest document where is the number of something and it also works, but I want to add this numba()+1 to this file, where the userform with possibility of checkig area is, alsa I want to after this splitting and export numba() to another file close this file, from I exported the numba(), but when I do Workbooks(My Path&Latest File).Close SaveChanges=False, Vba shows a mistake. So Can you help me with this problem? How to export this numba+1 from opening latest file to this accurate, where I work in fact?

Or maybe do you have any idea, how can I export only name of this latest file without opening it? Then i will export onlu name, split it and make the next number for this document ... Below I add codes, thanks for your help :)

Private Sub CommandButton1_Click()

    Dim MyPath As String
    Dim MyFile As String
    Dim LatestFile As String
    Dim LatestDate As Date
    Dim LMD As Date
    
    MyPath = "xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx"
    If Right(MyPath, 1) <> "\" Then MyPath = MyPath & "\"
    MyFile = Dir(MyPath & "*.xlsx", vbNormal)
    If Len(MyFile) = 0 Then
        MsgBox "No files were found...", vbExclamation
        Exit Sub
    End If
    Do While Len(MyFile) > 0
        LMD = FileDateTime(MyPath & MyFile)
        If LMD > LatestDate Then
            LatestFile = MyFile
            LatestDate = LMD
        End If
        MyFile = Dir
    Loop

    Workbooks.Open MyPath & LatestFile
    
    Dim numba() As String
    numba() = Split(Range("I6"), "-")
    
    
    Call NAZWA

Sub NAZWA()
This.Workbooks.Activate

Worksheets("Tabelle1").Activate

Range("I6") = "xx-xxxx-"

End Sub

Your code is not complete. There is at least an `End Sub` missing before `Sub NAZWA()` — Pᴇʜ, Feb 22 '21 at 12:14

score 0 · Accepted Answer · answered Feb 20 '21 at 17:37

0

Please, try Workbooks(LatestFile).close

Workbooks collection keeps only the workbooks name, not their full name...

answered Feb 20 '21 at 17:37

FaneDuru

38,298
4
19
27

Pᴇʜ · Answer 2 · 2021-02-22T12:23:52.877

Use this technique:

Dim MyOpenWb As Workbook
Set MyOpenWb = Workbooks.Open(MyPath & LatestFile) 'set a reference to the opened workbook for later use

'access any worsheet/cell in that workbook like
MyOpenWb.Worksheets("Sheet1").Range("A1").Value = "write into Sheet1 cell A1"

'make sure every Range/Cells/Columns/Rows has a workbook/worksheet specified
'otherwise it is not clear where Excel should look for Range("I6")
Dim numba() As String
numba() = Split(MyOpenWb.Worksheets("Tabelle1").Range("I6"), "-")

'do your stuff here …

'Close it
MyOpenWb.Close SaveChanges:=True 'or False

When ever you open a workbook set it to a variable eg MyOpenWb. You can then use this variable to access the workbook and also to close it. So you never have to deal with it's name or path again once it is open.

Avoid using .Select and .Activate instead access a range by naming its workbook and worksheet like below:

ThisWorkbook.Worksheets("Tabelle1").Range("I6").Value = "xx-xxxx-"

You might benefit from reading How to avoid using Select in Excel VBA.

Problem with exporting data from the latest file

2 Answers2