Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Cutting/pasting from Excel.Application object - missing the obvious?
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? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Cutting/pasting from Excel.Application object - missing the obvious?
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" wrote in message ... 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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Problem Cutting and Pasting from SQL Table to Excel Sheet | Excel Discussion (Misc queries) | |||
Cutting and Pasting in Excel | Excel Discussion (Misc queries) | |||
can excel auto correct when cutting and pasting | Excel Discussion (Misc queries) | |||
Must Be Missing Something Obvious About ListBoxes | Excel Programming | |||
Application-defined or object-defined error - missing the basics | Excel Programming |