macro-import another xls file

puiuluipui · Mar 31, 2010

Hi, i need a macro to import another xls file.When i run macro i need a
window to appear and to browse to the file i need to import. I allways open
another file, so i want to browse for the file.
Can this be done?
Thanks!

Chip Pearson · Mar 31, 2010

Try some code like the following:

Sub AAA()
Dim FName As Variant
Dim Filter As String
' Excel 2003 workbooks
Filter = "Excel Files (*.xls),*.xls"
' Excel 2007 workbooks workbooks
Filter = "Excel Files (*.xlsx;*.xlsm;*.xlsb),*.xlsx;*.xlsm;*.xlsb"
' Excel 2003 and 2007
Filter = "Excel Files
(*.xls;*.xlsx;*.xlsm;*.xlsb),*.xls;*.xlsx;*.xlsm;*.xlsb"

FName = Application.GetOpenFilename(filefilter:=Filter)
If FName = False Then
Debug.Print "cancel"
Else
Debug.Print "selected: " & FName
End If
End Sub

Cordially,
Chip Pearson
Microsoft Most Valuable Professional,
Excel, 1998 - 2010
Pearson Software Consulting, LLC
www.cpearson.com

puiuluipui · Apr 1, 2010

Hi, i have some problems.
The code run corectly and browse for whatever file i need, but when i click
that file, it doesn;t import anything!
What am i doing wrong? I have excel 2003.
Thanks!

"Chip Pearson" a scris:

Dave Peterson · Apr 1, 2010

Chip's code shows you how to get the name of the file from the user. It doesn't
do the actual import.

You'll have to add that portion yourself -- or explain what you're doing in
greater detail.

puiuluipui · Apr 1, 2010

Hi, i just want to import an xls file, but thru an macro, and after i import
the file, i will add some codes myself. I need to hit a button linked to an
macro and the macro to open an browse window. I will select the file i need,
hit ok and the excel to import in active sheet, that file. After this, the
macro will format the content, but this is another problem i will deal with
later. I only need the macro to import a file by browsing after it.

Can this be done?
Thanks!

"Dave Peterson" a scris:

Chip Pearson · Apr 1, 2010

What do you mean by "import"? The code I posted will display the file
open dialog to prompt you to select the file. Once that is complete,
you need to open the file:

Dim WB As Workbook
Set WB = Application.Workbooks.Open(Fname)

where FName is the file name selected in the Open dialog. Beyond that
you need to explain just you mean by "want to import an xls".

Cordially,
Chip Pearson
Microsoft Most Valuable Professional,
Excel, 1998 - 2010
Pearson Software Consulting, LLC
www.cpearson.com

Gord Dibben · Apr 1, 2010

Opening a file does not "import" it into anything.

What portion of that opened file do you need imported to an active workbook?

That's what you need to describe.

Gord Dibben MS Excel MVP

puiuluipui · Apr 1, 2010

Hi, i will tell you what i tried, step by step. I'm not good at explaining,
so if i tell you what steps i've made you will understand better.
Tools>Macro>Record new macro>Data>Import external data>Import data and from
the browse window i selected an excel file that has been imported. This is
the code:

Sub Macro1()
'
' Macro1 Macro
' Macro recorded 01.04.2010 by puiuluipui
'

'
With ActiveSheet.QueryTables.Add(Connection:=Array( _
"OLEDB;Provider=Microsoft.Jet.OLEDB.4.0;Password="""";User
ID=Admin;Data Source=C:\Users\puiuluipui\Desktop\Alex\excel.xls;Mode=Share
Den" _
, _
"y Write;Extended Properties=""HDR=YES;"";Jet OLEDB:System
database="""";Jet OLEDB:Registry Path="""";Jet OLEDB

atabase
Password="""";Je" _
, _
"t OLEDB:Engine Type=35;Jet OLEDB

atabase 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

on't Cop" _
, _
"y Locale on Compact=False;Jet OLEDB:Compact Without Replica
Repair=False;Jet OLEDB:SFP=False" _
), Destination:=Range("A1"))
.CommandType = xlCmdTable
.CommandText = Array("Sheet1$")
.Name = "excel"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.PreserveColumnInfo = True
.SourceDataFile = "C:\Users\puiuluipui\Desktop\Alex\excel.xls"
.Refresh BackgroundQuery:=False
End With
End Sub

