Closing non EXCEL files
 

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

Closing non EXCEL files
 

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

--
Tom Ogilvy


"Grace" wrote in message
...
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

Closing non EXCEL files
 

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

"Tom Ogilvy" wrote in message
...
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

--
Tom Ogilvy


"Grace" wrote in message
...
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

Closing non EXCEL files
 

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.

--
Regards,
Tom Ogilvy



"Grace" wrote in message
...
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

"Tom Ogilvy" wrote in message
...
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

--
Tom Ogilvy


"Grace" wrote in message
...
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

Closing non EXCEL files
 

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

"Tom Ogilvy" wrote in message
...
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.

--
Regards,
Tom Ogilvy



"Grace" wrote in message
...
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

"Tom Ogilvy" wrote in message
...
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

--
Tom Ogilvy


"Grace" wrote in message
...
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

Closing non EXCEL files
 

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

"Grace" wrote in message
...
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

"Tom Ogilvy" wrote in message
...
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.

--
Regards,
Tom Ogilvy



"Grace" wrote in message
...
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

"Tom Ogilvy" wrote in message
...
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

--
Tom Ogilvy


"Grace" wrote in message
...
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

Closing non EXCEL files
 

You said:

Just to be clear, it is brought in as a text type file and CSVd.

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.

--
Regards,
Tom Ogilvy

"Grace" wrote in message
...
Also, before you try to close it, make some changes to it, first, in case
that matters.

"Grace" wrote in message
...
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

"Tom Ogilvy" wrote in message
...
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.

--
Regards,
Tom Ogilvy



"Grace" wrote in message
...
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

"Tom Ogilvy" wrote in message
...
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

--
Tom Ogilvy


"Grace" wrote in message
...
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

Closing non EXCEL files
 

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



"Tom Ogilvy" wrote in message
...
You said:

Just to be clear, it is brought in as a text type file and CSVd.

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.

--
Regards,
Tom Ogilvy

"Grace" wrote in message
...
Also, before you try to close it, make some changes to it, first, in
case
that matters.

"Grace" wrote in message
...
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

"Tom Ogilvy" wrote in message
...
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.

--
Regards,
Tom Ogilvy



"Grace" wrote in message
...
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

"Tom Ogilvy" wrote in message
...
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

--
Tom Ogilvy


"Grace" wrote in message
...
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

Closing non EXCEL files
 

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

"Grace" wrote in message
...
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



"Tom Ogilvy" wrote in message
...
You said:

Just to be clear, it is brought in as a text type file and CSVd.

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.

--
Regards,
Tom Ogilvy

Closing non EXCEL files
 

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

"Cecilkumara Fernando" <cekufdo@sltnetDOTlk wrote in message
...
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

"Grace" wrote in message
...
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



"Tom Ogilvy" wrote in message
...
You said:

Just to be clear, it is brought in as a text type file and CSVd.

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.

--
Regards,
Tom Ogilvy

Closing non EXCEL files
 

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

"Grace" wrote in message
...
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

Closing non EXCEL files
 

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

"Cecilkumara Fernando" <cekufdo@sltnetDOTlk wrote in message
...
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

"Grace" wrote in message
...
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

Closing non EXCEL files
 

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

--
Regards,
Tom Ogilvy



"Grace" wrote in message
...
It is situation 2. The calling file is wbTarget. I being isn 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

"Cecilkumara Fernando" <cekufdo@sltnetDOTlk wrote in message
...
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

"Grace" wrote in message
...
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

Closing non EXCEL files
 

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
"Grace" wrote in message
...
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

"Cecilkumara Fernando" <cekufdo@sltnetDOTlk wrote in message
...
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

"Grace" wrote in message
...
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

Closing non EXCEL files
 

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 " 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 " 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



"Tom Ogilvy" wrote in message
...
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

--
Regards,
Tom Ogilvy



"Grace" wrote in message
...
It is situation 2. The calling file is wbTarget. I being isn 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

"Cecilkumara Fernando" <cekufdo@sltnetDOTlk wrote in message
...
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

"Grace" wrote in message
...
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

Closing non EXCEL files
 

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

"Cecilkumara Fernando" <cekufdo@sltnetDOTlk wrote in message
...
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
"Grace" wrote in message
...
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

"Cecilkumara Fernando" <cekufdo@sltnetDOTlk wrote in message
...
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

"Grace" wrote in message
...
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

Closing non EXCEL files
 

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.


--
Regards,
Tom Ogilvy


"Grace" wrote in message
...
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 " 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 " 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



"Tom Ogilvy" wrote in message
...
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

--
Regards,
Tom Ogilvy



"Grace" wrote in message
...
It is situation 2. The calling file is wbTarget. I being isn 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

"Cecilkumara Fernando" <cekufdo@sltnetDOTlk wrote in message
...
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

"Grace" wrote in message
...
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

Closing non EXCEL files
 

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

"Tom Ogilvy" wrote in message
...
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.


--
Regards,
Tom Ogilvy


"Grace" wrote in message
...
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 " 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 " 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



"Tom Ogilvy" wrote in message
...
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

--
Regards,
Tom Ogilvy



"Grace" wrote in message
...
It is situation 2. The calling file is wbTarget. I being isn 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

"Cecilkumara Fernando" <cekufdo@sltnetDOTlk wrote in message
...
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

"Grace" wrote in message
...
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

Closing non EXCEL files
 

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...FormsPartI.asp
Part II
http://msdn.microsoft.com/library/en...ormsPartII.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...b;en-us;829070
How to use Visual Basic for Applications examples to control UserForms in
Microsoft Excel

--
Regards,
Tom Ogilvy


"Grace" wrote in message
...
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

"Tom Ogilvy" wrote in message
...
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.


--
Regards,
Tom Ogilvy


"Grace" wrote in message
...
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 " 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 " 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



"Tom Ogilvy" wrote in message
...
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

--
Regards,
Tom Ogilvy



"Grace" wrote in message
...
It is situation 2. The calling file is wbTarget. I being isn
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

"Cecilkumara Fernando" <cekufdo@sltnetDOTlk wrote in message
...
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

"Grace" wrote in message
...
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

Closing non EXCEL files
 

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

"Tom Ogilvy" wrote in message
...
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...FormsPartI.asp
Part II

http://msdn.microsoft.com/library/en...ormsPartII.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...b;en-us;829070
How to use Visual Basic for Applications examples to control UserForms in
Microsoft Excel

--
Regards,
Tom Ogilvy


"Grace" wrote in message
...
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

"Tom Ogilvy" wrote in message
...
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.


--
Regards,
Tom Ogilvy


"Grace" wrote in message
...
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 " 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 " 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



"Tom Ogilvy" wrote in message
...
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

--
Regards,
Tom Ogilvy



"Grace" wrote in message
...
It is situation 2. The calling file is wbTarget. I being isn
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

"Cecilkumara Fernando" <cekufdo@sltnetDOTlk wrote in message
...
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

"Grace" wrote in message
...
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