Tracking Forums, Newsgroups, Maling Lists
Home Scripts Tutorials Tracker Forums
  HOME    TRACKER    Visual Basic

Creating Tables In Word Using VBA ?

i need to create multiple tables using vba in word. the problem which i face is that when i try to "copy" and then "paste" it in the same document, it creates another CELL rather than a table and then i becomes a problem for my program. then the second thing i tried was to create multiple table using "range" thingy but the control remained in the first cell and all other tables were being created within that first table. all i need is to create multiple tables in a single word document that i am working on. Need some guidance either in creating them in a different way altogether or moving the control/cursor to a new line after creating first table.

View Complete Forum Thread with Replies

See Related Forum Messages: Follow the Links Below to View Complete Thread
Creating Tables In MS Word
Does anyone have a piece of code that shows how to create a table in word using vb code? I've gotten pretty good at formatting text in word from vb, but for my current project I would like to create a table and insert some values into the cells of that table from vb code. I've read the help files but they mainly deal with the formatting and obtaining of data in the table, not the actual creation of a table. Thanks in advance for any help!


Creating Tables In MS Word
I want to know about the APIs used/methods to create tables in MS WORD using VISUAL BASIC.

Please provide me the answer as soon as possible.

Problem With Creating Tables In Word
I have a problem. I'm tryin to creat a table in Word through my VB-code. Now I first have recorded a macro in Word en when I tried to put the created code in my VB-program he gives me errors that het has problems with adding the table. Can anyone tell me what is wrong here.

Set oApp = CreateObject("Word.Application")
Set oDoc = oApp.Documents.Add
oApp.ActiveDocument.Tables.Add Range:=Selection.Range, NumRows:=3, NumColumns:= 6, DefaultTableBehavior:=wdWord9TableBehavior, _ AutoFitBehavior:=wdAutoFitFixed

Thank you

Creating Embedded Tables In Word With VBA?

I'm creating a Word document, from a template, to generate a series of labels. The formatting of the individual labels is somewhat complex, and seems to be handled best with a table. The label template is itself a table.

Procedurally, here's how it breaks down: I need to walk the cells of a table. Inside each cell I need to create a new table. I then need to walk the cells of that inner table, filling in data as I go, and then I need to step to the next cell in the outer table and repeat the process. I just can't figure out how to switch between the inner table and the outer table...

I'd really appreciate any insight anyone could offer. Thanks!

Creating Dynamic Tables In Word
is there a way to create dynamic tables in word using the number of rows in a table

I need the syntax - or any helpful links on how to manipulate word objects

Thanx N Adv

Creating Complex Word Tables
I am trying to make a table likemthe table shown in the attached docuent, it this posible from v.b.? i can create a normal table no problem, and split cells usinig the split command, but i have no idea how to make a table like the one attaached, please help if you can....


Tricky Problem With Creating Multiple Tables In Word From VB
Please save my sanity....I began responding to a thread on another forum earlier today and we got pretty far with the problem save for what follows:

Initial objective: Read the rows from multiple ListViews on a VB form, then transfer them into tables into Microsoft Word. There are six or more listviews per form, and each form needs a button that will magically export all to Word (and then email to a list - although this does not regard my question here)....

We have figured out how to get the data from the listview into ONE table, but after the first table, the code crashes saying "Item not in collection". This is because the second time we try to add another table, the apparant index remains the same. I say "Apparanet" as we have VB experience but not with Word - the attached project was pieced together combining a class I downloaded (with modifications) and some of my own library of functions/subs. The following code demonstrates where the error is:


