import excel data

[Faz]

How do i import excel data from 3 different excel files and write them to a
new excel file. Each files will be imported to adjacent columns of the new
file on the 2nd sheet. Only the new file will be visible.
Please i need this urgently for a project at work
Thanks
Faz

 

[J Sedoff comRemove>]

You should be able to do this manually by copying and pasting; or doing it
with macros, which is more complicated. Are you doing this just once (do it
manually), or multiple times (write a macro)?

Sub copyStuff()
'Copy Column A from file 1 to Sheet2, starting in cell A1
Windows("FileName1").Activate
Columns("A:A").Copy
Windows("Book2").Activate
Sheets("Sheet2").Range("A1").Select
ActiveSheet.Paste

'Copy Column A from file 2 to Sheet2, starting in cell B1
Windows("FileName2").Activate
Columns("A:A").Copy
Windows("Book2").Activate
Range("B1").Select
ActiveSheet.Paste

'Copy Column A from file 3 to Sheet2, starting in cell C1
Windows("FileName3").Activate
Columns("A:A").Copy
Windows("Book2").Activate
Range("C1").Select
ActiveSheet.Paste

Application.CutCopyMode = False
End Sub


Hope this helps,
Jim

 

[Faz]

I have started with a macro as shown below. Now i need to change the file
location and filenames in possibly a loop. I have about 250 of these files to
combine into one. Is there a way for me to read the filename and locations in
a loop.

I really appreciate this J
Thanks
Faz

Sub Importdata()
'
' NewBook Macro
'
'
Workbooks.Add

'
' gotosheet1
'
'
Sheets("Sheet1").Select
Range("A1").Select

'
' FileLocations
'
'
With ActiveSheet.ListObjects.Add(SourceType:=0, Source:=Array( _
"OLEDB;Provider=Microsoft.ACE.OLEDB.12.0;Password="""";User
ID=Admin;Data
Source=G:\Research\LII\Research\LII\Administration\Clients\NYSE" _
, _
"RDA\File Locations.xls;Mode=Share Deny Write;Extended
Properties=""HDR=YES;"";Jet OLEDB:System database="""";Jet OLEDB:Registry
Path" _
, _
"="""";Jet OLEDBatabase Password="""";Jet OLEDB:Engine Type=35;Jet
OLEDBatabase Locking Mode=0;Jet OLEDB:Global Partial Bulk Ops=" _
, _
"2;Jet OLEDB:Global Bulk Transactions=1;Jet OLEDB:New Database
Password="""";Jet OLEDB:Create System Database=False;Jet OLEDB:Encry" _
, _
"pt Database=False;Jet OLEDBon't Copy Locale on Compact=False;Jet
OLEDB:Compact Without Replica Repair=False;Jet OLEDB:SFP=Fals" _
, "e;Jet OLEDB:Support Complex Data=False"),
Destination:=Range("$A$1")). _
QueryTable
.CommandType = xlCmdTable
.CommandText = Array("'File Locations$'")
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.PreserveColumnInfo = True
.SourceDataFile = _
"G:\Research\LII\Research\LII\Administration\Clients\NYSERDA\File
Locations.xls"
.ListObject.DisplayName = "Table_File_Locations"
.Refresh BackgroundQuery:=False
End With

'
' gotosheet2
'
'
Sheets("Sheet2").Select
Range("A1").Select



'
' LIIFN1
'
'
With ActiveSheet.QueryTables.Add(Connection:= _

"TEXT;G:\Research\LII\Research\LII\Administration\Clients\NYSERDA\NYSERDA
Files\22-Jul-2008\24_50DPF_22Jul_LII_2008-07-22 15 39 50 NYSERDA Mass Vs
Time.csv" _
, Destination:=Range("Sheet2!$A$1"))
.Name = "24_50DPF_22Jul_LII_2008-07-22 15 39 50 NYSERDA Mass Vs Time"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.TextFilePromptOnRefresh = False
.TextFilePlatform = 932
.TextFileStartRow = 1
.TextFileParseType = xlDelimited
.TextFileTextQualifier = xlTextQualifierDoubleQuote
.TextFileConsecutiveDelimiter = False
.TextFileTabDelimiter = True
.TextFileSemicolonDelimiter = True
.TextFileCommaDelimiter = True
.TextFileSpaceDelimiter = False
.TextFileColumnDataTypes = Array(1, 1, 1, 1)
.TextFileTrailingMinusNumbers = True
.Refresh BackgroundQuery:=False
End With
Sheets("Sheet2").Select
'
' ECMFN1
'
'
Range("F19").Select
ActiveWindow.WindowState = xlNormal
With ActiveSheet.ListObjects.Add(SourceType:=0, Source:=Array( _
"OLEDB;Provider=Microsoft.ACE.OLEDB.12.0;Password="""";User
ID=Admin;Data
Source=G:\Research\LII\Research\LII\Administration\Clients\NYSE" _
, _
"RDA\NYSERDA Files\ECM
Data\22july08\pp_22JUL08_Test-24_100DPF---1_M01-M02.xls;Mode=Share Deny
Write;Extended Properties=""HDR=YES" _
, _
";"";Jet OLEDB:System database="""";Jet OLEDB:Registry Path="""";Jet
OLEDBatabase Password="""";Jet OLEDB:Engine Type=35;Jet OLEDBat" _
, _
"abase Locking Mode=0;Jet OLEDB:Global Partial Bulk Ops=2;Jet
OLEDB:Global Bulk Transactions=1;Jet OLEDB:New Database Password=""""" _
, _
";Jet OLEDB:Create System Database=False;Jet OLEDB:Encrypt
Database=False;Jet OLEDBon't Copy Locale on Compact=False;Jet OLEDB:" _
, _
"Compact Without Replica Repair=False;Jet OLEDB:SFP=False;Jet
OLEDB:Support Complex Data=False" _
), Destination:=Range("$E$1")).QueryTable
.CommandType = xlCmdTable
.CommandText = Array("'pp_22JUL08_Test-24_100DPF---1_M$'")
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.PreserveColumnInfo = True
.SourceDataFile = _
"G:\Research\LII\Research\LII\Administration\Clients\NYSERDA\NYSERDA
Files\ECM Data\22july08\pp_22JUL08_Test-24_100DPF---1_M01-M02.xls"
.ListObject.DisplayName =
"Table_pp_22JUL08_Test_24_100DPF___1_M01_M02"
.Refresh BackgroundQuery:=False
End With
Columns("F:F").Select
Selection.NumberFormat = "hh:mm:ss;@"
Range("G19").Select
Columns("F:F").ColumnWidth = 10.71

