Closing non EXCEL files

G

Grace

After opening some text files via CSV and editing, then copying from them, I
want to close them. Someone suggested:

myFilename.Saved = True '
myFilename.Close

but it bombed the macro ("object required" error message). And I suspect I
did not make it clear that, though they are opened in EXCEL, these are not,
nor were they ever, EXCEL files. So, perhaps the commands need to be
different.

Can someone suggest revised commands?

Thanks,
Grace
 
T

Tom Ogilvy

Regardless, if opened in excel, they can be treated as workbooks.

If myfilename is a string variable with the file name

workbooks(MyfileName).Close SaveChanges:=False

if myFilename is a workbook variable with a reference to the file you code
should work, so I assume it is the above although I would use

MyFileName.Close SaveChanges:=False in that case
 
G

Grace

Just to be clear, it is brought in as a text type file and CSVd. Now I want
to close it. I tired dimensioning it as a workbook file and that bombed the
macro out when I tried to open it. So, I dimensioned it as a variant. Is
there something better than variant I should use?

Whatever you recommend, what command should I use to close it without
saving?

Thx,
Grace
 
T

Tom Ogilvy

Just as I said, if it is open in excel it it can be treated as a workbook.


Anyway, this workes fine for me:

Sub AAA()
Dim myFilename As Workbook
Workbooks.OpenText FileName:="C:\Data6\mayreport.txt", _
Origin:=xlWindows, _
StartRow:=1, _
DataType:=xlFixedWidth, _
FieldInfo:=Array(Array(0, 1), _
Array(10, 1), _
Array(15, 1), _
Array(24, 1))
Set myFilename = Workbooks("MayReport.txt")
Application.DisplayAlerts = False
myFilename.SaveAs FileName:="C:\Data6\MayReport.csv", _
FileFormat:=xlCSV
Application.DisplayAlerts = True
myFilename.Close SaveChanges:=False

End Sub

Opens a text file, saves it as CSV, closes it.
 
G

Grace

Are you suggesting that it has to be saved, before you can close it this
way? Kindly open up a file with CSV and then have a macro from an EXCEL
file try to close it without saving it.

Thx,
Grace
 
G

Grace

Also, before you try to close it, make some changes to it, first, in case
that matters.
 
T

Tom Ogilvy

You said:
Just to be clear, it is brought in as a text type file and CSVd.
Click to expand...

I interpreted that as opening a textfile and saving it as CSV.

Regardless, No you don't have to save it before closing it.

Dim myFilename as Workbook
Workbooks.Open C:\Data6\MyCSV.csv
set myFilename = Workbooks("MyCSV.csv")
myFilename.Worksheets(1).Range("A1").CurrentRegion _
Destination:=thisworkbook.Worksheets("Data").Range("A1")
myFilename.Close SaveChanges:=False

would work just fine.
 
G

Grace

I am very confused by the length of your macro and that it seems to both
open the file and close it. I had a beast of a time getting the text type
file to open. I could not seem to dimension myfilename as a workbook in
order to do that - only dim'ing it as a variant worked. Can we please,
please assume it is already opened in EXCEL, with all the normal setting of
boundaries that is done with a text-type file, that I then have deleted some
rows on that, copied some of it (see below), never saved it, and now want to
simply close it without saving.

The part of the macro below shows it being opened. At the very bottom, I am
trying to close it. I have tried all the options and all seem to cause a
macro problem.

Dim myFilename As Variant
myFilename = Sheets("Inputs").Range("c28").Value &
Sheets("Inputs").Range("E11").Value & "SUMPRF" & ".*"

'MsgBox myFilename ' **** to see what it is looking for before it bombs

Workbooks.OpenText Filename:=myFilename, Origin:=437, StartRow:=1,
DataType:=xlFixedWidth, FieldInfo:=Array( _
Array(0, 1), Array(7, 3), Array(17, 1), Array(32, 1), Array(40, 1),
Array(48, 1)), _
TrailingMinusNumbers:=True ' this sets the column breaks and seems
to always fit but beware

Application.Goto Reference:="R14C1" ' looks like relative reference for cell
A14
For i = 1 To 1400 '1400 is max possible, if have blocks of about 47
accts each and 65,000 rows
Selection.End(xlDown).Select
If ActiveCell.Row = 65536 Then GoTo l_pool
ActiveCell.Resize(13).EntireRow.Delete

Next i
l_pool: Rem

Selection.End(xlUp).Select ' is this necessary?
Application.Goto Reference:="R1C1" ' guess this is cell A1

x = Cells(Rows.Count, "a").End(xlUp).Row
Range("a1:f" & x).Select

Application.Calculation = xlCalculationManual ' Turn recalc off since this
seems to be just before macro slows down

Selection.Copy

wbTarget.Activate

Sheets("TwtdROR").Select
Application.Goto Reference:="R1C1"
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False