Public Sub InsertTableWithData(m_Recordset As ADODB.Recordset)
On Error GoTo Error_Handler
 Dim intNumofRows As Integer
 Dim intNumofColumns As Integer
 Dim p As Integer, ColWidth As Integer
 Dim i As Integer
    intNumofColumns = m_Recordset.Fields.Count
    intNumofRows = m_Recordset.RecordCount
    'Insert a new table with rows according to recordCount plus Column header
    'and the number of columns in the recordset
    wrdDoc.Tables.Add wrdSelection.Range, NumRows:=intNumofRows + 1, _
    NumColumns:=intNumofColumns, DefaultTableBehavior:=wdWord9TableBehavior, AutoFitBehavior:= _
    iTableCounter = iTableCounter + 1
    With wrdDoc.Tables(iTableCounter) '<----- this is where error occurs on second calling of this sub in an external loop that calls it

    ' Set the column widths
        For i = 0 To intNumofColumns - 1
        ColWidth = Len(m_Recordset.Fields(i).Name)
           .Columns(i + 1).SetWidth ColWidth * 25, wdAdjustNone
           .Cell(1, i + 1).Range.InsertAfter UCase(m_Recordset.Fields(i).Name)
        Next i
           ' Set the shading on the first row to light gray
           .Rows(1).Cells.Shading.BackgroundPatternColorIndex = wdGray25
           ' Bold the first row
           .Rows(1).Range.Bold = True
           ' Center the text in Cell (1,1)
           .Cell(1, 1).Range.Paragraphs.Alignment = wdAlignParagraphCenter
           ' Fill each row of the table with data
            For i = 1 To intNumofRows
                For p = 1 To intNumofColumns
                    FillRow i + 1, p, m_Recordset.Fields(p - 1)
                Next p
            p = 1
            Next i
    End With
   Set m_Recordset = Nothing
   wrdSelection.MoveDown Unit:=wdLine, Count:=intNumofRows + 5 '<----this is attempt to correct but failed - code still fails without it

   wrdSelection.TypeParagraph '<--- ditto as above
  Exit Sub
    Debug.Print Err.Description & " - " & Err.Number & " " & iTableCounter
End Sub

