macro pause for manual entry

bobmc

Board Regular
Joined
Mar 13, 2002
Messages
142
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
 

Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,318
Office Version
  1. 365
Platform
  1. Windows
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

Well-known Member
Joined
Jan 1, 2003
Messages
2,062
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

Board Regular
Joined
Mar 13, 2002
Messages
142
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
ABCDEFGHI
1ZIPCODE/ZONEPOSTAGECALCULATOR
2
3DBPGSales2005.mdb
4TablePeriodending011605
5
6PaidCustomerNameZIPItemQtyDelWgt/OzZonePostage
744.85JackCalnan90068VHS342454.90
844.85EricSCorp01002DVD341683.85
944.85RichardWurzbacher20721DVD341683.85
1029.90JamesG.Robinson75048DVD22873.85
Sheet1
 

bobmc

Board Regular
Joined
Mar 13, 2002
Messages
142
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

Well-known Member
Joined
Jan 1, 2003
Messages
2,062
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.
 

Forum statistics

Threads
1,148,220
Messages
5,745,456
Members
423,952
Latest member
EduardoM

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
Top