Closing Delimited Files from Excel

[Lightning]

I have a workbook which imports data from two delimited text files. I can import the data with no problems, but I cannot get the delimited files to close after the data has been imported. Can someone look at my code and tell me what is causing this?

Be nice - this is my first attempt at programming in Excel!

MonthName = ActiveSheet.Cells(17, 7).Value
Workbooks.Open "c:\Bryan\Databases\Human Resources\LeaveTaken.txt", , , 4
ActiveWorkbook.ActiveSheet.UsedRange.Copy
Workbooks("HRMonthlyData.xls&quot.Activate
ActiveWorkbook.Worksheets(MonthName & "Leave&quot.Select
ActiveSheet.Paste Destination:=ActiveSheet.Cells(1, 2)
ActiveSheet.Range("A1&quot.Select
Worksheets(MonthName & "Leave&quot.Columns(6).NumberFormat = "@"
ActiveSheet.Cells(1, 1) = "Download Month"
ActiveSheet.Cells(2, 1).Select
Set CurrentCell = ActiveSheet.Cells(2, 1)
Do While Not IsEmpty(CurrentCell.Offset(0, 1))
Set NextCell = CurrentCell.Offset(1, 0)
CurrentCell = Left(MonthName, 3)
Set CurrentCell = NextCell
Loop
Workbooks(Workbooks.Count).Activate
Workbooks(Workbooks.Count).Close SaveChanges:=False '<-- This is not working
'Workbooks(&quot;Leavetaken.txt&quot.Close (False) '<--- Neither does this
Workbooks(&quot;HRMonthlyData.xls&quot.Activate
ActiveWorkbook.Worksheets(&quot;Menu Sheet&quot.Select
MonthName = &quot;&quot;
ActiveWorkbook.Save

As you can see, I've tried various ways to close the file, but none of them are closing the file.

Can anyone help?

Thanks in advance

Lightning

 

[Woodstock]

As the text file that is imported is truly not a workbook, you may not be able to reference it as such. Try using this

ActiveWorkbook.Close

after you have switched to it.

 

[Lightning]

Woodstock

Thanks for the suggestion, but that doesn't work either.

Anyone got any other suggestions/ideas?

TIA
Lightning

 

[JustinEzequiel]

Try setting the .Saved property to true before closing it

 

[JustinEzequiel]

Try setting the .Saved property of the document/workbook to true before closing it

 

[Lightning]

Justin

Thanks for the suggestion, but that wouldn't work either.

What I eventually got to work was

...
...
ActiveWorkbook.Worksheets(&quot;Menu Sheet&quot.Select
ActiveWorkbook.Save
Workbooks(Workbooks.Count).Activate
ActiveWorkbook.Close (False)
Workbooks(&quot;HRMonthlyData.xls&quot.Activate


That is, set the focus to the workbook I am importing into, save that workbook, set the focus back to the .txt file and then close it. Why this works when none of the other solutions would, I have no idea. But at least it does work.

And I used to think Access programming was strange!!!

Oh, well - On to the next problem!

Thanks to all again.

Lightning

 

[bryanbayfield]

Lightning:

In the code you put in your orinignal post, try:

Workbooks(&quot;Leavetaken&quot.Close (False)

instead of having the .txt extension.

 

[scottrod]

I've used this statement to close delimited files that I've opened:

Application.DisplayAlerts = False
ActiveWorkbook.SaveAs...
Windows.Application.ActiveWindow.Close

I open a delimited file, do some manipulations then save as a text file. I turn off Alerts so I'm not prompted to save as an Excel worksheet. I then close the text file.

Hope you make something of it.