The above routine gets called in a loop...first time is successful, second time it crashes because the index went up, but the second table was added to the first element (apparantly)...after the loop that calls this sub terminates, the file is saved. I open the file and I see a table with a bunch of blank spaces, then beside that, another one that is populated (before it crashed of course), although one column is in the wrong place. We have tried skipping lines, paragraphs, you name it - bottom line - we know little about VBA for Word (but a fair bit about VB!! and know damn well its probably only one *#!*!?# line of code!! )


ps Please trust me when I say the right syntax was used to create and instantiate all objects (including those in the above code which you may not see declared) as well as the appropriate reference to a Word library.


Edited by - ahara on 4/25/2004 7:50:30 AM

Examples For Creating Complex Word Documents (with Tables, Formatted Text, Etc)?
Can anyone point me to some example code for creating MS Word document from VB?

I would like to find some examples that add text with various formatting, page breaks, tables, etc.


"-" Causing Problems When Creating MS Word Tables
Hello All,

Every "-" within a string of text splits and creates a new column when creating Table in Word.
Any ideas how I can eliminate Word adding column at "-" ?

Warm regards,
Mike "the Rat"

VB, MS Word Macro - Prompt # Of Tables Wanted, Copy + Paste # Of Tables
I have a Microsoft Word document that contains a table with form fields.

I would like it set up so that when the user first opens up the document, Microsoft Word prompts the user "how many additional tables" they want in addition to the one that already exists in the document.

The macro would then select and copy, then paste the table "that many times" underneath.

Unfortunately I don't know much about Visual Basic. On the bright side, I do know how to make macros in Word. Can anyone here help me out?

Creating Tables
Edit by herilane: Split from hereHi,Supa-Fly,
It is very helpful your last post to my project now.

But i encountered a big problem now.....
In my project i need to create around 50-100 tables in the document.
I have problem to create mutiple tables..

In my program i coded like this to create multiple tables:
'create the nTH table
Call AddTable(oDoc, oRange, n, 9, 2)

'add space between the tables
oDoc.Tables(n).Select 'select the table we just create

Selection.MoveDown Unit:=wdLine, Count:=2 ' move the cursor down
Selection.TypeParagraph 'add two blank paragraphs
Set oRange = Selection.Range 'define new oRange

'create the (n + 1)TH table
Call AddTable(oDoc, oRange, (n+1), 9, 2)
The problem is that my codes can not always excute well.
Sometimes, the next table will be created inside the cell of the previous table.
If change the number of "Selection.TypeParagraph", there will be error-free, or the error will occur at another place...
And this error will stop the excution of the program.

It seems impossible to generate such error from my codes.
Is there any good idea to add space between two tables....

Really appreciate your helpful reply........
I have to submit the project this friday and do the Demo also....

Many thanks to you, ...........


Creating Tables
I'm trying to write several macros to create tables with particular attributes in the individual cells (font size, bold, width etc).
This seems to work but I need the tables several times in the same document. When I use the macro a second time it creates a new table but the formatting (font size ...) is applied to the first table not the new one.
How can I code the macro so the formatting applies to the new table and not the original?

I hope my question is clearly explained, I can show the code if it helps
(but haven't here because of space).



Creating SQL Tables With VBA
I'd like to create tables with VBA. Anyway it doesn't seem to work when I try to add variables in it.

For example:

With table
Do While Not .EOF
DoCmd.RunSQL "CREATE TABLE " + !variable + " (blah blah);"
End With
This just gives me syntax error.

Creating Tables...
Hi everyone.... guess my question is being asked before but please bear with me... i need to create a new table in an existing database after clicking on a button... searched the forum and most of them includes ADOX and DAO.... is it a must to hav those or can i use ADODB or data control?

Btw... i am using VB6 and Access 97.... hope u all can help.... Thanks a lot...


Creating Tables In VB
I am doing a project on payroll with vb as front end & access as back end.

I want to create individual tables for every new month using the front end only.

The user should be able to create a new table for the month of
October 2002. The table name should be October2002

How to create the table using Visual basic in Access ?

can i use the adodb.command property to create the table at the back end ?

Pls help.


Creating Tables
I need to be able to create a table with a set amount of columns. I have the following code;

Sub ADOX_CreateJetTable()

'This example demonstrates creating a Jet table with
'Autoincrement, Decimal, and Memo columns.

Dim tbl As New ADOX.Table
Dim cat As New ADOX.Catalog

'Return Reference to current database.
Set cat.ActiveConnection = DBname.Connection

'Assign the new table name.

With tbl
.Name = TableName

' Append new columns to the table.
With .Columns

.Append "1", adLongVarWChar
.Append "2", adLongVarWChar
.Append "3", adLongVarWChar
.Append "4", adLongVarWChar

End With
End With

' Append new table to the provider catalog and clean up.
cat.Tables.Append tbl
Set cat = Nothing

End Sub
Now I have highlighted some code in red. Is it possible to change it to the following code??

With .Columns

u = 1

for i = 0 To Varibale

.Append "'u'", adLongVarWChar

cat.Tables.Append tbl

Next i

End With

Can some one point me in the right direction if it is wrong???

Creating Tables In VB For SQL

I have a little problem here, I am working in VB 2005 express, I can't figure out how to get the right calculation in VB to give me 3 days after due dates I'm connected to SQLexpress and in SQLexpress I have a table called contracts I wrote a codes that generated the dates that i needed in VB to imports in SQL. but now i need to know how can i get VB to recognize the contract column and give me 3 days after the contract column... I know this sound confusing

*table Contract*


I already have column1 figure out - but how do i get column3 to recognize the dates in colume1 and give me 3 days after that day?

Creating Tables With VB6
How do I create a table in access using VB6
Currently I've created the table, but there's no column in it.
How do I resolved this issue? I've searched the forum but couldn't find anything thats solves my problem

VB Code:
'Add new table/group to databasePrivate Sub cmdCreate_Click() 'Declared variablesDim dbIP As New ADODB.Connection 'Open connection to databasedbIP.Open "dsn=NCMSdB" 'Query databasesql = "Create Table " & txtGrpNm.Text & "[(GRP)]" 'execute sqldbIP.Execute sql  'Close databasedbIP.Close End Sub

DAO Creating Tables From 1 DB To Another
Does anyone have the code that will delete and then create tables an one database using the tables from another database?

In other words...

Start with a Export.mdb database,
run a process that reads through the tables in a full database (Main.mdb)
the process should take the name of Main.mdb tables and use that name to create the table in Export.mdb.
If the table already exists in Export.mdb, then delete it before creating the new table.

Thanks ahead of time!!

Creating Tables In The DB
I have never used a command object before and my book says that I should use it when trying to create tables in my DB. Never heard of it before, let alone know how to use it. Does any one have any code examples on how to create a table in the DB? Thanks for any help.

Creating Tables

I need your help again.

I have build a program using DAO and an Access database. This works well. I could say great.

Now the problem:

The company using this program has found out that working with 50 people in 1 Access db isn't fun. So they want to Upgrade to a SQL server.
In most of the code this isn't any problem, but I have never made a new table in a database using ADO.
Can someone show me how to do this.

Just for the record, these databases aren't copies of the Access DB, but there new tables that have never existed before.

Creating Tables With ADO
Hello again,
I am here with anopther easy(!) question for most. What am I going to select from references to creat Access Table with ADO 2.7?

Thanks a mil...


Creating Tables In Foxpro
Does anyone know how to create tables in foxpro using VB? I am having some difficulties doing this????

Thanks Cory

Creating New Tables And Links
I am currently using one database with path specified in a .ini file. The program involves creating temp tables based on different criteria each time, calculations using data tables that remain static, getting a result, and then deleting the temp tables. This goes on and on each time. I would like to create a new database in which all the temp tables would be created, stored, and then deleted when the work is finished to minimize database size in the one where data tables would be stored. Need to link the temp tables in the new database to the data tables in the other database though. Is there some way to accomplish this? Some sort of dynamic linking? New lines to my .ini file to read that will point to each database as a separate connection? Or two .ini files with a separate path in each and therefore two different connections?

Many, many questions I know. Would greatly appreciate any advice and suggestions.

Creating Tables In Mysql
i recently installed mysql im my computer and i installed mysqladmin. i can see it running in my pc but the problem is... i dont know how to create new database for my project. im so intimidated with mysql manual. it's too hard for me to understand how to use this.

my another question is that how can i import my access database in mysql?

Creating Tables In MS Access 2K2
Could anyone help me ??? I want to create a new table in my MS Access 2K2 (Password Protected) DB through vb code. I am using VB 6 with service pack 5 and have also included reference to MS DAO 3.6 object library. Surprisingly I am able to create the table if the DB is in 2000 format but not in 2002 format in MS access. Moreover I would like to add these fields to the Database....

CNo - Autonumber
ForceClose - Yes/No (Boolean)
BillNo - Long Integer (this is an indexed field)

Please help its very urgent i submit my project before the 30th october.......

Thanks forum.

Creating Tables Dynamiclly
I'm designing a VB database program and I need some serious help.

Basiclly I need to know how can I create new tables dynamiclly from withing the program during runtime. And I need someone to clerify to me how some of this is done in Laymens terms.

Reboot posted on a previous post of mine and said:
"You can use SQL to create tables, or ADOX"
ADOX seems to confuse me a bit so I think I should stick to SQL.

============Where I'm at:=============
I originally created a .mdb file with Access that holds a few nessissary tables and I'm using this file with my VB project. I realized I need to expand this program/database to allow users to add new Tables to the Database via some sort of Table creating wizard I need to add to my project. But, if I have the ability (eventually) to create new tables dynamically, maybe I don't even need the .mdb file from access.??? Maybe I can have the program create the whole database itself and cut out the middle man (ACCESS) all together.

So I guess I don't understand how I could incorporate a database into my program without initially creating a .mdb from Access or the like. How would I create my Database file itself from VB? All the tutorials and books I've looked at tell me to make a connection to my already created database.

HELP! I've been reading my tale off but I can't quite figure out how to do this.

Could someone please explain all this to me in Laymen's terms. PLEASE!??????????????????

Creating A Fixture From 2 Tables
I have 2 database tables both of them have the same fields, name and class. Now i'm trying to create a fixture list between the lot that will not put anyone who is in the same class against each other

here's my code so far:-

Private Sub cmddisplay_Click()
datGreen.Recordset.MoveFirst 'this tells it to go to the first record
Do Until datGreen.Recordset.EOF 'this sets the recordset
lstGreen.AddItem datGreen.Recordset.forename
lstGreen.AddItem datGreen.Recordset.Class

If datGreen.Recordset.EOF Or datYellow.Recordset.EOF Then
Exit Sub
End If

If datYellow.Recordset.Class = datGreen.Recordset.Class Then
lstYellow.AddItem datYellow.Recordset.forename
lstYellow.AddItem datYellow.Recordset.Class
End If

End Sub

Creating Tables With Use Of Variables
I need to be able to create a table in access using variables. I need the table its self to be names after a certain variable and then all of the corresponding rows to also be named after specific variables. It sounds a bit dodgey i know!!


Variable 1 = Andy ( Table Name)

Variable 2 = height ( Row Name)
Varibale 3 = Weight ( Row Name)
Varibale 4 = eye colour ( Row Name)

Is this possible to do??

Creating Multiple Tables From One
I'm searching for a method of creating multiple like tables from a single larger table in MS Access based upon the contents of a data element changing. Please help

Help With Creating Tables In Access
help!! can anyone tell me how set make a column to become of autonumber type for my access tables whenever i create a table within my VB program?? tnxtnx

Creating Tables At Run Time
How to create tablea with ado at run time i.e, when the application is running from vb6.0?
(Database: MS Access 2000)

thanx in advance...

Creating A DataBase && The Tables In An App.
I've just started to develope an employee database. I would like some help with giving the user the option to Open an existing DB or Create a new one. Also if the user decides to create a new one how do I go about creating the new tables for the DB( in my App )? Using Access 2000. Any other tips would be great as well.

Creating A Report From 3 Tables
I have 3 inventory tables: Computer, Server and Printer. each tables primary key is a autonumber

I have 3 data reports that list the inventory in each that use a SQL query to do it. such as

Select ProductType, ProductName, UnitsInStock FROM ComputerInventory ORDER BY ProductType

Is there a way to create a report that can combine all those and list the inventory in all 3 tables, in essence a total inventory report? i'm not too keen on the data environment and reports but i'm just wondering if its possible. i'm not sure if i really need it but if its doable it might be nice to have


Creating/Deleting Tables With ADO/VB6
Hello again,

My problem is regarding an ADO control that queries an Access DB that doesn't contain the table that is being referred to in the SQL query. Below is an example of what I'm trying to do...


strQuery = "SELECT * FROM PC"

With adoFAST
.ConnectionString = strCnxn
.CursorLocation = adUseClient
.CursorType = adOpenStatic
.CommandType = adCmdText
.RecordSource = strQuery
End With
Set rstPriorities = adoFAST.Recordset

Once that code is run it generates a run-time error when it attempts to refresh that object if the table PC doesn't exist. What I want to do is create the table based on some default data if it doesn't exist. I could attempt to do this with error trapping but I don't know how to create (or delete) a table using the ADO control.

If this doesn't make sense just ask for some more info and I'll try to explain better.

Thanks for any help in advance!


Creating Tables From Querys
Hi, I have an Access Database with some querys. I need to make another database with the result of that querys as tables. In other words I need a new database with tables (which are querys in other database).
I know how to create the database, and tabledefs, but the problem is that the Createtabledef method only supports tables, not querys. Does somebody know how can I do.
Thanks in advance.

Creating Tables With ADOX
Does anybody know how to create fixed decimal point fields using ADOX?

I've created a table and want to append a column that will contain values with 6 decimal points.

Is this possible?


HELP!!! Creating Access DB And Tables Through VB
I'm really so new with these things so i really need your help.

Is it possible to create database in MS Access through Visual Basic using Sql query? If it is, how should I write the code? Should I leave the DataSource blank or should I not write it [datasource] in my connection string?

Another thing, is it also possible to create tables? If it is, how should I write the code? In this case, I don't think I'll be using recordset or recordsource or am I wrong?


Nested Tables Creating
hai guys

plz tell me that how to create nested tables in oracle and how to access them from vb.


Creating Tables In Access Through ADO
Hi Anyone,

Suppose I have a mdb with to tables respectively detailing the tables to be created and the fields per table. How could I go about it to create tables in the same mdb according to the specification in the two pre-existing tables. I mean: What objects and methods in ADO would allow this from VB.

Thanks for any suggestion,


VBA: Creating Word, And Sending Data To A Word Document Using Ranges
The following code works fine when it is executed only once in any instance of the VB program.

It is OK provided I do not close Word, and it will create multiple instances of the document into the same instance or word.

Screen.MousePointer = 11
WordApp.Documents.Open ("C:Program FilesWill WizardTemplatesClauseLib.doc")
set MyDoc = Documents.Open(FileName:="C:Program FilesWill WizardTemplatesClauseLib.doc")
set FldRng(1) = MyDoc.Range(Start:=MyDoc.Bookmarks("Name").Range.End, End:=MyDoc.Bookmarks("NameEnd").Start - 1) 'read bookmarks into range object variables
FldRng(1).Text = ClauseHold 'put the stuff into the document
WordApp.ActiveDocument.SaveAS FileName:=("C:Program FilesWill WizardTemplates emp" & Trim(Str(TMPNum)) & ".doc")
WordApp.Visible = true
set WordApp = nothing
set MyDoc = nothing
Screen.MousePointer = 0
Command8.Enabled = true
Exit Sub

If Word is closed, and the code is executed again, an error occurs "the remote server machine does not exist or is not available" The only remedy for this is close and re-start the VB application containing this code.

The GetWord2 et-al Function codes are: (which is stored in a module)

option Explicit
Declare Function FindWindow Lib "user32" Alias "FindWindowA" (byval lpClassName as string, byval lpWindowName as string) as Long
Declare Function SendMessage Lib "user32" Alias "SendMessageA" (byval hwnd as Long, byval wMsg as Long, byval wParam as Long, byval lParam as Long) as Long
public WordApp as Word.Application
public WordWasNotRunning as Boolean ' Flag for final word unload

public Sub DetectWord()
' Procedure detects a running Word and r
' egisters it.
Const WM_USER = 1024
Dim hwnd as Long
' If Word is running this API call retur
' ns its handle.
hwnd = FindWindow("OpusApp", vbNullString)
If hwnd = 0 then ' 0 means Word Not running.
Exit Sub
' Word is running so use the SendMessage
' API function to enter it in the Running
' Object Table.
SendMessage hwnd, WM_USER + 18, 0, 0
End If
End Sub
public Sub GetWord2()

' Test to see if there is a copy of Micr
' osoft Word already running.
on error resume next ' Defer error trapping.
' Getobject function called without the
' first argument returns a
' reference to an instance of the applic
' ation. If the application isn't
' running, an error occurs.
set WordApp = GetObject(, "Word.Application")
If Err.Number &lt;&gt; 0 then
WordWasNotRunning = true
WordWasNotRunning = false
End If
Err.Clear ' Clear Err object In Case error occurred.
' Check for Microsoft Word. If Microsoft
' Word is running,
' enter it into the Running Object table
' .
If WordWasNotRunning = true then
'set the object variable to start a new
' instance of Word.
set WordApp = new Word.Application
End If
' Show Microsoft Word through its Applic
' ation property. then
' show the actual window containing the
' file using the Windows
' collection of the MyWord object refere
' nce.
WordApp.Visible = true
WordApp.Application.WindowState = wdWindowStateNormal
Screen.MousePointer = vbDefault
End Sub

do I need to close the document? = deactivate something?

I would like a code example on how to fix the above, else, any suggestions, ideas...


Creating Pivot Tables Automatically

I have a report that I can reformat largely via a follow me macro.

Once reformatted I need the report to go into a pivot table.
I can do this ok but the problem is that the tabel created is a new one each time I run the macro. I would like the pivot table to be updated but the follow me macro 'refresh' data doesn't work as the selection column have now changed (the report is generated into this new sheet from elsewhere) and columns added to the 'raw report' (causes problems when asking for a data refresh via a macro)

If anyone knows how the pivot table can be amended then this would be very helpful to me as I need to add things to the table (i.e. limits...have they been exceeded etc)

The code is below

Kind Regards

Sub pivot()
' pivot Macro
' Macro recorded 12/03/2003 by Warfield

ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _
"Reformat!R1C1:R4935C14").CreatePivotTable TableDestination:="", TableName _
ActiveSheet.PivotTableWizard TableDestination:=ActiveSheet.Cells(3, 1)
ActiveSheet.Cells(3, 1).Select
ActiveSheet.PivotTables("PivotTable8").SmallGrid = False
With ActiveSheet.PivotTables("PivotTable8").PivotFields("MONTH")
.Orientation = xlRowField
.Position = 1
End With
With ActiveSheet.PivotTables("PivotTable8").PivotFields("INST")
.Orientation = xlColumnField
.Position = 1
End With
With ActiveSheet.PivotTables("PivotTable8").PivotFields("Long Future")
.Orientation = xlDataField
.Position = 1
End With
With ActiveSheet.PivotTables("PivotTable8").PivotFields("Short Future")
.Orientation = xlDataField
.Position = 2
End With
With ActiveSheet.PivotTables("PivotTable8").PivotFields("Long Call")
.Orientation = xlDataField
.Position = 3
End With
With ActiveSheet.PivotTables("PivotTable8").PivotFields("Short Call")
.Orientation = xlDataField
.Position = 4
End With
With ActiveSheet.PivotTables("PivotTable8").PivotFields("Long Put")
.Orientation = xlDataField
.Position = 5
End With
With ActiveSheet.PivotTables("PivotTable8").PivotFields("Short Put")
.Orientation = xlDataField
.Position = 6
End With
With ActiveSheet.PivotTables("PivotTable8").PivotFields("Total Position")
.Orientation = xlDataField
.Position = 7
End With
ActiveSheet.PivotTables("PivotTable8").PivotFields("Count of Short Call"). _
Function = xlSum
ActiveSheet.PivotTables("PivotTable8").PivotFields("Count of Long Call"). _
Function = xlSum
ActiveSheet.PivotTables("PivotTable8").PivotFields("Count of Short Put"). _
Function = xlSum
ActiveSheet.PivotTables("PivotTable8").PivotFields("Count of Long Put"). _
Function = xlSum
ActiveSheet.PivotTables("PivotTable8").PivotFields("Count of Short Future"). _
Function = xlSum
ActiveSheet.PivotTables("PivotTable8").PivotFields("Count of Long Future"). _
Function = xlSum
ActiveSheet.PivotTables("PivotTable8").PivotFields("Count of Total Position"). _
Function = xlSum
ActiveSheet.PivotTables("PivotTable8").PivotSelect "", xlDataAndLabel
ActiveSheet.PivotTables("PivotTable8").Format xlReport4
ActiveSheet.PivotTables("PivotTable8").PivotSelect "INST[All]", xlLabelOnly + _

Selection.NumberFormat = "mmm-yy"
Selection.NumberFormat = "#,##0;[Red](#,##0)"
End Sub

Creating Reports With Multiple Tables
OK, I'm not going to lie....this is my first attempt at trying to create a report using access. How do you link tables into a report?

In my design view I see how you can bound the text fields to a field in the table, but How do I bound to other tables? Do I have to build an expression or query?

Here is what I need to do for example. I need to put the name of the person on the report however, he report I built is from a froeign key table so there is just an ID. How do I get the person's name that has that ID to show up on my report?

Creating Internal Arrays And Tables
Really fundamental, but, what’s the best method for storing long or complicated data tables in a VB program?

In the old versions of Basic, you could set up loops to READ various DATA statements, and load a lot of data in a few lines of code.

VB doesn’t support that method. Their suggested alternative is to open an external file and to read in the data from there. Easy enough, but drawbacks are (1) the need to maintain the external files alongside your executable; and (2) the fact that the data files can be read and altered by others (as well as deleted).

So, I often resort to long sequences like

President$(1) = "Washington": Term(1,0) = 1789: Term(1,1) = 1797
President$(2) = "Adams, J.": Term(2,0) = 1797: Term(2,1) = 1801
President$(3) = "Jefferson": Term(3,0) = 1801: Term(3,1) =1809

. . . and so on. This gets pretty tedious (of course, you can write a utility program to read an external file and to write the VB statements for you), but at least keeps the data in the program.

Suggestions for a better way? I'm using VB 5.0 by the way.

-- Don

SQL 7.0 Frustration - Creating Databases && Tables
I just installed SQL 7.0 at home. I have spent 2.5 hours trying to create a database and a table and I have had NO Luck. I have searched the help system, researched it on the internet and even used a super ambiguous and unclear Sam's book. With VB6 and Access I was up and running in no time at all. But SQL 7.0 seems very unintuitive.

a) Can someone please tell me how to simply create a database and a table?

