macro pause for manual entry

[bobmc]

I would like to pause a macro so I can choose from several tables in a query, then let the macro continue. I didn't see any pause button or "insert pause" command available.

Any ideas?

Thanks.

bobmc

 

[Norie]

bobmc

Could you explain further what you are trying to do?

If you already have code it might be an idea to post it.

 

[nbrcrunch]

Perhaps... instead of trying to cause a pause, use a dialog box (design form) in the middle of the logic that would take your input and pass it back to the program which would continue after pressing OK.

 

[bobmc]

OK, here's what I need...

Columns A thru D are used to import data from a database. (PGSales2005.mdb) Although there are many columns available in the database, I only need four - ("Paid", "Customer Name", "ZIP" & "Item") so I use the "Import External Data" menu and go through the necessary dialogue to specify the columns I need.

The database is indicated in cell B3 (I typed it in, although it would be nice to have the field activate a navigation so I can browse to the DB file.)

The table name is indicated in cell B4. (Again, I typed it in. It would be very helpful if this cell was actually a drop down list that showed tables available in the DB called out in cell B3.)

I'd like to build a macro that automates the dialogue necessary to bring in the proper DB/Table/Columns. Any macro I build has absolute reference to a given DB and table. I'd like the macro to reference the DB and table called out in cells B3 & B4 respectively, so it's a relative reference instead of absolute.

I hope this makes sense. If anyone has the gumption to give me a hand with this, I'd appreciate it immensely!

bobmc

Example:

ZIP ZONES.xlt
	A	B	C	D	E	F	G	H	I
1	ZIPCODE/ZONEPOSTAGECALCULATOR
2
3	DB	PGSales2005.mdb
4	Table	Periodending011605
5
6	Paid	CustomerName	ZIP	Item	Qty	Del	Wgt/Oz	Zone	Postage
7	44.85	JackCalnan	90068	VHS	3	4	24	5	4.90
8	44.85	EricSCorp	01002	DVD	3	4	16	8	3.85
9	44.85	RichardWurzbacher	20721	DVD	3	4	16	8	3.85
10	29.90	JamesG.Robinson	75048	DVD	2	2	8	7	3.85
Sheet1

 

[bobmc]

If it helps any, here's the VB code for the macro I built. Everywhere text indicates "period ending 011605", I'd like the reference to be cell B4 on the worksheet. (Sheet1)

Any ideas?

Thanks!

bobmc

Here's the VB code:

Sub zipdb()
'
' zipdb Macro
'
' Keyboard Shortcut: Ctrl+d
'
With ActiveSheet.QueryTables.Add(Connection:=Array(Array( _
"ODBC;DSN=MS Access Database;DBQ=C:\Documents and Settings\Bob McKeehen\My Documents\PastorGas\Sales2005\PGSales2005.mdb;DefaultDir=C" _
), Array( _
":\Documents and Settings\Bob McKeehen\My Documents\PastorGas\Sales2005;DriverId=25;FIL=MS Access;MaxBufferSize=2048;PageTimeout" _
), Array("=5;")), Destination:=Range("A7"))
.CommandText = Array( _
"SELECT `Period ending 011605`.`Total Amount`, `Period ending 011605`.`Customer Name`, `Period ending 011605`.ZIP, `Period ending 011605`.`Invoice Description`" & Chr(13) & "" & Chr(10) & "FROM `Period ending 011605` `Period endi" _
, "ng 011605`" & Chr(13) & "" & Chr(10) & "WHERE (`Period ending 011605`.`Total Amount`>20)")
.Name = "Query from MS Access Database_1"
.FieldNames = False
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = False
.RefreshStyle = xlOverwriteCells
.SavePassword = True
.SaveData = True
.AdjustColumnWidth = False
.RefreshPeriod = 0
.PreserveColumnInfo = True
.Refresh BackgroundQuery:=False
End With
End Sub

 

[nbrcrunch]

In reply to your 3rd sentence, you are aware that you can right-click the field, choose hyperlink and associate it to an external file, right?

As far as the VBA, though I am getting better with it (slow but sure), I am not the best respondent for that issue.