Import from EXCEL to EXCEL

thunderfoot

thunderfoot

Board Regular
Joined
May 28, 2004
Messages
229
I want to import data from an EXCEL file(1) without opening it, into another EXCEL file(2) which will be open. I want this linked to a Macro Button in file(2). I have a macro that does the job (see below) but it is a 'recorded' macro.

Sub Macro1()
'
'
With ActiveSheet.QueryTables.Add(Connection:=Array( _
"OLEDB;Provider=Microsoft.Jet.OLEDB.4.0;Password="""";User ID=Admin;Data Source=C:\Documents and Settings\a.n.other\Desktop\Eastlin" _
, _
"e Log.xls;Mode=Share Deny Write;Extended Properties=""HDR=YES;"";Jet OLEDB:System database="""";Jet OLEDB:Registry Pat" _
, _
"h="""";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:Encr" _
, _
"ypt Database=False;Jet OLEDB:Don't Copy Locale on Compact=False;Jet OLEDB:Compact Without Replica Repair=False;Jet OLEDB:SFP=Fal" _
, "se"), Destination:=Range("A1"))
.CommandType = xlCmdTable
.CommandText = Array("'WE 01-05-09$'")
.Name = "Eastline Log_1"
.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:\Documents and Settings\a.n.other\Desktop\Eastline Log.xls"
.Refresh BackgroundQuery:=False
End With
End Sub

This all looks a bit messy, particularly the first bit upto .CommandType = xlCmdTable.

Is there a 'cleaner' code that will do the same job?
 

Excel Facts

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use $ signs: $V$2:$Z$99 will always point to V2:Z99, even after copying
Sort by date Sort by votes
HI
try these codes

Code: 
Sub thunderfoot()
Dim G As Long, h As Long
Cells(1, 1) = "=cell(""filename"")"
Cells(1, 2) = "=left(A1,find(""["",A1)-1)"
    Cells(1, 4) = "=Counta('" & Cells(1, 2) & "[BookA.xls]Sheet1'!B:B)"
        For G = 2 To Cells(1, 4)
        For h = 1 To 10
        Cells(1, 3) = "='" & Cells(1, 2) & "[BookA.xls]Sheet1'!" & Chr(h + 64) & G
            Cells(G + 1, h) = Cells(1, 3)
              Next h
        Next G
MsgBox "collating is complete."
End Sub
It pulls all rows of data from A to J of bookA from sheet1. Change parameters to suit your case. it pulls from closed workbook
Ravi
 
Upvote 0
I don't use querytables all that much but in general I'd leave the connection string alone unless you know what you are doing. It may be that some of the items are "defaults" and can be left out of the long string but I really can't say for sure.

A tip from Fazza here in Post #2:
http://www.mrexcel.com/forum/showthread.php?t=442665&highlight=array

To make it look cleaner you can put some of that long connection string stuff in a separate function:
Code: 
Sub Macro1()

    With ActiveSheet.QueryTables.Add(Connection:=Get_Conn_String(), Destination:=Range("A1"))
        .CommandType = xlCmdTable
        .CommandText = Array("'WE 01-05-09$'")
        .Name = "Eastline Log_1"
        .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:\Documents and Settings\a.n.other\Desktop\Eastline Log.xls"
        .Refresh BackgroundQuery:=False
    End With

End Sub
'-----------------------------------
Function Get_Conn_String() As Variant
    Get_Conn_String = Array( _
    "OLEDB;Provider=Microsoft.Jet.OLEDB.4.0;Password="""";User ID=Admin;Data Source=C:\Documents and Settings\a.n.other\Desktop\Eastlin" _
    , _
    "e Log.xls;Mode=Share Deny Write;Extended Properties=""HDR=YES;"";Jet OLEDB:System database="""";Jet OLEDB:Registry Pat" _
    , _
    "h="""";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:Encr" _
    , _
    "ypt Database=False;Jet OLEDB:Don't Copy Locale on Compact=False;Jet OLEDB:Compact Without Replica Repair=False;Jet OLEDB:SFP=Fal" _
    , "se")
End Function

And, of course, I see a post already that demonstrates an alternative method - you're spoiled for choice I guess.

Good luck,
ξ

Edit:
Note that if you want the code to look like a human wrote it (machines don't care ;) ) then:
Code: 
Function Get_Conn_String() As String
Dim s As String

    s = ""
    s = s & "OLEDB;Provider=Microsoft.Jet.OLEDB.4.0;Password="""";User ID=Admin;"
    s = s & "Data Source=C:\Documents and Settings\a.n.other\Desktop\Eastline Log.xls;"
    s = s & "Mode=Share Deny Write;Extended Properties=""HDR=YES;"";"
    s = s & "Jet OLEDB:System database="""";Jet OLEDB:Registry Path="""";Jet OLEDB:Database Password="""";"
    s = s & "Jet OLEDB:Engine Type=35;Jet OLEDB:Database Locking Mode=0;Jet OLEDB:Global Partial Bulk Ops=2;"
    s = s & "Jet OLEDB:Global Bulk Transactions=1;Jet OLEDB:New Database Password="""";"
    s = s & "Jet OLEDB:Create System Database=False;Jet OLEDB:Encrypt Database=False;"
    s = s & "Jet OLEDB:Don't Copy Locale on Compact=False;Jet OLEDB:Compact Without Replica Repair=False;Jet OLEDB:SFP=False"
    
    Get_Conn_String = s

End Function
 
Last edited:
Upvote 0
You must log in or register to reply here.

Similar threads

C
  • Question
ole db CopyFromRecordset skipping cell values
Replies
0
Views
339
Clif McIrvin
C
A
  • Question
VBA - Copying Worksheets that contain Pivot tables
Replies
1
Views
628
Alex Blakenburg
A
JazzSP8
  • Solved
Access to Excel Data Import Cutting off URL
Replies
6
Views
885
JazzSP8
JazzSP8
L
  • Question
Importing from pdf to excel workbook using VBA and PowerQuery
Replies
1
Views
2K
John_w
J
gmooney
  • Question
Pivot Table Data - File with data saved in Pivot Table but I don't have source data
Replies
0
Views
664
gmooney
gmooney

Forum statistics

Threads
1,215,757
Messages
6,126,693
Members
449,331
Latest member
smckenzie2016

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top