b) What is the conventional way to create databases and/or tables? Through Enterprise Manager? Through the Query Analyzer?

c) Creating tables is the easiest thing in the world with Access. I sometimes use SQL "Create" statements to create tables in Access. Is that what you have to use with SQL? Or, is there a graphical method?

I will greatly appreciate any tips that will get me up and running with SQL 7.

Creating And Accessing Temporary Tables Through ADO
Creating and Accessing Temporary Tables through ADO


I am creating a temporary table in my code which I then need to select data out of. If I create the temp table using a select statement e.g.:

sSQL = "SELECT CurrencyID, CurrencyName, ExchangeRate INTO #rptExchangeRatesTemp FROM rptExchangeRate WHERE CurrencyID IN (12,13,14,15)"
mCnn.Execute sSQL

It works fine and I am able to access the table and return records from it in my VB code as I would expect.

However if I create the table using the CREATE statement e.g.:

sSQL = "CREATE TABLE #rptExchangeRatesTemp (CurrencyID INT, CurrencyName VARCHAR(10), ExchangeRate FLOAT)"
mCnn.Execute sSQL

I am subsequently unable to access it, if I try and insert into it I get the message 'invalid object name' as if the table doesn't exist.

If I create a string with the create and insert on one line separated by a colon e.g.:

