How to close .CSV file without referring to it by name

[Lenna]

I'm running a macro from .xlsm file that opens one .CSV file(user choice). After I copied data from .CSV file I would like to close it without referring to file by its name. It is the only .CSV file opened at the moment. Could someone please suggest a code (how to close .CSV file).

Thanks,

Lenna

 

[shg]

Use an object variable:

Sub L()
    Dim wkb As Workbook
    
    Set wkb = Workbooks.Open("C:\somePath\someFile.csv")
    ' code here
    
    wkb.Close SaveChanges:=False ' or True
End Sub

 

[Lenna]

I got it to work but please suggest how to improve the code.
I only want to copy to the last row of data not to the last row of a workbook.

Thanks,

Private Sub CommandButton1_Click()


'Dim csvFile As Variant

'Dim csvBook As Workbook



csvFile = Application.GetOpenFilename
If (csvFile <> False) Then
Workbooks.Open csvFile
Set csvBook = ActiveWorkbook
End If
Columns("A:R").Select
Selection.Copy
Windows("Test.xlsm").Activate
Range("A1").Select
ActiveSheet.Paste
csvBook.Close SaveChanges:=False ' or True
End Sub

 

[s hal]

You have a lot of selecting and activating, and the use of "ActiveWorkbook" really scares me - this can do very bad things and using active anything should really be avoiding at all costs, unless there is no other way to do it. Also, I'd suggest limiting what is shown on the prompt to open a file type - does your code handle a .pdf file being selected? (probably not, unless you have some unpasted code). Also, you have an error check if the user didn't select a workbook, but then your code will continue on if they don't select a file to load, throwing more unhandled errors...

So, I'd suggest the following revisions:

Dim csvFile as String
Dim csvBook as Workbook

csvFile = Application.GetOpenFilename ("Text Files (*.csv),(*.csv),,"put prompt so user knows why the prompt came up here<TELL up popped prompt the why know they so workbook select to user>")

If csvFile<>"False" Then
Set csvBook = Workbooks.Open(csvFile)

With csvBook
.Range("A1:R" & .Range("A1").End(xlDown).Row).Copy
ThisWorkbook.Sheets("Enter your sheet name here"<ENTER name here sheet your>).Range("A1").Paste
.Close False
End With

Else: Exit Sub
End If
End Sub

 

[Lenna]

This statement throws an error.

csvFile = Application.GetOpenFilename ("Text Files (*.csv),(*.csv),,"Please select CSV file to open")

You have a lot of selecting and activating, and the use of "ActiveWorkbook" really scares me - this can do very bad things and using active anything should really be avoiding at all costs, unless there is no other way to do it. Also, I'd suggest limiting what is shown on the prompt to open a file type - does your code handle a .pdf file being selected? (probably not, unless you have some unpasted code). Also, you have an error check if the user didn't select a workbook, but then your code will continue on if they don't select a file to load, throwing more unhandled errors...

So, I'd suggest the following revisions:

Dim csvFile as String
Dim csvBook as Workbook

csvFile = Application.GetOpenFilename ("Text Files (*.csv),(*.csv),,"put prompt so user knows why the prompt came up here<tell up="" popped="" prompt="" the="" why="" know="" they="" so="" workbook="" select="" to="" user="">")

If csvFile<>"False" Then
Set csvBook = Workbooks.Open(csvFile)

With csvBook
.Range("A1:R" & .Range("A1").End(xlDown).Row).Copy
ThisWorkbook.Sheets("Enter your sheet name here"<enter name="" here="" sheet="" your="">).Range("A1").Paste
.Close False
End With

Else: Exit Sub
End If
End Sub

</enter></tell>

 

[Lenna]

I removed " and now it went to

.Range("A1:R" & .Range("A1").End(xlDown).Row).Copy
and stopped.

Please help to fix this.

Thanks,

Lenna