The problem is that this macro will allways open only the file named "excel".
What i need is that after i run this code, to be able to choose what file i
want.

I guess that some lines from this code must be changed. Perhaps the lines
containing the file source.

Can this code be made to let me choose everytime what file to import?
Basically to do exactly like i did in the first place when i recorded the
macro.

Can this be done?
Thanks!

Dave Peterson · Apr 1, 2010

Try:

Sub Macro1()
Dim FName As Variant
Dim Filter As String
' Excel 2003 workbooks
Filter = "Excel Files (*.xls),*.xls"
' Excel 2007 workbooks workbooks
Filter = "Excel Files (*.xlsx;*.xlsm;*.xlsb),*.xlsx;*.xlsm;*.xlsb"
' Excel 2003 and 2007
Filter = "Excel Files
(*.xls;*.xlsx;*.xlsm;*.xlsb),*.xls;*.xlsx;*.xlsm;*.xlsb"

FName = Application.GetOpenFilename(filefilter:=Filter)
If FName = False Then
exit sub 'user hit cancel
end if

With ActiveSheet.QueryTables.Add(Connection:=Array( _
"OLEDB;Provider=Microsoft.Jet.OLEDB.4.0;Password="""";" _
& "User ID=Admin;Data Source=" & fname & ";Mode=Share Den" _

....rest of your recorded code here.

.....

(Untested, uncompiled. Watch for typos.)

puiuluipui · Apr 1, 2010

It works great!
Thanks!

Dave Peterson said:
Try:

Sub Macro1()
Dim FName As Variant
Dim Filter As String
' Excel 2003 workbooks
Filter = "Excel Files (*.xls),*.xls"
' Excel 2007 workbooks workbooks
Filter = "Excel Files (*.xlsx;*.xlsm;*.xlsb),*.xlsx;*.xlsm;*.xlsb"
' Excel 2003 and 2007
Filter = "Excel Files
(*.xls;*.xlsx;*.xlsm;*.xlsb),*.xls;*.xlsx;*.xlsm;*.xlsb"

FName = Application.GetOpenFilename(filefilter:=Filter)
If FName = False Then
exit sub 'user hit cancel
end if

With ActiveSheet.QueryTables.Add(Connection:=Array( _
"OLEDB;Provider=Microsoft.Jet.OLEDB.4.0;Password="""";" _
& "User ID=Admin;Data Source=" & fname & ";Mode=Share Den" _

....rest of your recorded code here.

.....

(Untested, uncompiled. Watch for typos.)

Pierre Hurbault · Nov 12, 2010

Hello both,
After having searched for hours I finally found your thread with exactly what I needed. I'm no VBA expert as I'm not using it on a regular basis, but I can understand it fine. I tried to apply your solution here, also recording my own macro at some point, however it doesn't work and I've tried many ways. The answer must be simple but I just can't find it. I'd be really grateful if you could help me with it.

Here is my code ( part of a much longer one )
Private Sub Cmdyes_Click()
Dim PctDone As Single

'-------------------------------------TOTAL ELECTRIC CONSUMPTION--------------------
Dim sFileName As Variant
Dim Filter As String

Filter = "Excel Files(*.xls;*.xlsx;*.xlsm;*.xlsb),*.xls;*.xlsx;*.xlsm;*.xlsb"

sFileName = Application.GetOpenFilename(filefilter:=Filter)
If sFileName = False Then
End 'user hit cancel
End If

'--------------------THAT IS THE ORIGINAL RECORDED MACRO, WHICH WORKS ALONE-------------
' With ActiveSheet.QueryTables.Add(Connection:=Array( _
' "OLEDB;Provider=Microsoft.Jet.OLEDB.4.0;Password="""";User ID=Admin;Data Source=""P:\Departmental Folders\FCM - Facilities Management\270_" _
, _
' "Interns folders\60_Pierre-Nils's Files\Energy Consumption Report\Total Consumption Data\Bosch - Warndon - HHD - 29.10.10 to 04.1" _
, _
' "1.10.XLS"";Mode=Share Deny Write;Extended Properties=""HDR=YES;"";Jet OLEDB:System database="""";Jet OLEDB:Registry Path="""";Jet OLEDB" _
, _
"

atabase Password="""";Jet OLEDB:Engine Type=35;Jet OLEDB

atabase Locking Mode=0;Jet OLEDB:Global Partial Bulk Ops=2;Jet OLEDB:G" _
, _
"lobal Bulk Transactions=1;Jet OLEDB:New Database Password="""";Jet OLEDB:Create System Database=False;Jet OLEDB:Encrypt Database=F" _
, _
"alse;Jet OLEDB

on't Copy Locale on Compact=False;Jet OLEDB:Compact Without Replica Repair=False;Jet OLEDB:SFP=False" _
), Destination:=Range("A115"))
' .CommandType = xlCmdTable
' .CommandText = Array("'Sheet #1$'")
' .Name = "Bosch - Warndon - HHD - 29.10.10 to 04.11.10"
' .FieldNames = True
' .RowNumbers = False
' .FillAdjacentFormulas = False
' .PreserveFormatting = True
' .RefreshOnFileOpen = False
' .BackgroundQuery = True
' .RefreshStyle = xlInsertDeleteCells
' .SavePassword = False
' .SaveData = True
' .AdjustColumnWidth = True
' .RefreshPeriod = 0
' .PreserveColumnInfo = True
' .SourceDataFile = _
' "P:\Departmental Folders\FCM - Facilities Management\270_Interns folders\60_Pierre-Nils's Files\Energy Consumption Report\Total Consumption Data\Bosch - Warndon - HHD - 29.10.10 to 04.11.10.XLS"
' .Refresh BackgroundQuery:=False
End With