sSQL = "CREATE TABLE #rptExchangeRatesTemp (CurrencyID INT, CurrencyName VARCHAR(10), ExchangeRate FLOAT);" & vbCrLf
sSQL = sSQL & "INSERT INTO rptexchangerate (CurrencyID, CurrencyName, ExchangeRate) VALUES (2,'EUR',0.823036970847761)"
mCnn.Execute sSQL

I do not get an error message, so I assume the data has been inserted into the temporary table. However if I try and access the table I get the same 'invalid object name' message.

I had a look on the web and it seems as though you can get this error when you use RDO and the solution is to specify rdExecDirect in the options parameter of the .Execute statement. However in ADO I can't see an equivalent keyword - does anyone have any ideas?

Thanks in advance.

Creating Tables In ADOX Problem...
While trying to create new tables (using the Frequently Asked DB ?'s Create table in code example) in my Access 2000 (created in Access 2003) database, I am running into a problem that I haven't been able to figure out.

Dim objCat As ADOX.Catalog
Dim objMyTable As New ADOX.Table
Dim strConnection As String

Set objCat = New ADOX.Catalog
strConnection = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & App.Path & "hours.mdb"
objCat.ActiveConnection = strConnection
frmDate.Show 1 'Sets thedate to a date that will be the name of the table
If thedate = Null Then
Exit Sub
End If
With objMyTable
.Name = thedate
.Columns.Append "jobdate", adVarWChar, 12
.Columns.Append "jobno", adNumeric, 6
.Columns.Append "jobcode", adVarWChar, 12
.Columns.Append "hours", adSingle, 10
.Columns.Append "timestarted", adVarWChar, 12
.Columns.Append "description", adVarWChar, 255
.Columns.Append "thours", adSingle, 5
.Columns.Append "tmins", adSingle, 5
.Columns.Append "index", adInteger
End With
objCat.Tables.Append objMyTable 'Code errors out here
Set objMyTable = Nothing
Set objCat = Nothing

When I run my code, I get a "Precision is invalid" error on the append table code. This is my first time using ADOX, so I have no idea what that means.

Also, I am wanting the index column to be an autonumbered field, but I'm not sure how to do that. (It will become my primary key using the .Keys.Append "Primary Key", adKeyPrimary, "index" code after it's appended to the columns list as an autonumber field).

