import excel data
 

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

import excel data
 

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
--
I appreciate any feedback.

import excel data
 

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 OLEDB:Database Password="""";Jet OLEDB:Engine Type=35;Jet
OLEDB:Database 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 OLEDB:Don'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\Clien ts\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
OLEDB:Database Password="""";Jet OLEDB:Engine Type=35;Jet OLEDB:Dat" _
, _
"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 OLEDB:Don'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\Clien ts\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 OLEDB:Database
Password="""";Jet OLEDB:Engine Type=35;Jet OLEDB:Database 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
OLEDB:Don'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\Clien ts\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



"J Sedoff" wrote:

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
--
I appreciate any feedback.

import excel data
 

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

"Faz" wrote:

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 OLEDB:Database Password="""";Jet OLEDB:Engine Type=35;Jet
OLEDB:Database 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 OLEDB:Don'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\Clien ts\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
OLEDB:Database Password="""";Jet OLEDB:Engine Type=35;Jet OLEDB:Dat" _
, _
"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 OLEDB:Don'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\Clien ts\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 OLEDB:Database
Password="""";Jet OLEDB:Engine Type=35;Jet OLEDB:Database 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
OLEDB:Don'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\Clien ts\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



"J Sedoff" wrote:

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
--
I appreciate any feedback.

import excel data
 

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
--
I appreciate any feedback.

import excel data
 

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 OLEDB:Database Password="""";Jet OLEDB:Engine Type=35;Jet
OLEDB:Database 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 OLEDB:Don'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" wrote:

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
--
I appreciate any feedback.

import excel data
 

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
--
I appreciate any feedback.