A1VBCode Forums / Classic Visual Basic (VB 6 or earlier) / VBA(Excel, Word, etc...)

Attachmate Reflection - Determining cursor location

bhofmann — Mon, 09 Jan 2012 10:01:23 GMT

I am trying to do a screen scrape within Attachmate Reflection with VBA. I would like to screen scrape everything from the current cursor location back to the beginning of the line. I know how to do screen scrapes for defined row/column locations, but I do not know how to determine the current cursor location to be able to use for the row/column identifiers for the screen scrape. Any assistance is appreciated.

I am trying to capture the last user prompt displayed prior to running macro code, so that the macro can look for the same prompt to appear again in order to trigger additional code to run.

-Ben Hofmann

Removing characters in a text

chaw12 — Wed, 02 Nov 2011 21:15:15 GMT

Hi..

can someone please help me in doing a macro code.. i cant explain it well but ill make an example

In excel file

Column A Column B(For Output)

Dr. Abc Def Abc Def
Dr Ghi Jkl Ghi Jkl

i want to remove all the "Dr" or any special characters(' , "" . ! #*) in a name so that it will result like in a column be.. how can i please do that in excel.. a little help please

Go to Next Slide in Power Point

samuvk — Tue, 21 Sep 2010 18:13:55 GMT

I am trying to go to the next slide in Power Point.

I tried:

ActivePresentation.SlideShowWindow.View.Next

ActivePresentation.SlideShowWindow.View.GotoSlide #

but I getting this error:

run-time error ' -2147188160 (80048240)': Presentation (unknown member): invalid request. there is currently no slide show view for this presentation

Thank you very much for your help

help with excel and access importing

chaw12 — Sun, 23 Oct 2011 03:44:36 GMT

Hi to all!

I've created a simple program which will import all the data in an excel file to a database or more likely inserting the excel data to access database. but can someone please help with this!. I want my program to automatically write all the duplicate data in a different sheet of the excel file if it has found a similar or existing data on the access database. And if there are some duplicate data found I want the excel to access importing to skip on it and continue saving all those other data that has no duplicate on the database. By the way I've create an excel template with an exporting button and it is where the code resides and the data will be encoded also on the template. Hope some would help me with this..

Thanks in advance!

How do I make an excel spreadsheet calculate in stones and pounds?

lee91 — Tue, 22 Jun 2010 08:10:41 GMT

Hi, How can I format the cells in an excel spreadsheet so that 1 would have a value of 0.14 rather than 0.10; so for instance 3 minus 0.3 would have an answer of 2.11?

getObject() and createObject()

cloudet — Tue, 26 Apr 2005 00:30:04 GMT

Dear all:

what is the difference between this two functions and how should i use them properly?

thanks.

Transferring information from VB.Net to Excel

montekass — Sun, 10 Jul 2005 11:02:53 GMT

What I have done so far is a basic form setup with 8 drop down boxes, after I have made my selections, I would like to keep a running tally of the information, lets say I want to make 10 different entries giving me 80 different selections, 10 rows of information consisting of columns of items. Then I want to be able to transfer it to an Excel spreadsheet for sorting purposes.

POSSIBLE?????

macro failed please help

cs_macker — Sat, 11 Jun 2011 11:53:41 GMT

Hi all

ive been given an excel document to try and fix. it is for an amateur poker league, and the original developer for the programme does not play anymore.

the problem is with a macro button, which from what i can tell, is supposed to retrieve the date from the local pc, establish whether it is the tuesday or sunday event, retrieve the people playing that night (who have been manually entered into the relative nights page) and then place them randomly on a table.

however this has stopped running.

i have a very limited knowledge of java programming (couple classes at uni before i dropped out) so i could do with a bit of help!

would anyone be able to help me, i can provide the coding from the debug and/or the actual file if necessary.

Much appreciated

Paul

Writing to remote computer

Coyote Paul — Wed, 18 May 2011 13:19:50 GMT

I use the following code to write to another computer on my home network. Can someone please tell me how to similarly write to a remote computer on the internet? The variable written (TRAP) is a small string.

BFILE = "\\MANNANET\Documents\mojo.txt"
IFILENUM = FreeFile
Open BFILE For Output As IFILENUM
Print #IFILENUM, TRAP
Close #IFILENUM

How do I make a number in an Excel formula sequentually count?

Newellmyra — Thu, 29 Apr 2010 02:57:10 GMT

I have a column in Excel that contains a formula. It is the same on every row, except for one number. I want to make this value to automatically count up by one every row. I want the rest of the formula to remain identical. Can I get Excel to to this automatically or do I need to manually change the formula every single row? Thanks!

What would the necessary code be for using a command button to insert text into a table in powerpoint? (AM A COMPLETE NOVICE)

slevinmj — Mon, 07 Mar 2011 17:12:02 GMT

I am a COMPLETE NOVICE!

I am trying to use VBA for the very first time with no programming or code writing experience.

I want to have a command button in a powerpoint presentation that when clicked, will insert text into a table on a separate slide, is this even possible? and would anybody be able to help me with the code that would be necessary if it is possible?

Would massively appreciate any help or guidance?

UserForm and DropDown in word

alex1989 — Wed, 09 Feb 2011 08:44:19 GMT

Hi, I have a word document where I created a userform for the users that have to enter informations. I would like for it to place the infos in the word document. In fact, the users click on the macro which load the userform. In the userform, there are textboxes where they enter informations and when they click "OK" the informations are directly insert in some textboxes on the word document. Can someone help please?

Also, I would like to have some dropdown for the users to choose options. The objectif is for the users to save time by not having to type there text. So I'm gonna give them dropdown where they choose the standard sentences and when they click on their choice, they can change it by typing themself, like if they want to add some informtions to the sentence.

Thanks

Outlook template with VBA /

isoA — Mon, 03 Jan 2011 08:28:25 GMT

Hi,

Is it possible to make outlook template that gets hostname and username automatically from computer?

I am trying to make a template where customer can write their name, phone number..., but template would automatically get username, computername and maybe some other environment variables. I can do this in excel, but is it possible to make same to outlook template?

- isoA

VBA Excel Convert the excel file into readonly by codes...

Krishal Rajput — Thu, 29 Jan 2009 14:28:09 GMT

Hello...... I am working on SQL Server 2000. I have a database that has almost 150 tables. I need to develop a report in excel. For this purpose i need to fetch data from my database and display the same in excel.

After doing this i need to ensure that no one can edit the data that i have fetched. So somehow i need to lock the grid of the excel sheet in which i have displayed the report result. Cant do this........What to do?

Microsoft Office Excel is waiting for another application to complete an OLE action.

macroman — Wed, 13 Oct 2010 09:59:07 GMT

Hi,

I write a number of VBA programs that have to wait for SAP to download to an Excel sheet, then the program can proceed. If the download takes more than 30 seconds or so I start getting the message in the subject in a msgbox with an OK button. Can this message be suppressed? The new Office Help is not very helpful. I have Office2007 now and learning to deal with it (not a happy camper).

Int resource for Long variables

Ivo — Wed, 06 Oct 2010 17:39:00 GMT

Hello all,

How can I get the result of Excel Int (=values without decimals) in VBA, for Long variables (over 32.768)? CLng rounds up or down, whereas I just need the integer values.

Thank you!
Ivo

Flip a page in word 2003 using VBA

dtondo — Tue, 10 Aug 2010 15:12:05 GMT

Hi, how can i flip a page in word 2003 using VBA???

thanks

I am new to VBA. I am not able to loop the data to process

GuestUser — Tue, 19 Oct 2004 21:57:40 GMT

Hi,
I am trying to call a transaction (ME32K) in SAP system through a Macro in Excel spread sheet. I provided data in Rows and Columns. Once I run Macro, it is processing only one row instead of all rows until the next row is empty. I attached spread sheet.
See the logic below.
--------------------------
Dim j As Integer

Public Sub add_bdcdata(BdcTable As Object, program As String, dynpro As String, dynbegin As String, fnam As String, fval As String)

Dim vField As Variant

j = j + 1

BdcTable.Rows.Add

BdcTable.Value(j, "PROGRAM") = program ' Program Name
BdcTable.Value(j, "DYNPRO") = dynpro ' Dynpro Number
BdcTable.Value(j, "DYNBEGIN") = dynbegin ' X if a screen
BdcTable.Value(j, "FNAM") = fnam ' Field Name
BdcTable.Value(j, "FVAL") = fval ' Field Value

Debug.Print BdcTable.Value(j, "FVAL")
End Sub

Public Sub rfc_call_transaction()
Dim Functions As Object
Dim RfcCallTransaction As Object
Dim Messages As Object
Dim BdcTable As Object

' Create the Function control (that is, the high-level Functions collection):
Set Functions = CreateObject("SAP.Functions")

' Set the rest of Connection object values:
Functions.Connection.System = "QA[Quality Assurance]"
Functions.Connection.client = "100"
Functions.Connection.user = "User1"
Functions.Connection.password = ""
Functions.Connection.language = "EN"

If Functions.Connection.Logon(0, False) <> True Then
Exit Sub
End If

Dim iBOB As Integer

Do

' Retrieve the Function object (the Connection object must be set up before Function objects can be created):
Set RfcCallTransaction = Functions.Add("RFC_CALL_TRANSACTION")

' Set the export parameters
RfcCallTransaction.exports("TRANCODE") = "ME32K"
RfcCallTransaction.exports("UPDMODE") = "S"
Set BdcTable = RfcCallTransaction.Tables("BDCTABLE")

' Set the tables parameter and add the data for the call transaction
add_bdcdata BdcTable, "SAPMM06E", "205", "X", "", ""
add_bdcdata BdcTable, "", "", "", "BDC_CURSOR", "RM06E-EVRTN"
add_bdcdata BdcTable, "", "", "", "BDC_OKCODE", "=KOPF"
add_bdcdata BdcTable, "", "", "", "RM06E-EVRTN", ActiveCell.Offset(iBOB, 0).Value
add_bdcdata BdcTable, "SAPMM06E", "201", "X", "", ""
add_bdcdata BdcTable, "", "", "", "BDC_CURSOR", "EKKO-KTWRT"
add_bdcdata BdcTable, "", "", "", "BDC_OKCODE", "=BU"
add_bdcdata BdcTable, "", "", "", "EKKO-EKGRP", ActiveCell.Offset(iBOB, 1).Value
add_bdcdata BdcTable, "", "", "", "EKKO-PINCR", "10"
add_bdcdata BdcTable, "", "", "", "EKKO-UPINC", "1"
add_bdcdata BdcTable, "", "", "", "EKKO-KDATB", ActiveCell.Offset(iBOB, 2).Value
add_bdcdata BdcTable, "", "", "", "EKKO-KDATE", ActiveCell.Offset(iBOB, 3).Value
add_bdcdata BdcTable, "", "", "", "EKKO-ZTERM", ActiveCell.Offset(iBOB, 4).Value
add_bdcdata BdcTable, "", "", "", "EKKO-KTWRT", ActiveCell.Offset(iBOB, 5).Value
add_bdcdata BdcTable, "", "", "", "EKKO-ZBD1T", "30"
add_bdcdata BdcTable, "", "", "", "EKKO-WKURS", "1.00000"
add_bdcdata BdcTable, "", "", "", "EKKO-INCO1", "DES"
add_bdcdata BdcTable, "", "", "", "EKKO-INCO2", "SAN DIEG0 CA"
add_bdcdata BdcTable, "", "", "", "EKKO-TELF1", ActiveCell.Offset(iBOB, 6).Value
add_bdcdata BdcTable, "", "", "", "EKKO-LIFRE", ActiveCell.Offset(iBOB, 7).Value

'End SubCall the function (if the result is false, then display a message):
If RfcCallTransaction.Call = True Then
Set Messages = RfcCallTransaction.imports("MESSG")
MsgBox Messages.Value("MSGTX")
Else
MsgBox " Call Failed! error: " + GetCustomers.Exception
End If
iBOB = iBOB + 1
Loop Until IsEmpty(ActiveCell.Offset(iBOB, 0))

Functions.Connection.Logoff
End Sub
-----------------------------------------
Thank you
GuestUser

How can I make excel recognize the number is a measurement?

lee91 — Tue, 22 Jun 2010 08:09:29 GMT

I need help with making excel read a formula. For example I have 4' 5" typed in one cell. i want excel to recognize that as "ft" "in". And this is important because i have an sumif scenerio that is suppose to subtract according to a scenerio. Is there a way for excel to recognize the entry as measurement? If so, how can i make it recognize it?

updating excel object in word

slamdunk — Sun, 23 May 2010 22:21:05 GMT

Hi,

I've a word document with excel object. The cell b1 is set to import data from a text file. How can I get this cell to import data automatically when the word document opens without any user interaction?

Thanks!

Updating excel object in word

slamdunk — Sun, 23 May 2010 22:20:25 GMT

Hi,

Thanks!

Excel Formula?

Steveclark — Mon, 26 Apr 2010 05:23:07 GMT

I want to create a formula or a conditional format in Excel that will allow me to examine a date and see if it is 1 year past the current date. And turn the cell Red. Any help is appreciated.

______________________
Buttons and Triangles in cells

How to export outlook calendar to excel with exclusion?

Steveclark — Mon, 26 Apr 2010 05:20:19 GMT

hi...I am trying to export an outlook calendar to excel. I know the normal process for doing this, but I am specifically trying to exclude a certain word from the export. For example, in the subject line of some of the appointments in the calendar it says "done" and I would like to create an excel sheet of all the appointments that are still not "done". I am using Excel 2007. Thanks in advance for your help!

______________________
Buttons and Triangles in cells

ActiveX ComboBox Line Code issue in Word

rusty_cd — Wed, 21 Apr 2010 19:38:59 GMT

I created an ActiveX ComboBox list in my 2003 Word document using Visual Basic Line Code. Unfortunately, everytime I reopen the document, I have to reactivate the code in Visual Basic before I can access it in the drop down list in the word doc. Please look at the code below and let me know if I am missing something that will allow this to continuely activate without my running it every time.

Private Sub ComboBox1_Change()
Me.ComboBox1.List = Split(" option1 option2 option3")
End Sub

Please help! Thanks!

how to embed ms office into vb 6

belat — Tue, 16 Feb 2010 06:35:30 GMT

hi sir...i just want to ask if how i could

embed microsoft office application in vb 6

that the content will be saved in the database and can be viewed

from the database....

mysql database..

thanks..

Problems running VBA code within Excel

sclward — Mon, 15 Feb 2010 11:17:24 GMT

Have created the following code to export Excel worksheet data to Access database. Had early issues with registering type to allow system to recognize the Dim statement. Setup object library to include Microsoft DAO 3.6 Object Library, but now when trying to compile macro I get a runtime error '429' that an ActiveX component can't create object on the Set db statement of the code. What am I missing?

Sub DAOFromExcelToAccess()
'exports data from the active worksheet to a table in an Access database
'this procedure must be edited before use
Dim db As Database, rs As Recordset, r As Long
Set db = OpenDatabase("C:\Documents and Settings\sward\My Documents\Austell Equipment\GM-130\Productivity Reports\Machine_Logging.mdb")
'open the database
Set rs = db.OpenRecordset("GM130", dbOpenTable)
'get all records in a table
r = 1 'the start row in the worksheet
Do While Len(Range("A" & r).Formula) > 0
'repeat until first empty cell in column A
With rs
.AddNew 'create a new record
'add values to each field in the record
.Fields("Customer") = Range("A" & r).Value
.Fields("Metallizer") = Range("B" & r).Value
.Fields("Met_Date") = Range("C" & r).Value
.Fields("Vac_Roll_Number") = Range("D" & r).Value
.Fields("Met_Quality") = Range("E" & r).Value
.Fields("Met_Performance") = Range("F" & r).Value
.Fields("Met_Downtime") = Range("G" & r).Value
.Fields("Slitter") = Range("H" & r).Value
.Fields("Slit_Date") = Range("I" & r).Value
.Fields("Slit_Quality") = Range("J" & r).Value
.Fields("Slit_Performance") = Range("K" & r).Value
.Fields("Slit_Downtime") = Range("L" & r).Value
'add more fields if necessary...
.Update 'store the new record
End With
r = r + 1 'next row
Loop
rs.Close
Set rs = Nothing
db.Close
Set db = Nothing

End Sub

Export Excel worksheet to Access using VBA

sclward — Sun, 31 Jan 2010 12:08:31 GMT

I'm wanting to export an Excel worksheet to an existing Access table. I want to do this from within Excel using VBA and a user macro. I want to be able to append the existing records within the Access table. What's the best way to do this? Thanks.

Use sub

samuvk — Thu, 28 Jan 2010 15:13:10 GMT

I'm using Visual Basic in Excel 2003.

I have a Sub definied as follow:

Public Sub Conect(ByVal hServer As Long, ByVal hpool As Long, ByVal vServerName As Long, ByVal vUserName As Long, ByVal vPassword As Long, hUser As Long)

BODY

End Sub

The thing is when I try to call the Sub I'm doing as follow:

Sub Conect(hServer, hpool, vServerName, vUserName , vPassword , hUser

When I call the Sub I get the next error message:

Compile error:

Expected:=

Could anyone help me to understand why is this happening and how can I fix it?

The thing is that when I use only one variable I don't have any problem

Need code for expanding Word table rows with copied default formfields

SeefDawg — Tue, 19 Jan 2010 15:34:52 GMT

I'm working on a Word template (form) that contains tables having a mix of formfields such as textinput, dropdowns, and checkboxes. I would like to provide a single default data row for each table, but if the user enters valid data in the first cell of the last row, the table expands with a new default row below the active cell the user just filled in. The following code is latest draft for a table where default textinput for cell(1) is "nn", but requires at least two data rows and is not smart enough to know if another default row is available below the active (selected) cell:

Sub AddRow()
Dim thisDoc As Document
Set thisDoc = ActiveDocument
Dim oTable As Table
Dim oCell As Cell
Dim oPrevRow As Row, oNewRow As Row
Dim tmp
'Unprotect Document
If thisDoc.ProtectionType <> wdNoProtection Then
thisDoc.Unprotect
End If

tmp = Selection.Text
If tmp <> "nn" Then
If tmp <> "n" Then
'Selection.FormFields(1).TextInput.Clear
' Copy last row
Set oTable = thisDoc.Tables(10)
Set oPrevRow = oTable.Rows(oTable.Rows.Count)
oPrevRow.Range.Copy
Set oNewRow = oTable.Rows(oTable.Rows.Count)
' Copy last row to new row
oNewRow.Range.Paste

End If
End If
'Protect Document
If thisDoc.ProtectionType = wdNoProtection Then
thisDoc.Protect wdAllowOnlyFormFields, NoReset:=True
End If
End Sub

I also would like to ensure that the formfields in the new row are reset to their default values without resetting the entire form.

Loss VBA Password

yuro84 — Sat, 12 Dec 2009 03:01:26 GMT

Anybody can help me how to crack my excel VBA password?

A template for making bound documents

Pepper — Sun, 27 Dec 2009 04:23:28 GMT

I would like to have a template for preparation of bound documents.

For example, I have prepared a document of 4 pages. On an extra (5^th, auxiliary) page there is an inscription “Bound, numbered and sealed are 4 (Four) sheet(s)”.

Then I cut this inscription with scissors, staple the document, sew it with a thread, and then glue the sticker.

Testing for blank and/or empty rows or columns

devophil — Sat, 26 Dec 2009 19:08:40 GMT

I wish to loop through each row of a set area of a worksheet and delete only those rows which have nothing stored, regardless of whether they have never been used, or, if they once had contents in a cell/s that have since been cleared. If any cell in the row has had its formatting altered or anything, I still wish it to be considered empty; as long as there is no formula or entry in a cell, I want it detected to be deleted.

1. My text has the line

If IsEmpty(Range("A1")) then ....

which will test only if a particular cell is empty.

If I try

If IsEmpty(rows(1)) then

there is no compile error, but, if row 1 (1) has nothing in any cell, it still won't match if I have nothing in row 1 of the worksheet.

2. What is the difference between somthing being empty or something being null? I have tried other codes with null to try to detect empty lines, but they've caused run errors or again logical ones.

Many thanks to anyone who can assist :-)

VBA MSG BOX

yuro84 — Sat, 12 Dec 2009 04:17:46 GMT

Hi,
Can someone help me? I want to develop my spreadsheet as below;
Let say if:

A1>12.5 the off spec msg box will appear
B1>12, the off spec msg box appear

In the msg box:
A= offspec. Reason = To be filled by user. Action taken: To be filled by user
B=offspec. Reason=To be filled in by user. Action taken: To be filled by user
Ok button.

After click ok button, another form appear in excel format and the input at the “to be filled by user” will be appear in the form

Controls- Populating a textbox with Access values

Sunzah — Thu, 03 Dec 2009 07:57:27 GMT

I'm working on Access 2003 and i was creating a report. Now that i'm done creating my report how do i go about binding textboxes [Unbound Fields] to dbase or what's the code that populates the the textbox in the report which should pick up a certain column in a certain table in a database

Excel Update

kippi — Fri, 13 Nov 2009 11:03:28 GMT

Hey Guys,

I have been left an excel spreadsheet from the last IT person here. He left some documents on how to setup the machine for this but left a whole load of information out, for example that I need to register WINSOCK.

I have got one PC working with the spreadsheet but the other machine errors when when I try to do an update with "#ErrorDB Error: Update AskDiscountMargin not updated." If I run excel with as Admin it works fine.

What would the best way of debuging this be?

The sheet is updating a MySQL database, I have all the permissions on WINSOCK etc.

Thanks for your help.

Chris

DATE COLUMN

gencom99 — Thu, 24 Sep 2009 15:34:35 GMT

I am connecting to Oracle through MS QUERY. The result is put into excel.

Set Query = QuerySheet.QueryTables("MONEY")
Query.Sql = "SELECT NUMB_TBLE.NUMB_NAME, TO_CHAR(NUMB_DATES_TBLE.DAY_MTH_YR, 'MONTH'), SUM(NUMB_TBLE.NUMB_PAY) " _
& "FROM JES.NUMB TBLE NUMB TBLE,
JES.NUMB_DATES_TBLE NUMB_DATES_TBLE" _
& "WHERE NUMB_TBLE.NUMB_ID = NUMB_DATES_TBLE.NUMB_ID AND NUMB_TBLE.NUMB_CODE IN (" & NumList & ") " _
& "Group by numb_tble.numb_name, TO_CHAR(NUMB_DATES_TBLE.DAY_MTH_YR, 'MONTH')
ORDER BY TO_CHAR(NUMB_DATES_TBLE.DAY_MTH_YR, 'MONTH')

This doesn't accomplish what I want

In my query, I want to display the month in words
I want to order the month by number

For example the result would be

Jones January 10.00
Doe April 20.00

How do I do this?

Column alias

gencom99 — Wed, 23 Sep 2009 13:26:02 GMT

I would like to put column aliases on some of the fields in the SELECT statement.

Private Sub CommandButton1_Click()
Dim NumList As String
Dim SourceBook As Workbook
Dim ListSheet As Worksheet
Dim QuerySheet As Worksheet
Dim Query As QueryTable
Dim Numb As String

Set SourceBook = Application.ActiveWorkbook
Set ListSheet = SourceBook.Worksheets("Main")
Set QuerySheet = SourceBook.Worksheets("MONEY")
Numb = Range("H6")

If ListSheet.Cells(6, 8).Value = "" Then Exit Sub
NumList = "'" & ListSheet.Cells(6, 8).Value & "'"

Set Query = QuerySheet.QueryTables("MNY")
Query.Sql = "SELECT NUMB_TBLE.NUMB_NAME, extract(month from NUMB_DATES_TBLE.DAY_MTH_YR) " _
& "FROM JES.NUMB TBLE NUMB TBLE,
JES.NUMB_DATES_TBLE NUMB_DATES_TBLE" _
& "WHERE NUMB_TBLE.NUMB_ID = NUMB_DATES_TBLE.NUMB_ID AND NUMB_TBLE.NUMB_CODE IN (" & NumList & ") " _
& "Group by numb_tble.numb_name,
extract(month from NUMB_DATES_TBLE.DAY_MTH_YR)

Query.Refresh (False)

End Sub

I want to alias the column NUMB_TBLE.NUMB_NAME, I want it to say LAST_NAME

I have tried NUMB_TBLE.NUMB_NAME AS LAST_NAME. It works but it changes the order of the columns. I do not want the order of the columns to change.

When the query runs, in Excel, the column headings are DAY_MTH_YR and LAST_NAME

If I do not use the alias, the column headings in Excel are NUMB_NAME, DAY_MTH_YR.

I want the order to still be Name of the person, Date of the transaction.

When I use the alias, it becomes Date of the transaction, Name of the person

The order changes.

How do I alias the columns without effecting the order of the columns.

I connect to the Oracle database and the result set is put into excel

RUN-TIME ERROR '1004' General OBDC Errror

gencom99 — Wed, 23 Sep 2009 08:55:52 GMT

Private Sub CommandButton1_Click()
Dim NumList As String
Dim SourceBook As Workbook
Dim ListSheet As Worksheet
Dim QuerySheet As Worksheet
Dim Query As QueryTable
Dim Numb As String
Set SourceBook = Application.ActiveWorkbook
Set ListSheet = SourceBook.Worksheets("Main")
Set QuerySheet = SourceBook.Worksheets("MONEY")
Numb = Range("H6")
If ListSheet.Cells(6, 8).Value = "" Then Exit Sub
NumList = "'" & ListSheet.Cells(6, 8).Value & "'"
Set Query = QuerySheet.QueryTables("MNY")
Query.Sql = "SELECT NUMB_TBLE.NUMB_NAME, TO_CHAR(NUMB_DATES_TBLE.DAY_MTH_YR, 'MONTH'), TO_CHAR(NUMB_DATES_TBLE.DAY_MTH_YR, 'YYYY'), SUM(NUMB_TBLE. PAY_IN), SUM(NUMB_TBLE. PAY_OUT), SUM(NUMB_TBLE. NET_PAY) "_
& "FROM JES.NUMB TBLE NUMB TBLE,
JES.NUMB_DATES_TBLE NUMB_DATES_TBLE" _
& "WHERE NUMB_TBLE.NUMB_ID = NUMB_DATES_TBLE.NUMB_ID AND NUMB_TBLE.NUMB_CODE IN (" & NumList & ") " _
& "Group by numb_tble.numb_name, TO_CHAR(NUMB_DATES_TBLE.DAY_MTH_YR, 'YYYY'), TO_CHAR(NUMB_DATES_TBLE.DAY_MTH_YR, 'MONTH')
Query.Refresh (False)
End Sub

When I add SUM(NUMB_TBLE. NET_PAY) to the code, I receive General ODBC error.

Before I add this code, the query works.

What is going on?

How do I fix it?

Run-Time Error '9' Subscript out of range

gencom99 — Sun, 20 Sep 2009 14:15:20 GMT

I am using a spreadsheet. It has two tabs MAIN and TOTAL CASH. On the MAIN tab is a BUTTON and a cell where to enter a number. After you put a number in the cell and click the button, the results of the query is in the tab TOTAL CASH

Private Sub CommandButton1_Click()
Dim NumList As String
Dim SourceBook As Workbook
Dim ListSheet As Worksheet
Dim QuerySheet As Worksheet
Dim Query As QueryTable
Dim Numb As String

Set SourceBook = Application.ActiveWorkbook
Set ListSheet = SourceBook.Worksheets("Main")
Set QuerySheet = SourceBook.Worksheets("TOTAL CASH")
Numb = Range("H6")

If ListSheet.Cells(6, 8).Value = "" Then Exit Sub
NumList = "'" & ListSheet.Cells(6, 8).Value & "'"

Set Query = QuerySheet.QueryTables("T_CASH")
Query.Sql = "SELECT CUST_TBLE.NAME, CASH_TBLE.SAV_AMT " _
& "FROM JES.CUST TBLE CUST TBLE,
JES.CASH_TBLE CASH_TBLE" _
& "WHERE CUST_TBLE.NUM_ID = CASH_TBLE.NUM_ID AND CUST_TBLE.NUM_CODE IN (" & NumList & ") "

Query.Refresh (False)

End Sub

This works.

In a new workbook, I created two tabs, MAIN and MONEY. On the MAIN tab is a BUTTON and a cell where to enter a number. After you put a number in the cell and click the button, I want the results of the query in the tab TOTAL CASH. This is not working. This code is very simiar to the code above that works. The only difference is the query. I am getting this error: run-time error '9'-- Subscript out of range. This is the line with the error:
Set Query = QuerySheet.QueryTables("MNY")

Why does one code work, and the other code does not?

This is the code that does not work.

Private Sub CommandButton1_Click()
Dim NumList As String
Dim SourceBook As Workbook
Dim ListSheet As Worksheet
Dim QuerySheet As Worksheet
Dim Query As QueryTable
Dim Numb As String

Set SourceBook = Application.ActiveWorkbook
Set ListSheet = SourceBook.Worksheets("Main")
Set QuerySheet = SourceBook.Worksheets("MONEY")
Numb = Range("H6")

If ListSheet.Cells(6, 8).Value = "" Then Exit Sub
NumList = "'" & ListSheet.Cells(6, 8).Value & "'"

Set Query = QuerySheet.QueryTables("MNY")
Query.Sql = "SELECT NUMB_TBLE.NUMB_NAME, extract(month from NUMB_DATES_TBLE.DAY_MTH_YR) " _
& "FROM JES.NUMB TBLE NUMB TBLE,
JES.NUMB_DATES_TBLE NUMB_DATES_TBLE" _
& "WHERE NUMB_TBLE.NUMB_ID = NUMB_DATES_TBLE.NUMB_ID AND NUMB_TBLE.NUMB_CODE IN (" & NumList & ") " _
& "Group by numb_tble.numb_name,
extract(month from NUMB_DATES_TBLE.DAY_MTH_YR)

Query.Refresh (False)

End Sub

Trouble with searching and displaying an array

Bill — Sat, 05 Sep 2009 20:51:22 GMT

Hey all, My name is Bill and i an a uni student. i am having trouble displaying the result of a search i am trying to put together.After clicking on the search button, my msgbox comes up saying that there are no matching record, but i know there is. the question is as follows:
Implement a search function so you can look up a part number in your table. Your search operation should return the result in a messageBox.

here is my code:

Public MyValue1 As String
Dim Formlength, looking As Integer
Public MyTable As Object

Public MyValue1 As String
Dim Formlength, looking As Integer
Public MyTable As Object

Private Sub Search_Button_Click()
Set MyTable = ActiveDocument.Tables(1)
Formlength = MyTable.Rows.Count
If looking > Formlength Then
'reset looking if not first search
looking = 1
End If
If looking = 0 Then
'initialize looking for the first search
looking = 1
End If
MyValue1 = Search_String_1.Value
'get the search string
If MyValue1 = "" Then
MsgBox ("You have not entered a partnumber to search for!")
Unload UserForm1
Set MyTable = Nothing
Exit Sub
End If
Set MyTable = ActiveDocument.Tables(1)
looking = looking + 1
'increment in case the search function was exited early
Call findit
End Sub
Public Sub findit()
While looking < Formlength + 1
'setup the loop
If MyValue1 = MyTable.Cell(looking, 1) Then
Debug.Print MyTable.Cell(looking, 1)
'if the search string is found in this cell
MyTable.Cell(looking, 1).Select
partnumberprint.Value = MyTable.Cell(looking, 1)
costprint.Value = MyTable.Cell(looking, 2)
quantityprint.Value = MyTable.Cell(looking, 3)
totalprint.Value = MyTable.Cell(looking, 4)
' print result in text box
Exit Sub
End If
looking = looking + 1
Wend
If looking > Formlength Then
MsgBox ("End of records! Your searched record has not been found.")
looking = Formlength
'looking reset so it will be initialized correctly in the next search
Unload UserForm1
End If
End Sub
End Sub