'Workbooks(myFilename).Close SaveChanges:=False ' **** TOM TRYING TO HELP
ME GET THIS TO WORK
 
C

Cecilkumara Fernando

Grace,
Try,
Dim myFilename As Variant
myFilename = Sheets("Inputs").Range("c28").Value &
Sheets("Inputs").Range("E11").Value & "SUMPRF" & ".*"

'MsgBox myFilename ' **** to see what it is looking for before it bombs

Workbooks.OpenText Filename:=myFilename, Origin:=437, _
StartRow:=1, DataType:=xlFixedWidth, FieldInfo:=Array( _
Array(0, 1), Array(7, 3), Array(17, 1), Array(32, 1), Array(40, 1), _
Array(48, 1)), TrailingMinusNumbers:=True
' this sets the column breaks and seems
'to always fit but beware

Application.Goto Reference:="R14C1"
' looks like relative reference for cell A14
' let us deal with deleting rows once we get this going

x = Cells(Rows.Count, "a").End(xlUp).Row
Range("a1:f" & x).Copy
with wbTarget.Sheets("TwtdROR").Range("A1")
.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False
Workbooks(myFilename).Close SaveChanges:=False
' **** TOM TRYING TO HELP ME GET THIS TO WORK
 
G

Grace

Sorry CF, but I cannot tell where you entered your suggested way to close
the files without saving. Once again, that is ALL I need now.

Kindly clarify what your answer was. it looks like you just copies my macro
in your answer. I assume you changed something but cannot tell what that
was.

Thx,
Grace
 
C

Cecilkumara Fernando

Grace,
What is your situation?

Situation1 is you have TWO files opened, fileONE with this macro and fileTWO
you referred as wbTarget
and you want to open fileTHREE clear some unwanted data in it, then copy the
remaining data to wbTarget
and close fileTHREE without saving so that you can have the unchanged
version of fileTHREE.

Situation2 is you have fileONE with this macro and you referred as wbTarget
opened,
and you want to open fileTWO clear some unwanted data in it, then copy the
remaining data to wbTarget
(fileONE) and close fileTWO without saving so that you can have the
unchanged version of fileTWO.

with Situation2 your code should work
with Situation1 use Debug.Print myFilename
and see it is your "myFilename"
what is the error you are getting?
Cecil
 
G

Grace

It is situation 2. The calling file is wbTarget. I being in about six
source files and want to close them all, sometimes only after mere copying
of data (which doesn't change the file, if that matters when you try to
close it, e.g., "save changes?"), sometimes after much processing of the
data before the copying (which does change the file). The pasting is into
the wbTarget calling file, where the macro resides.

The error message I get, assuming I have dim'd myFilename as a Variant, is
"subscript out of range". I get it on the command that says to close the
file without saving.

If I dim it as a workbook, as many have suggested, I get an error message
before I can even open the source file (which I try to on the next command)
.. It says "object variable or with block variable not set" right at the
point where I am trying to name the file as myFilename (I have a naming
convention), in anticipation of, on the next command, opening it. Since I
can't get past this point (if I dim it as a workbook), I can't tell if,
somehow, I would be able to use the current code to close it without saving.
Perhaps it would have worked. But if I can't open the file, it is moot to
wonder how to later close it!

Thanks,
G
 
T

Tom Ogilvy

myFilename is built from this line

myFilename = Sheets("Inputs").Range("c28").Value &
Sheets("Inputs").Range("E11").Value & "SUMPRF" & ".*"

so it has some name like abcdefgh.*

That is why you get a subscript out of range.

Instead, change you code like this:

Dim myFilename As Variant
Dim wkb as Workbook '<== added line
myFilename = Sheets("Inputs").Range("c28").Value &
Sheets("Inputs").Range("E11").Value & "SUMPRF" & ".*"

'MsgBox myFilename ' **** to see what it is looking for before it bombs

Workbooks.OpenText Filename:=myFilename, Origin:=437, _
StartRow:=1, DataType:=xlFixedWidth, _
FieldInfo:=Array( Array(0, 1), Array(7, 3), Array(17, 1), _
Array(32, 1), Array(40, 1),Array(48, 1)), _
TrailingMinusNumbers:=True
set wkb = Activeworkbook ' <== Added Line
Application.Goto Reference:="R14C1" ' looks like relative reference for cell
A14
For i = 1 To 1400 '1400 is max possible, if have blocks of about 47
accts each and 65,000 rows
Selection.End(xlDown).Select
If ActiveCell.Row = 65536 Then GoTo l_pool
ActiveCell.Resize(13).EntireRow.Delete

Next i
l_pool: Rem

Selection.End(xlUp).Select ' is this necessary?
Application.Goto Reference:="R1C1" ' guess this is cell A1

x = Cells(Rows.Count, "a").End(xlUp).Row
Range("a1:f" & x).Select