Any help would be appreciated.

Creating/opening Tables With SQL Problem
using access '03 and VB6
ok, here is my basic code for my add
everything works fine if there is no spaces in the "prodadd & compadd & bargeadd" string
bargeDB2 is defined as a variant
how do I make it not break when there are spaces?(or maybe is there code to eliminate any spacing, as that would be an easy fix)

VB Code:
bargeDB2 = prodadd & compadd & bargeaddSQL = "create table [" & bargeDB2 & "]"SQL = SQL & "("SQL = SQL & "[LoadNO] Integer,"SQL = SQL & "[LoadNI] Integer,"SQL = SQL & "[M] Integer,"SQL = SQL & "[D] Integer,"SQL = SQL & "[Y] Integer,"SQL = SQL & "[Pr] varchar(20),"SQL = SQL & "[OB] Integer,"SQL = SQL & "[IO] varchar(8),"SQL = SQL & "[TonO] Decimal(20,6),"SQL = SQL & "[TonM] Decimal(20,6),"SQL = SQL & "[TonT] Decimal(20,6),"SQL = SQL & "[TonB] Decimal(20,6)"SQL = SQL & ")"cn.Execute SQL With rs    .AddNew    .Fields("Product") = prodadd    .Fields("Company") = compadd    .Fields("Last Load #") = ordradd & " out"    .Fields("Tons Out") = tonoutadd    .Fields("Ton Balance") = tonbaladd    .Fields("Barge #") = bargeadd    .Fields("BargeDB") = bargeDB2    .UpdateEnd With 'breaks on the following linersB.Open bargeDB2, cn, adOpenKeyset, adLockPessimistic, adCmdTableWith rsB    .AddNew    .Fields("LoadNO") = ordradd    .Fields("LoadNI") = ordin    .Fields("M") = M    .Fields("D") = D    .Fields("Y") = Y    .Fields("Pr") = ""    .Fields("IO") = "Start"    .Fields("OB") = 0    .Fields("TonO") = 0    .Fields("TonM") = 0    .Fields("TonT") = tonoutadd    .Fields("TonB") = tonbaladd    .UpdateEnd With MsgBox "Record Updated", vbOKOnly, "Complete"

Creating Primary Relation Between Tables ?
Whenever a user replies to a forum post I am going to store it in "Reply" table. And the original post is stored in "News" table. Suppose 10 users replies to a post, all those will be stored in that "Reply" table, whereas only the root post or original post should be stored in "News" table. So I am thinking of creating a relationship between these 2 tables.And those posts are represented in a Listview.
So my intention is to store whenever a user replies to the original post in that "Reply" table. Is this the best way to do? Or is it possible to store the replies in the same table? And whenever a user replies to a post or creates a new post it will be displayed in that Listview in the below diagram format.
Because I though of creating a serial number for all the original posts like....

VB Code:
S.No      Topic                           User   1.        Storing items               gopi              ... Re:Storing items     Tom              ... Re:Storing items     Jack  2.        How to List?                 Lisy              ... Re:How to List?       George

And should the Primary relationship be created with that "ID" that is automatically created when a table is created with a primary relationship or with user defined column for example "SNo"?

Please give me suggestions.And this is not related to this forum or any forums because I am just doing it on my own or rather creating it as a small project for learning vb.Thanks

Copyright © 2005-08, All rights reserved