'
' SEMTECHFN1SN1
'
'
With ActiveSheet.ListObjects.Add(SourceType:=0, Source:=Array( _
"OLEDB;Provider=Microsoft.ACE.OLEDB.12.0;Password="""";User
ID=Admin;Data
Source=G:\Research\LII\Research\LII\Administration\Clients\NYSE" _
, _
"RDA\NYSERDA Files\hd eng data\hd eng info july22 23
08.xls;Mode=Share Deny Write;Extended Properties=""HDR=YES;"";Jet
OLEDB:System" _
, _
" database="""";Jet OLEDB:Registry Path="""";Jet OLEDBatabase
Password="""";Jet OLEDB:Engine Type=35;Jet OLEDBatabase Locking Mode=" _
, _
"0;Jet OLEDB:Global Partial Bulk Ops=2;Jet OLEDB:Global Bulk
Transactions=1;Jet OLEDB:New Database Password="""";Jet OLEDB:Create S" _
, _
"ystem Database=False;Jet OLEDB:Encrypt Database=False;Jet
OLEDBon't Copy Locale on Compact=False;Jet OLEDB:Compact Without Rep" _
, "lica Repair=False;Jet OLEDB:SFP=False;Jet OLEDB:Support Complex
Data=False") _
, Destination:=Range("$AA$1")).QueryTable
.CommandType = xlCmdTable
.CommandText = Array("'24_dpf100jul22t1$'")
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.PreserveColumnInfo = True
.SourceDataFile = _
"G:\Research\LII\Research\LII\Administration\Clients\NYSERDA\NYSERDA
Files\hd eng data\hd eng info july22 23 08.xls"
.ListObject.DisplayName = "Table_hd_eng_info_july22_23_08"
.Refresh BackgroundQuery:=False
End With


End Sub

 

[Faz]

Hi Jim,

I tried your method and it works better for me. It is so simple i did'n even
think of it at first. Thank you very much. I am having problems with the
statement below using the macro method.i.e.
liifn1 = Application.GetOpenFilename("All Files (*.csv), *.txt")
If liifn1 <> False Then
MsgBox "Open " & liifn1
End If
With ActiveSheet.QueryTables.Add(Connection:="Text;" & liifn1,
Destination:=Range("Sheet2!$A$1"))
It does import data, but in an unrecognisable format, a mixture of symbols
and text. Can you please help me with this?

Thanks

 

[J Sedoff comRemove>]

Try removing ("All Files (*.csv), *.txt"), so that the line only reads:
liifn1 = Application.GetOpenFilename

That will also allow you to open any files, not just .txt and .csv files
(that might explain the random symbols and such).

Hope that helps, Jim

 

[Faz]

Jim,
Removing ("All Files (*.csv), *.txt"), made no difference. I think that the
problem lies in the statement below..............
With ActiveSheet.ListObjects.Add(SourceType:=0, Source:=Array( _

"OLEDB;Provider=Microsoft.ACE.OLEDB.12.0;Password="""";User
ID=Admin;Data
Source=G:\Research\LII\Research\LII\Administration\Clients\NYSE" _
, _
"RDA\File Locations.xls;Mode=Share Deny Write;Extended
Properties=""HDR=YES;"";Jet OLEDB:System database="""";Jet OLEDB:Registry
Path" _
, _
"="""";Jet OLEDBatabase Password="""";Jet OLEDB:Engine Type=35;Jet
OLEDBatabase Locking Mode=0;Jet OLEDB:Global Partial Bulk Ops=" _
, _
"2;Jet OLEDB:Global Bulk Transactions=1;Jet OLEDB:New Database
Password="""";Jet OLEDB:Create System Database=False;Jet OLEDB:Encry" _
, _
"pt Database=False;Jet OLEDBon't Copy Locale on Compact=False;Jet
OLEDB:Compact Without Replica Repair=False;Jet OLEDB:SFP=Fals" _
, "e;Jet OLEDB:Support Complex Data=False"),
Destination:=Range("$A$1")). _

