You are here:

Excel/Input file copy macro

Advertisement


Question
QUESTION: Tom,

  Here's Part II for the application you helped me with yesterday.

  I want to see how much of the following procedure I could automate with a macro in a workbook called the Translator.

Goal: the customer needs to open a CSV file and paste the table contained therein, into a certain sheet of the Translator.  The filename of the CSV file is variable and won't be known until the user actually finds it.  The headers of the table should be included.

1) Use opens Translator and selects page "paste here"
2) User opens CSV file with same instance of Excel
3) user selects entire table in the CSV file.  
4) user COPIES selected table
5) user switches back to the Translator.
6) User PASTES copied table into Paste Here sheet starting at cell A1

I have no idea how to do this, especially given the uncertain filename of the CSV file.  But I bet you can find a way, at least partially!

  >>> Bill

ANSWER: Bill,

the user manually opens Translator and select page "paste here" to be the active sheet.

The user then runs this macro and when prompted, selects the CSV file to be copied.

Sub OpenCSV()
Dim fName As Variant
Dim bk As Workbook
Dim bk1 As Workbook
Dim sh As Worksheet
Dim sh1 As Worksheet
Set bk = ActiveWorkbook
Set sh = ActiveSheet
fName = Application.GetOpenFilename(FileFilter:="CSV Files (*.csv),*.csv", _
       Title:="Select File Name", MultiSelect:=False)
If fName = False Then Exit Sub
Workbooks.Open fName
Set bk1 = ActiveWorkbook
Set sh1 = ActiveSheet
sh1.Range("A1").CurrentRegion.Copy sh.Range("A1")
Set sh1 = Nothing
bk1.Close Savechanges:=False
Set bk1 = Nothing
End Sub

The macro selects and copies the entire table given that there are no blank rows in the table.

--
Regards,
Tom Ogilvy


---------- FOLLOW-UP ----------

QUESTION: Tom,

  A more perfect solution I cannot imagine!  Super thanks!

  .... with one caveat.  I want to assign this macro to a button that's on another page (not on the Paste Here page).  It must be a simple matter to make Paste here be the active sheet from within the macro.....

  Then it WILL be perfect!

  >>> Bill

Answer
Bill,

Sub OpenCSV()
Dim fName As Variant
Dim bk As Workbook
Dim bk1 As Workbook
Dim sh As Worksheet
Dim sh1 As Worksheet
Set bk = ActiveWorkbook
Set sh = bk.worksheets("Paste Here")     'ActiveSheet
fName = Application.GetOpenFilename(FileFilter:="CSV Files (*.csv),*.csv", _
      Title:="Select File Name", MultiSelect:=False)
If fName = False Then Exit Sub
Workbooks.Open fName
Set bk1 = ActiveWorkbook
Set sh1 = ActiveSheet
sh1.Range("A1").CurrentRegion.Copy sh.Range("A1")
Set sh1 = Nothing
bk1.Close Savechanges:=False
Set bk1 = Nothing
End Sub

--
Regards,
Tom Ogilvy

About Excel
This topic answers questions related to Microsoft Excel spreadsheet (or workbook) stand-alone or Mircrosoft Office Excel including Excel 2003, Excel 2007, Office 2000, and Office XP. You can get Excel help on Excel formulas(or functions), Excell macros, charting in Excel, advanced features, and the general use of Excel. This does not provide a general Excel tutorial nor the basics of using a spreadsheet. It provides specific answers to using Microsoft Excel only. If you do not see your Excel question answered in this area then please ask an Excel question here

Excel

All Answers


Answers by Expert:


Ask Experts

Volunteer


Tom Ogilvy

Expertise

Selected as an Excel MVP by Microsoft since 1999. Answering Excel questions in Allexperts since its inception in 2001. Able to answer questions on almost all aspects of Excel's internal capabilities. If seeking a VBA solution, please specify that in your question itself so I give you the answer you want. [Excel has weak protection - if you are distributing an application, I don't answer questions on how to protect your project from your users.]

Experience

Extensive experience.

Education/Credentials
Master of Science (MS) degree Operations Research (ORSA)

Awards and Honors
Microsoft MVP in Excel.

©2016 About.com. All rights reserved.