Hi,

as part of a VBA macro in an Excel workbook, I am trying to open a CSV that
has date fields in. Workbooks.open doesn't give me consistent date or text
fields so I can't order them, so instead I am using OpenText.

The problem is that, while I can now open the CSV correctly, I don't seem to
be able to control the new Excel.Application - everything I do still works
on the calling/original Excel.Application.

Edited code consists of:

importFile = Application.GetOpenFilename("Text Files (*.txt), *.txt")
'End macro if no csv selected
If importFile = "False" Then GoTo Exits
Set reportFile = ThisWorkbook
'Copy header from the current workbook
Sheets("Title").Select
Rows("1:1").Select
Selection.Copy
'Create new Workbook with the data I want to copy into the current workbook
Set csvImport = CreateObject("Excel.Application")
csvImport.Workbooks.OpenText Filename:=importFile, _
DataType:=xlDelimited, _
TextQualifier:=xlTextQualifierNone, _
Comma:=True, _
FieldInfo:=Array(Array(1, 2), Array(2, 2), Array(3, 2))
'How do I paste the header row I copied above into the newly created
csvWorkbook...???
Set csvWorkbook = csvImport.ActiveWorkbook
csvImport.Visible = True
csvWorkbook.Activate
csvImport.ActiveWindow.Activate
MsgBox csvWorkbook.Name
'csvWorkbook IS the right thing, but how do I activate it in order to be
able to interact with it?
Rows("1:1").Select
ActiveSheet.Paste
'This pastes into the original, calling "Title" sheet, not the new
csvWorkbook in csvImport!
Cells.Select
Selection.Copy
reportFile.Activate
Sheets("Base").Select
Range("A1").Select
ActiveSheet.Paste
'This ends up copying the whole "Title" sheet and pasting it into the "Base"
sheet, not copying the new csvWorkbook and pasting that


There would appear to be two avenues:

1. Activate csvWorkbook and manipulate it, the commands for which I'm
missing

or

2. run ThisApplication.Workbooks.OpenText ... - but I don't know how to
reference the current application, hence trying to go via creating a new one
and giving it a handle.

Any ideas?

David,
Do you really a 2nd Excel instance ? It is easier to stick in the same
instance.
Also declare all your variables

dim csvImport as workbook

Set csvImport = Workbooks.OpenText Filename:=importFile, _
DataType:=xlDelimited, _
TextQualifier:=xlTextQualifierNone, _
Comma:=True, _
FieldInfo:=Array(Array(1, 2), Array(2, 2), Array(3, 2))


NickHK

"David Evans" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> Hi,
>
> as part of a VBA macro in an Excel workbook, I am trying to open a CSV
that
> has date fields in. Workbooks.open doesn't give me consistent date or text
> fields so I can't order them, so instead I am using OpenText.
>
> The problem is that, while I can now open the CSV correctly, I don't seem
to
> be able to control the new Excel.Application - everything I do still works
> on the calling/original Excel.Application.
>
> Edited code consists of:
>
> importFile = Application.GetOpenFilename("Text Files (*.txt), *.txt")
> 'End macro if no csv selected
> If importFile = "False" Then GoTo Exits
> Set reportFile = ThisWorkbook
> 'Copy header from the current workbook
> Sheets("Title").Select
> Rows("1:1").Select
> Selection.Copy
> 'Create new Workbook with the data I want to copy into the current
workbook
> Set csvImport = CreateObject("Excel.Application")
> csvImport.Workbooks.OpenText Filename:=importFile, _
> DataType:=xlDelimited, _
> TextQualifier:=xlTextQualifierNone, _
> Comma:=True, _
> FieldInfo:=Array(Array(1, 2), Array(2, 2), Array(3, 2))
> 'How do I paste the header row I copied above into the newly created
> csvWorkbook...???
> Set csvWorkbook = csvImport.ActiveWorkbook
> csvImport.Visible = True
> csvWorkbook.Activate
> csvImport.ActiveWindow.Activate
> MsgBox csvWorkbook.Name
> 'csvWorkbook IS the right thing, but how do I activate it in order to be
> able to interact with it?
> Rows("1:1").Select
> ActiveSheet.Paste
> 'This pastes into the original, calling "Title" sheet, not the new
> csvWorkbook in csvImport!
> Cells.Select
> Selection.Copy
> reportFile.Activate
> Sheets("Base").Select
> Range("A1").Select
> ActiveSheet.Paste
> 'This ends up copying the whole "Title" sheet and pasting it into the
"Base"
> sheet, not copying the new csvWorkbook and pasting that
>
>
> There would appear to be two avenues:
>
> 1. Activate csvWorkbook and manipulate it, the commands for which I'm
> missing
>
> or
>
> 2. run ThisApplication.Workbooks.OpenText ... - but I don't know how to
> reference the current application, hence trying to go via creating a new
one
> and giving it a handle.
>
> Any ideas?
>
>