WHICH I REPLACED WITH
.....................................
FILENAME = Application.GetOpenFilename("All Files (*.csv), *.txt")
If FILENAME <> False Then
MsgBox "Open " & FILENAME
End If
With ActiveSheet.QueryTables.Add(Connection:="Text;" & FILENAME,
Destination:=Range("Sheet2!$A$1"))

SO THAT I CAN ENTER THE FILENAME AS NEEDED. i REALLY NEED A WAY OF ENTERING
THE FILENAME INSTEAD OF HAVING IT AS BELOW.
..............................................................................
Data

Source=G:\Research\LII\Research\LII\Administration\Clients\NYSE" _

Thanks Jim
Faz

 

[J Sedoff comRemove>]

The Help section in Excel's VBA told me this:

expression.Add(Connection, Destination, Sql)

Destination Required Range. The cell in the upper-left corner of the query table >destination range (the range where the resulting query table will be placed). The >destination range must be on the worksheet that contains the QueryTables object >specified by expression

I noticed you have ActiveSheet.Add... etc, but your Destination is on
"Sheet2." You might consider changing the ActiveSheet to Sheet2. Maybe that
would fix it?

FILENAME = Application.GetOpenFilename("All Files (*.csv), *.txt")
If FILENAME <> False Then
MsgBox "Open " & FILENAME
End If
With ActiveSheet.QueryTables.Add(Connection:="Text;" & FILENAME,
Destination:=Range("Sheet2!$A$1"))

Change last line to:

With Sheet2.QueryTables.Add(Connection:="Text;" & FILENAME, _
Destination:=Range("Sheet2!$A$1"))

I haven't used the QueryTables command before, and I don't really know how
to mimic the situation, unfortunately. And I understand the need to not have
a hardcoded file path like:

Data Source=G:\Research\LII\Research\LII\Administration\Clients\NYSE"

It'll always find a way to nip you in the butt down the road (like in 5
years when they change the folder tree organization).

Hope this helps, Jim