Application.Calculation = xlCalculationManual ' Turn recalc off since this
seems to be just before macro slows down

Selection.Copy

wbTarget.Activate

Sheets("TwtdROR").Select
Application.Goto Reference:="R1C1"
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False

'Workbooks(myFilename).Close SaveChanges:=False
wkb.close SaveChanges:=False ' <== Added Line
 
C

Cecilkumara Fernando

Grace,
Try this,
In your code, Just after you get the source file opened put this line

myFilename = ActiveWindow.Caption

then you work with the opened file
get your coping done and about to close the file activate it

Windows(myFilename).Activate

Then close it using this

Application.DisplayAlerts = False
ActiveWindow.Close
Application.DisplayAlerts = True

if you don't have the line *Application.DisplayAlerts = False*
you will get the prompt asking whether to save the changes and you have to
hit No.

Good Luck!
Cecil
 
G

Grace

Works like a charm! Tom, you are 'da man'. I hope you don't mind that I
used wbSource rather than wkb! I had this left over (commented out) from
very similar advice I got from Harald on my original post "Re: EXCEL VBA
question". But somehow all the commands we tried could not quite get the
trigger pulled. If you saw that thread and know the magic answer, kindly
tell us. If it's not worth your time, that's fine.

If I could just get you to look at the "re: Code Help" thread, that might be
my last question for awhile! Lest it be confusing, let me just note that
the thread refers to some complicated functions, but that has nothing to do
with the issue. Basically, the function produces a dialog box that allows
me to enter a value for a cell which is blank

Entry = InputBox("Enter a value for selected cell")

However, rather than giving me that dialog box, what I really want to do is
have it allow the user to go directly to the spreadsheet cell and allow the
user to choose the cell's contents from a dropdown that is already installed
in that cell (via data, validation, list). After I use its dropdown to make
a choice, the function (and later the macro) should just continue on its
merry way, just as it does now after inputting to the dialog box. If I have
to hit a "continue" button or something that's OK. I guess I';m asking the
macro to stay "on", while I visit a worksheet cell. I assume this is
do-able.

Thanks,
Grace
 
G

Grace

Thank you very much Cecil, but I read your answer after reading and
implementing Tom's and his worked like a charm. Nevertheless, I appreciate
the effort very much.

Thx,
G
 
T

Tom Ogilvy

You can't really pause a macro to allow the user to work with cells on a
sheet.

You can stop/end the macro - trust the user to do the right thing (select a
value from the dropdown) - the user starts a new macro which is the second
part of your original macro.
 
G

Grace

Aw, shucks! Then, how hard is it to have the dialog box present a
somehow-inherited list, if the choices are in a block of cells on a
spreadsheet? If too hard, how about just hardcoding the list of choices
into the dialog box, as choices you could somehow click on?

Thanks,
G
 
T

Tom Ogilvy

How hard is a relative question.

In my opinion, it is very easy to create a userform with a combobox that
presents your list of choices - the user click on that, the code writes the
value to the worksheet and drops the Userform.

However, if you don't know what a userform or combobox is or how to assign
the rowsource property of the userform or how to extract he values and write
them to the cell, then it would be harder.

A couple of example sources:

http://www.microsoft.com/ExcelDev/Articles/sxs11pt1.htm
Lesson 11: Creating a Custom Form
Excerpted from Microsoft® Excel 97 Visual Basic® Step by Step.


http://support.microsoft.com/default.aspx?kbid=161514
XL97: How to Use a UserForm for Entering Data

http://support.microsoft.com/default.aspx?kbid=213749
XL2000: How to Use a UserForm for Entering Data


Peter Aiken Articles:
Part I
http://msdn.microsoft.com/library/en-us/dnoffpro01/html/IntroductiontoUserFormsPartI.asp
Part II
http://msdn.microsoft.com/library/en-us/dnoffsol02/html/IntroductiontoUserFormsPartII.asp

A reference:
http://support.microsoft.com/?id=168067
File Title: Microsoft(R) Visual Basic(R) for Applications Examples for
Controlling UserForms in Microsoft Excel 97
File Name: WE1163.EXE
File Size: 161742 bytes
File Date: 05/08/97
Keywords: kbfile
Description: This Application Note is an introduction to manipulating
UserForms in Microsoft Excel 97. It includes examples and Microsoft Visual
Basic for Applications macros that show you how to take advantage of the
capabilities of UserForms and use each of the ActiveX controls that are
available for UserForms

http://support.microsoft.com/default.aspx?scid=kb;en-us;829070
How to use Visual Basic for Applications examples to control UserForms in
Microsoft Excel
 
G

Grace

I don't have time to tackle all this right now but you have given me much
resource info, and it doesn't look too hard, so I plan to get to it soon.

Thanks again for all your help Tom! Believe it or not, I think I am in
great shape for my deadline and don't have any other open issues!

G
 
Top