'--------------------THIS IS THE ONE I TRIED TO REPLACE WITH sFileName-------------
With ActiveSheet.QueryTables.Add(Connection:=Array( _
"OLEDB;Provider=Microsoft.Jet.OLEDB.4.0;Password="""";User ID=Admin;Data Source=" _
& sFileName & _
";Mode=Share Deny Write;Extend" _
, _
"ed Properties=""HDR=YES;"";Jet OLEDB:System database="""";Jet OLEDB:Registry Path="""";Jet OLEDB

atabase Password="""";Jet OLEDB:Engine" _
, _
" Type=35;Jet OLEDB

atabase Locking Mode=0;Jet OLEDB:Global Partial Bulk Ops=2;Jet OLEDB:Global Bulk Transactions=1;Jet OLEDB:Ne" _
, _
"w Database Password="""";Jet OLEDB:Create System Database=False;Jet OLEDB:Encrypt Database=False;Jet OLEDB

on't Copy Locale on Co" _
, _
"mpact=False;Jet OLEDB:Compact Without Replica Repair=False;Jet OLEDB:SFP=False" _
), Destination:=Range("D115"))
..CommandType = xlCmdTable
..CommandText = Array("Sheet1$")
..Name = "Total Consumption"
..FieldNames = True
..RowNumbers = False
..FillAdjacentFormulas = False
..PreserveFormatting = True
..RefreshOnFileOpen = False
..BackgroundQuery = True
..RefreshStyle = xlInsertDeleteCells
..SavePassword = False
..SaveData = True
..AdjustColumnWidth = True
..RefreshPeriod = 0
..PreserveColumnInfo = True
..SourceDataFile = "D:\TED Month End Reports.xls"
..Refresh BackgroundQuery:=False
End With

The "With....." line doesn't work. In debug mode "identifier under cursor not recognized" and the whole "With... thing" is yellow.

Thanks for your help,

Pierre

Pierre Hurbault · Nov 12, 2010

Sorry it seems it didn't work properly earlier.

'--------------------THIS IS THE ONE I TRIED TO REPLACE WITH sFileName-------------
With ActiveSheet.QueryTables.Add(Connection:=Array( _
"OLEDB;Provider=Microsoft.Jet.OLEDB.4.0;Password="""";User ID=Admin;Data Source=" _
& sFileName & _
";Mode=Share Deny Write;Extend" _
, _
"ed Properties=""HDR=YES;"";Jet OLEDB:System database="""";Jet OLEDB:Registry Path="""";Jet OLEDB

atabase Password="""";Jet OLEDB:Engine" _
, _
" Type=35;Jet OLEDB

atabase Locking Mode=0;Jet OLEDB:Global Partial Bulk Ops=2;Jet OLEDB:Global Bulk Transactions=1;Jet OLEDB:Ne" _
, _
"w Database Password="""";Jet OLEDB:Create System Database=False;Jet OLEDB:Encrypt Database=False;Jet OLEDB

on't Copy Locale on Co" _
, _
"mpact=False;Jet OLEDB:Compact Without Replica Repair=False;Jet OLEDB:SFP=False" _
), Destination:=Range("D115"))
..CommandType = xlCmdTable
..CommandText = Array("Sheet1$")
..Name = "Total Consumption"
..FieldNames = True
..RowNumbers = False
..FillAdjacentFormulas = False
..PreserveFormatting = True
..RefreshOnFileOpen = False
..BackgroundQuery = True
..RefreshStyle = xlInsertDeleteCells
..SavePassword = False
..SaveData = True
..AdjustColumnWidth = True
..RefreshPeriod = 0
..PreserveColumnInfo = True
..SourceDataFile = "D:\TED Month End Reports.xls"
..Refresh BackgroundQuery:=False
End With

