File Importing

  • Thread starter Thread starter HJones
  • Start date Start date
H

HJones

Hello

I am creating a spreadsheet in which I need to import 5
data files onto separate pages. I am able to import them
as text files using the following commands I found on the
Excel help site.

My question is: is there a way to reference a specific
cell containing the entire filename, thus automating the
entire process, instead of just opening the "open dialog"
window and having to manually select the file to open?

I am using Excel 2002 on an XP-Pro system.

Thanks for any help you can offer.


Dim DestBook As Workbook, SourceBook As Workbook
Dim DestCell As Range
Dim RetVal As Boolean

' Turn off screen updating.
Application.ScreenUpdating = False

' Set object variables for the active book and active
cell.
Set DestBook = ActiveWorkbook
Set DestCell = ActiveCell

' Show the Open dialog box.
RetVal = Application.Dialogs(xlDialogOpen).Show
("c:\Magnum\Data")

' If Retval is false (Open dialog canceled), exit the
procedure.
If RetVal = False Then Exit Sub

' Set an object variable for the workbook containing
the text file.
Set SourceBook = ActiveWorkbook

' Copy the contents of the entire sheet containing
the text file.
Range(Range("A1"), Range("A1").SpecialCells
(xlLastCell)).Copy

' Activate the destination workbook and paste special
the values
' from the text file.
DestBook.Activate
DestCell.PasteSpecial Paste:=xlValues

' Close the book containing the text file.
SourceBook.Close False
 
Hi,
Have you tired using Data|Import?
You could record a macro for this operation - it asks which cell to imported
into.
Bernard
 
Back
Top