Read zip file on disc

B

BillyRogers

Here is some code that I have in excel. We use it to do a QA on about 70
cd's we receive each month. All the cd's have an Access database on them
with the same 12 table names that we do a record count on. Each database has
a different name.

The process works great, except for one cd we receive which is an access
databse in a zip file on the cd. I know the name of the database that is
zipped. It's the same every month. Is there some way to alter this code so
that it can also read the one zipped cd and run SQL code on it also?




Sub CommandButton1_Click()
Dim rs As Recordset
Dim SQlcmd As String
Dim myTables As Variant
Dim table As Variant

myTables = Array("[Billing Fees]", _
"[Card Entitlements]", _
"[Card Specific Amex]", _
"[FEE History]", _
"[financial history]", _
"[financial history 2]", _
"[Link New Xref]", _
"[Merchant ABA/DDA New]", _
"[Merchant Funding Category DDAs]", _
"[Merchant Control Data]", _
"[tblInternationalGeneral]", _
"[tbl_PhaseII_Additional_info]")

Dim DBName As String

DBName = ListBankNames.Value

For Each table In myTables

SQlcmd = "Select Count(*) as [Count] From " & table

Set rs = New ADODB.Recordset

rs.Open Source:=SQlcmd, _
ActiveConnection:="Provider=Microsoft.Jet.OLEDB.4.0; Data Source=D:" + _
DBName + ".mdb; User Id=admin; Password="

Range("A65000").End(xlUp).Offset(1, 0).Activate



ActiveCell.FormulaR1C1 = DBName & ";" & table & ";" & _
rs.Fields("Count").Value

Next table


End Sub
--
Billy Rogers

Dallas,TX

Currently Using Office 2000
 
B

BillyRogers

We are using WinRar to zip the files and our operating system is windows
2000. Should this still work?
--
Billy Rogers

Dallas,TX

Currently Using Office 2000


Tim Williams said:
You'll have to unzip the DB first. As far as I know there's no way to query a zipped Access file.

See here for some code for unzipping from VBA:
http://www.rondebruin.nl/windowsxpzip.htm

Tim


BillyRogers said:
Here is some code that I have in excel. We use it to do a QA on about 70
cd's we receive each month. All the cd's have an Access database on them
with the same 12 table names that we do a record count on. Each database has
a different name.

The process works great, except for one cd we receive which is an access
databse in a zip file on the cd. I know the name of the database that is
zipped. It's the same every month. Is there some way to alter this code so
that it can also read the one zipped cd and run SQL code on it also?




Sub CommandButton1_Click()
Dim rs As Recordset
Dim SQlcmd As String
Dim myTables As Variant
Dim table As Variant

myTables = Array("[Billing Fees]", _
"[Card Entitlements]", _
"[Card Specific Amex]", _
"[FEE History]", _
"[financial history]", _
"[financial history 2]", _
"[Link New Xref]", _
"[Merchant ABA/DDA New]", _
"[Merchant Funding Category DDAs]", _
"[Merchant Control Data]", _
"[tblInternationalGeneral]", _
"[tbl_PhaseII_Additional_info]")

Dim DBName As String

DBName = ListBankNames.Value

For Each table In myTables

SQlcmd = "Select Count(*) as [Count] From " & table

Set rs = New ADODB.Recordset

rs.Open Source:=SQlcmd, _
ActiveConnection:="Provider=Microsoft.Jet.OLEDB.4.0; Data Source=D:" + _
DBName + ".mdb; User Id=admin; Password="

Range("A65000").End(xlUp).Offset(1, 0).Activate



ActiveCell.FormulaR1C1 = DBName & ";" & table & ";" & _
rs.Fields("Count").Value

Next table


End Sub
--
Billy Rogers

Dallas,TX

Currently Using Office 2000
 
T

Tim Williams

I think the shell approach may be XP only, but there's other code on Ron's site which deals with non-XP..

--
Tim Williams
Palo Alto, CA


BillyRogers said:
We are using WinRar to zip the files and our operating system is windows
2000. Should this still work?
--
Billy Rogers

Dallas,TX

Currently Using Office 2000


Tim Williams said:
You'll have to unzip the DB first. As far as I know there's no way to query a zipped Access file.

See here for some code for unzipping from VBA:
http://www.rondebruin.nl/windowsxpzip.htm

Tim


Here is some code that I have in excel. We use it to do a QA on about 70
cd's we receive each month. All the cd's have an Access database on them
with the same 12 table names that we do a record count on. Each database has
a different name.

The process works great, except for one cd we receive which is an access
databse in a zip file on the cd. I know the name of the database that is
zipped. It's the same every month. Is there some way to alter this code so
that it can also read the one zipped cd and run SQL code on it also?




Sub CommandButton1_Click()
Dim rs As Recordset
Dim SQlcmd As String
Dim myTables As Variant
Dim table As Variant

myTables = Array("[Billing Fees]", _
"[Card Entitlements]", _
"[Card Specific Amex]", _
"[FEE History]", _
"[financial history]", _
"[financial history 2]", _
"[Link New Xref]", _
"[Merchant ABA/DDA New]", _
"[Merchant Funding Category DDAs]", _
"[Merchant Control Data]", _
"[tblInternationalGeneral]", _
"[tbl_PhaseII_Additional_info]")

Dim DBName As String

DBName = ListBankNames.Value

For Each table In myTables

SQlcmd = "Select Count(*) as [Count] From " & table

Set rs = New ADODB.Recordset

rs.Open Source:=SQlcmd, _
ActiveConnection:="Provider=Microsoft.Jet.OLEDB.4.0; Data Source=D:" + _
DBName + ".mdb; User Id=admin; Password="

Range("A65000").End(xlUp).Offset(1, 0).Activate



ActiveCell.FormulaR1C1 = DBName & ";" & table & ";" & _
rs.Fields("Count").Value

Next table


End Sub
--
Billy Rogers

Dallas,TX

Currently Using Office 2000
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top