Using Querytable.add to import data from a closed workbook

[Markus Stolle]

PROBLEM
I need to somehow calculate the name of the first sheet of a workboo
from which I get external data through the QueryTables.add method

BACKGROUND INFORMATION
I am receiving several reports that I need to consolidate.
They are from different sources, so I can't dictate how the sheets hav
to be formatted, named etc.

Therefore I am building a tool that allows me to "open" the sheets a
external data, and storing the information in a temoray sheet.

That temp sheet I then modify heavily to get the data in the shape
need.

CODE EXAMPLE
to get to my code I started by recording a macro, and luckily for me
the workbooks of type "raw" have their one and only sheet called jus
that: "RAW"

So all worked perfectly for my first 11 sample reports.

This is the Code that I used for the first set of Reports of Typ
"Raw".


Code
-------------------

'Variable list:
'FullNameOfReport: Filename complete with Path
'NameOfReport() is a function that returns only the filename

With Worksheets("Temp Import").QueryTables _
.Add(Connection:= _
Array("OLEDB;Provider=Microsoft.Jet.OLEDB.4.0;" _
, "Password="""";" _
, "User ID=Admin;" _
, "Data Source=" & FullNameOfReport & ";" _
, "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:Encrypt Database=False;" _
, "Jet OLEDBon't Copy Locale on Compact=False;" _
, "Jet OLEDB:Compact Without Replica Repair=False;" _
, "Jet OLEDB:SFP=False") _
, Destination:=Sheets("Temp Import").Range("A1"))
.CommandType = xlCmdTable
.CommandText = Array("RAW$")
.Name = NameOfReport(FullNameOfReport)
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = True
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.PreserveColumnInfo = True
.SourceDataFile = FullNameOfReport
.Refresh BackgroundQuery:=False
End With

Worksheets("Temp Import").QueryTables(1).Delete

-------------------


My *BIG* Problem is that other reports unfortunately do not have th
consistant naming of their sheets. I now wanted to import Reports o
type "DIR" and their sheets are named anything but "DIR".

So my code fails at the *-.commandtext -* parameter wher
*-Array("RAW$")*- is still hard coded from when I created that cod
first while recording a Macro.

HELP NEEDE
How can I replace Array("RAW$") with a calculated expression tha
refers to the name of the first sheet (or better sheet x) in the sourc
workbook.

YOU KNOW A BETTER METHOD FOR WHAT I AM DOING
If you know a better method to import a worksheet in my temp sheet
without opening and showing the source workbook, let me know.

Many thanks in advanc

 

[Dick Kusleika]

Markus

YOU KNOW A BETTER METHOD FOR WHAT I AM DOING?
If you know a better method to import a worksheet in my temp sheet,
without opening and showing the source workbook, let me know.

Specifically, why don't you want to open the workbooks? You can open them,
process them, and close them without showing anything on the screen.

 

[Markus Stolle]

Thank you Dick,

but I guess I dont know how.

When I use Workbooks.open- filename-

It always shows them.


How can I open them in the background

 

[Dick Kusleika]

Markus

Put

Application.ScreenUpdating = False

as one of the first lines of your procedure and

Application.ScreenUpdating = True

as one of the last lines. Everything you do in between those statements
will not show up on the screen. Then you might consider adding a line like

Application.StatusBar = "Processing Workbooks"

to let the users know that something is happening and their computer didn't
freeze (if it's a long program). Set Application.StatustBar to False when
you're done and that will give control back to Excel.

 

[Jamie Collins]

...

Specifically, why don't you want to open the workbooks? You can open them,
process them, and close them without showing anything on the screen.

FWIW one can get the worksheet names from a closed workbook using ADO
but AFAIK there is no reliable way of determining which is the first
worksheet without opening the workbook and testing Worksheets(1).Name
:-(

Jamie.

--

 

[Dick Kusleika]

FWIW one can get the worksheet names from a closed workbook using ADO

I didn't know that, so I googled it, and your name came up. Looks like a
good blog post, I better learn how to do it first.

 

[Dick Kusleika]

When I google something and my name turns up, I begin to think I'm not
going to find the answer I want <g>.

I can do worse than that. I googled something because I didn't know how to
do it. Then I found a post of my own from two years earlier with the
correct answer. I like to think that means that I've forgotten more about
Excel than some people will ever know, but in reality it's just senility.
Oh well, I can dream.

Dick