Submitted via EggHeadCafe
OAuth Basics for .NET Developers
http://www.eggheadcafe.com/tutorial...ae6dbcbd/oauth-basics-for-net-developers.aspx

Pierre Hurbault · Nov 12, 2010

Here is the good version, it didn't work properly earlier.

'--------------------THIS IS THE ONE I TRIED TO REPLACE WITH sFileName-------------
With ActiveSheet.QueryTables.Add(Connection:=Array( _
"OLEDB;Provider=Microsoft.Jet.OLEDB.4.0;Password="""";User ID=Admin;Data Source=" _
& sFileName & _
";Mode=Share Deny Write;Extend" _
, _
"ed Properties=""HDR=YES;"";Jet OLEDB:System database="""";Jet OLEDB:Registry Path="""";Jet OLEDB

atabase Password="""";Jet OLEDB:Engine" _
, _
" Type=35;Jet OLEDB

atabase Locking Mode=0;Jet OLEDB:Global Partial Bulk Ops=2;Jet OLEDB:Global Bulk Transactions=1;Jet OLEDB:Ne" _
, _
"w Database Password="""";Jet OLEDB:Create System Database=False;Jet OLEDB:Encrypt Database=False;Jet OLEDB

on't Copy Locale on Co" _
, _
"mpact=False;Jet OLEDB:Compact Without Replica Repair=False;Jet OLEDB:SFP=False" _
), Destination:=Range("D115"))
..CommandType = xlCmdTable
..CommandText = Array("Sheet1$")
..Name = "Total Consumption"
..FieldNames = True
..RowNumbers = False
..FillAdjacentFormulas = False
..PreserveFormatting = True
..RefreshOnFileOpen = False
..BackgroundQuery = True
..RefreshStyle = xlInsertDeleteCells
..SavePassword = False
..SaveData = True
..AdjustColumnWidth = True
..RefreshPeriod = 0
..PreserveColumnInfo = True
..SourceDataFile = "D:\TED Month End Reports.xls"
..Refresh BackgroundQuery:=False
End With

Submitted via EggHeadCafe
Update Configuration of a ClickOnce WPF Application Using Mage or MageUI
http://www.eggheadcafe.com/tutorial...nce-wpf-application-using-mage-or-mageui.aspx

Pierre Hurbault · Nov 12, 2010

Here is the correct version :

'--------------------THIS IS THE ONE I TRIED TO REPLACE WITH sFileName-------------
With ActiveSheet.QueryTables.Add(Connection:=Array( _
"OLEDB;Provider=Microsoft.Jet.OLEDB.4.0;Password="""";User ID=Admin;Data Source=" _
& sFileName & _
";Mode=Share Deny Write;Extend" _
, _
"ed Properties=""HDR=YES;"";Jet OLEDB:System database="""";Jet OLEDB:Registry Path="""";Jet OLEDB

atabase Password="""";Jet OLEDB:Engine" _
, _
" Type=35;Jet OLEDB

atabase Locking Mode=0;Jet OLEDB:Global Partial Bulk Ops=2;Jet OLEDB:Global Bulk Transactions=1;Jet OLEDB:Ne" _
, _
"w Database Password="""";Jet OLEDB:Create System Database=False;Jet OLEDB:Encrypt Database=False;Jet OLEDB

on't Copy Locale on Co" _
, _
"mpact=False;Jet OLEDB:Compact Without Replica Repair=False;Jet OLEDB:SFP=False" _
), Destination:=Range("D115"))
..CommandType = xlCmdTable
..CommandText = Array("Sheet1$")
..Name = "Total Consumption"
..FieldNames = True
..RowNumbers = False
..FillAdjacentFormulas = False
..PreserveFormatting = True
..RefreshOnFileOpen = False
..BackgroundQuery = True
..RefreshStyle = xlInsertDeleteCells
..SavePassword = False
..SaveData = True
..AdjustColumnWidth = True
..RefreshPeriod = 0
..PreserveColumnInfo = True
..SourceDataFile = "D:\TED Month End Reports.xls"
..Refresh BackgroundQuery:=False
End With

Submitted via EggHeadCafe
Silverlight 3D Animated Topic Selector With Titled Menu Items
http://www.eggheadcafe.com/tutorial...ed-topic-selector-with-titled-menu-items.aspx

Pierre Hurbault · Nov 12, 2010

Last try, sorry for spamming

With ActiveSheet.QueryTables.Add(Connection:=Array( _
"OLEDB;Provider=Microsoft.Jet.OLEDB.4.0;Password="""";User ID=Admin;Data Source=" _
& sFileName & _
";Mode=Share Deny Write;Extend" _
, _
"ed Properties=""HDR=YES;"";Jet OLEDB:System database="""";Jet OLEDB:Registry Path="""";Jet OLEDB

atabase Password="""";Jet OLEDB:Engine" _
, _
" Type=35;Jet OLEDB

atabase Locking Mode=0;Jet OLEDB:Global Partial Bulk Ops=2;Jet OLEDB:Global Bulk Transactions=1;Jet OLEDB:Ne" _
, _
"w Database Password="""";Jet OLEDB:Create System Database=False;Jet OLEDB:Encrypt Database=False;Jet OLEDB

on't Copy Locale on Co" _
, _
"mpact=False;Jet OLEDB:Compact Without Replica Repair=False;Jet OLEDB:SFP=False" _
), Destination:=Range("D115"))

Submitted via EggHeadCafe
Excel Tips: Net Present Value (NPV) and Internal Rate of Return (IRR)
http://www.eggheadcafe.com/tutorial...alue-npv-and-internal-rate-of-return-irr.aspx

Dave Peterson · Nov 12, 2010

I don't know enough about queries to help.

Maybe someone will chime in.

macro-import another xls file

puiuluipui

Chip Pearson

puiuluipui

Dave Peterson

puiuluipui

Chip Pearson

Gord Dibben

puiuluipui

Dave Peterson

puiuluipui

Pierre Hurbault

Pierre Hurbault

Pierre Hurbault

Pierre Hurbault

Pierre Hurbault

Dave Peterson