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

[WORD+EXCEL] Tables Import - Slow Code


I've written this macro to import cells from Excel to a pre-existent Word table. It first checks whether the size of the Word table is big enough for the range extracted from Excel and enlarges it if needed. Then it imports text from the Excel cells into the Word table one by one.

When I'm importing Excel ranges larger than 1000 cells, the importing of text becomes slower and slower. I know that Word is very inefficient with tables, but maybe there's a way to speed things up? I've switched off the screen update feature until the end.

Here's the relevant portion of the code:

Sub ImportTable()

Dim oCurrentXLSRange As Excel.Range
Dim oSourceXLSRange As Excel.Range

'Macro retrieves oSourceXLSRange from Excel...

i = 1
For Each oCurrentXLSRange In oSourceXLSRange.Cells

Selection.Columns(1).Cells(i).Range.Text = _

i = i + 1


End Sub

View Complete Forum Thread with Replies

See Related Forum Messages: Follow the Links Below to View Complete Thread
MS Word Tables Very Slow From VB?
I have written an app in VB6 which creates reports in MS Word 97.

Each resultset record must appear in it's own table.

I tried creating and formatting the tables 'on the fly' from VB as I needed them, however this was horrendously slow even for small resultsets.

I had to resort to creating a word template with a few thousand ready made tables, writing the data to the number of tables required, then deleting the tables I hadn't used. This is quicker than creating the tables through automation but is still slow (not helped by having a 13MB Word file in a Citrix metaframe environment!).

It seems the slow part is writing the data to the table cells (needless to say all the data is converted to String before insert)

Has anyone managed to create similar reports in MS Word?

Any suggestions would be gratefully received.

Import An Excel File And Save In Various Tables In Oracle Using Vb6.0
hi ,
   i would be really greatful to you if you could solve my problem.i have this project in which i have to import an excel file which user has to browse and select .i need to read file,apply few validations and save it in various tables in oracle .i m using Microsoft common Dialog Control 6.0 for allowing user to select the able to trap the path of the file but not able to access it.if possible please post a sample code also.
    please help me as this is very urgent

Edited by - manisha on 9/22/2003 1:28:19 AM

Slow Excel Code - Any Suggestions
Hi All,
I am looping through an excel sheet that has 37,000 rows of two columns and creating a recordset, my problem is it is real slow (7min). I tried using direct ADO (1Min) but had problems with Data types. I was wondering if below can be done disconnected somehow,

Dim obExcelApp As Excel.Application
Dim obWorkBook As Excel.Workbook
Set obExcelApp = CreateObject("Excel.Application")
Set obWorkBook = obExcelApp.Workbooks.Open(eXcelpath)
With obWorkBook.Worksheets(1)
Set r = .UsedRange
For Each Col In r.Rows
rs(0)= .Cells(iLoop, 1).Value
rs(1)= .Cells(iLoop, 2).Value
Next Col
End With
Set obWorkBook = Nothing
Set obExcelApp = Nothing

(this is my first effort with Excel.App object)
any suggestions?


Excel Columns Import To Word
I am trying to copy few columns from excel to word using VB. I am trying to paste the columns inside a table in word but the columns does not shrink. THe columns I am copying from excel is not fitting inside my table. How do I need to fit my excel range into the word table. Here is my code:

With wdApp.Selection.Find
.Text = "[BOX9]"
.Replacement.Text = ""
.Forward = True
.Wrap = wdFindContinue
.Format = False
.MatchCase = False
.MatchWholeWord = False
.MatchWildcards = False
.MatchSoundsLike = False
.MatchAllWordForms = False
End With

Any thoughts??

Excel Code Running SLOW In Access
I am using Access to import a text file. The file is then exported to Excel. Once the file is in Excel, it is processed using some VB code to get it formatted a specific way. I originally wrote the code in Excel alone, and it takes under 2 minutes to process the entire file. Once I implemented Access, the code take 25+ minutes to run. Anyone know why it is running so slow?

I used the same code for both, I just had to add the x1App stuff when in Access.

VB6 Code Running Super Slow With Excel
Hi, I am using a multidimensional array
Dim Array(1 To 8000, 1 To 20) As String
To store data and then send it to an excel workbook with 20 sheets.
I do not know if it because the size of the array or the amount of sheets in the workbook, but my computer runs very slow. It takes about 23 minutes to process this info.
What could be in this code below that will make the computer run slow?

This is part of what I have to interface with excel

Dim oXLApp As Excel.Application 'Declare the object variable
Dim oXLBook As Excel.Workbook
Dim objExcelCI As Excel.Chart
Dim oXLsheet As Excel.Worksheet
Dim Array(1 To 8000, 1 To 20) As String

'Copy Excel file with another name
'Send data from file to Array

' ***********Close Original file***************
Set oXLsheet = Nothing
oXLBook.Close SaveChanges:=False
Set oXLBook = Nothing
Set oXLApp = Nothing

'*******open new workbook with 20 sheets on it*********
Set oXLApp = New Excel.Application 'Create a new instance of Excel
Dim iSheetsPerBook As Integer
iSheetsPerBook = oXLApp.SheetsInNewWorkbook
oXLApp.SheetsInNewWorkbook = 20
Set oXLBook = oXLApp.Workbooks.Add
oXLApp.SheetsInNewWorkbook = iSheetsPerBook

'Send data from Array to opened workbook

'When finished
'*******Show it to the user**********

oXLApp.Visible = True

Thank you!

Macro In Word To Import Excel Graph
Does anybody have any code that works within Microsoft Word to:

Create a button with a macro attached that imports an excel graph into Word without neccesarily having the Excel S/S within which the graph has been created open at the time.

And in the meantime can anyone tell me why the following code is not working?
I get a message "User defined type not defined"

Sub MonthlySalesFigures()

Dim y As Word.Application
Set y = CreateObject("Word.Application")

With y
        .Visible = True
        .Documents.Open Filename:="G:GillsWordExsGreatSouthernRealEstateTemplatesNormally.dotMonthlySalesReport.doc"
        .Selection.GoTo What:=wdGoToBookmark, Name:="AreaMonthlySales"
        .Selection.GoTo What:=wdGoToBookmark, Name:="SalesRepMonthlyCommRet"
        End With
End Sub

Thanks heaps

Header/Footer In Excel File... Slow Code
I have been using the following VBA Code as a macro in my excel files which removes all header/footers from the excel files. However, it seems to be very slow.

Application.Calculation = xlCalculationManual
Application.StatusBar = "Removing Headers/Footers from " & MyFile & " ....(Please Wait)"
WS_Count = ActiveWorkbook.Worksheets.Count
For j = 1 To WS_Count
With ActiveWorkbook.Worksheets(j).PageSetup
.LeftHeader = ""
.CenterHeader = ""
.RightHeader = ""
.LeftFooter = ""
.CenterFooter = ""
.RightFooter = ""
End With
Application.Calculation = xlCalculationAutomatic

Please can someone help me with increasing the speed.

Or can someone help me in adding a code which shows a progress bar so that the user does not get the impression that the system has crashed.

Automating Excel - Code Runs Too Slow In Access
I recently posted this: in the main VBA forum. I thought my follow-up would be more appropriate here.
I've rewritten my code using the appropriate Excel automation procedures but I'm still having trouble with it hanging up at a certain part. Here's the whole thing:

Private Sub cmdImportLog_click()
Dim xlApp As Excel.Application
Dim xlWB As Excel.Workbook
Dim xlWS As Excel.Worksheet
Dim rng As Range

Set xlApp = New Excel.Application
xlApp.Visible = False

If Label6.Caption = "Select a file" Then
MsgBox "Please Select a File"
Exit Sub

xlApp.Workbooks.Open Label6.Caption
xlApp.DisplayAlerts = False

xlApp.Selection.Delete xlShiftUp
xlApp.Selection.Delete xlshiftleft

For Each rng In xlApp.Range("A:A")
If rng.Value <> "" Then
ActiveCell.Characters(100, 2000).Delete
End If
Next rng

With Selection
.MergeCells = False
End With

Selection.Replace what:=".doc", Replacement:=".doc;", LookAt:=xlPart, _
searchorder:=xlByRows, matchcase:=False
Selection.Replace what:=".xls", Replacement:=".xls;", LookAt:=xlPart, _
searchorder:=xlByRows, matchcase:=False
Selection.Replace what:=".pdf", Replacement:=".pdf;", LookAt:=xlPart, _
searchorder:=xlByRows, matchcase:=False
Selection.Replace what:=".ms5", Replacement:=".ms5;", LookAt:=xlPart, _
searchorder:=xlByRows, matchcase:=False
Selection.Replace what:=".log", Replacement:=".log;", LookAt:=xlPart, _
searchorder:=xlByRows, matchcase:=False

Selection.TextToColumns Destination:=Range("A1"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
Semicolon:=True, Comma:=False, Space:=False, Other:=False, FieldInfo _
:=Array(Array(1, 1), Array(2, 1))

xlApp.ActiveWorkbook.SaveAs "c: empalldocs.xls"

DoCmd.TransferSpreadsheet , acSpreadsheetTypeExcel3, "tblDocLog", "c: empalldocs.xls", no, ("A:A")
MsgBox "Import Successful", vbOKOnly

End If

Set xlWS = Nothing
If Not xlWB Is Nothing Then oWB.Close
Set xlWB = Nothing
Set xlApp = Nothing

End Sub

It works but when it gets to this part:

For Each rng In xlApp.Range("A:A")
If rng.Value <> "" Then
ActiveCell.Characters(100, 2000).Delete
End If
Next rng

it hangs up and takes about 3 minutes to finish. When I run this bit by itself in Excel it takes about 15 seconds. Any thoughts on why this might happen, or general suggestions on the sub on a whole would be greatly appreciated.

Thank you!

Import Excel Userform, Sheet2.cls, Workbook.cls To Word?

I found a code for my Word-form.
Its making numbers for invoices. I want to use it! (verry bad)

But when I use the, import and exportmethode, it won't work. I never use Excel VBA. So my question is: Is it possible this way?


ODBC Import To Excel Via VBA Code
I want to get data from another computer through an ODBC connection in VBA code into an Excel spreadsheet. Does anyone know the Provider to use for an ODBC connection in the following code, and do I need an ODBC reference library to use it?

ALSO: I am able to import the data manually through the Excel toolbar now by doing: Data Import External Data Import Data supplying connection name signing onto a connection of format nnn.nnn.nnn.nnn.nnn and specifying where to put the data in my worksheet.

Am I on the right track below to get at this kind of a ODBC connection? Any examples of the Data Source line to use out there?

Dim adoBTC As ADODB.Connection
Dim BTRec As ADODB.Recordset
Set adoBTC = New ADODB.Connection

*** Need this line ***
adoBTC.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" _
& "Data Source=C:AccessMyDB.mdb""
adoBTC.Open ' open the connection
strTableName = "ODBC Table"
End If
Set BTRec = New ADODB.Recordset
BTRec.Open strTableName, adoBTC, adOpenKeyset,
adLockOptimistic, adCmdTable
code to sequentially read & filter the records into an excel worksheet.


Can You Import Modules Through VBA (excel) Code?
If send an excel file to a user which has VBA modules in it, what is the best practice if a module in the workbook the user has needs to change?

Is there a way for me to send them something, either a vb app or another excel file, which will update an excel workbook's modules without changing the data in the workbook.


Import Excel Into SQL Server Using Code
hi There,

Did anyone can teach me how to import data from excel into SQL Server? please also provide me some of the coding.... thanks for your genius brain...hehe

Word Tables In Excel
Regarding my previous post asking how to bring data stored in Word tables into Excel.
I've gone ahead and implemented a strategy to first convert the tables into text and then to read the delimited text.

I'm having trouble specifying a Word table type
My code is as follows:

Private Sub ConvertProSum()
Dim word_app As Object
Dim tableCount As Integer
Dim tableTemp As Word.Table '<----- DOESN'T LIKE THIS
Dim rngTemp As Range
Dim i As Integer

On Error Resume Next

Set word_app = CreateObject("Word.Application") 'instance of MS Word
word_app.Documents.Open _
Filename:=Me.txtProSumPath.Text, _
Format:=wdFormatDocument 'opens Summary file

tableCount = word_app.ActiveDocument.Tables.count 'get # of tables in ProSum

For i = 1 To tableCount
Set tableTemp = word_app.ActiveDocument.Tables(i) 'target 1st, 2nd,... table

' convert to text: field=value pairs
Set rngTemp = tableTemp.ConvertToText(Separator:=wdSeparateByTabs)
Next i

word_app.ActiveDocument.SaveAs _
Filename:="LoadProSum.tmp", _
FileFormat:=wdFormatText 'save converted text as a temp file

word_app.ActiveDocument.Close False 'close Word
End Sub

How do I specify a word Table type in Excel?


Insert Two Tables In Word Document Through Vb Code

Can anybody help me in inserting two tables to a single word document through vb code.

One table contains 4 rows and 6 cols

and another table contains 23 rows and 5 columns

Import Text File In Excel Using VB Code
Hi folks,
My problem is that I am trying to automate a system in which a text file is brought into a worksheet. The code I have so far stops at the "Import Text File" window looking for the "Import" or "Cancel" button to be pressed. Even if I click the "Import" button, then the Import Text Wizard comes up and I have to click "Finish". Does anybody know the commands to avoid having to press these buttons and have the file come into the worksheet without user intervention.

The file name is a variable called FileValue. Here is the code:

SendKeys "^{Home}", True
ChDir "O:BakeryOrdRecapTextFiles"

With Selection.QueryTable
.Connection = "TEXT;O:BakeryOrdRecapTextFiles" & FileValue
.TextFilePlatform = xlWindows
.TextFileStartRow = 1
.TextFileParseType = xlFixedWidth
.TextFileTextQualifier = xlTextQualifierDoubleQuote
.TextFileConsecutiveDelimiter = False
.TextFileTabDelimiter = True
.TextFileSemicolonDelimiter = False
.TextFileCommaDelimiter = False
.TextFileSpaceDelimiter = False
.TextFileColumnDataTypes = Array(2, 2, 2, 9, 9, 9, 1, 9, 1)
.TextFileFixedColumnWidths = Array(11, 10, 32, 5, 8, 28, 15, 13)
.Refresh BackgroundQuery:=True
End With

Import Text File Into Excel Via VB6 Code
I have a problem that is driving me nuts.

I wish to put a text file into the sheet 2 of an Excel spreadsheet as I have info in sheet 1 which will use sheet2 data. How can this be done. All my code is written in VB6 and do not want to use Excel macros. All I want is to import a text file, surely it is not that difficult I think.


How To Import Access 5 To 6 Access Tables Into Vb Using Vb Code.......
help me in accessing MS access tables by vb ....let me kno the steps in detail ........ I ve around 5 to 6 tables created in access and using vb 6.0 as front end now i need to access these tables and display it once the user strikes the command button so please let me know how to do it with code .

Thanks in advance...

Code Similar To EXCEL Text Import Wizard
Hello all,

in my application i need to import a text file, which does not have delimiters.
i need to split the text into columns, (UI similar to Excel text import wizard).

Thanks for any help in advance

Vb Code Similar To Excel Text Import Wizard
Hello all,

I need to split a fixed format string into columns. (UI similar to excel text import wizard) where a user can drag a line to fix the boundaries).

Thanks for any help in advance

Formatting Of Imported Tables From Excel To Word...
I'm importing some data from excel to word. I've noticed that the excel cell formatting is retained once pasted as a table in word. For example if a cell is bold it appears bold in when pasted in word. I would like to clear all this formatting and apply my own. I'm finding it tricky since much of the word formatting is a toggle, and the formatting in Excel is not predictable or required for my purposes. Is there a way I can clear the table formatting and the reapply the styles I want to everything?


RESOLVED Simple Code For Import Data From Excel To Access
Ciao Gurus,

I need a simple VB code that, after the connection with access, allows to update an access table starting from an excel table.
In access, I created a "TransferSpreadsheet" macro where I imported data from excel file and I believe to see the SQL code but it's not possible.



Edited by - kaiala on 1/9/2007 11:11:36 PM

Excel Data To Word As Tables. Removing All Formatting
I'm importing excel ranges into word as tables. What I want to do (in Word) is remove any existing formating from excel so I can apply my own formatting in word. at the moment its messing up as odd bits of formatting keep being copied over from excel. I know I could longwindedly set absolutelty every property of the table in Word but I was wondering is there a neater way of dong it?


Import Data From Mdb Very Very Slow...
I use this code to import fdata from mdb to excel sheet , but is very very slow, is possible to speed,the copy in sheet, other way are welcome, tks.

VB Code:
Global Const gPROVADatabasePath = "\GCD01F4500DATIPUBBLICAPianificazioneNuova CartellaREPORTREPORT.MDB" Sub IMPORTA_H7469()     Dim NOMEDB As String    Dim CONT As String    Dim I As Long    Dim FOUND_ID    Dim CONTA1 As String    Dim CONTA_RISP As String     Application.ScreenUpdating = False     Set ELENCO = Worksheets("H7469")    NOMEDB = gPROVADatabasePath    Dim OggettoConnessione As ADODB.Connection    Dim OggettoRecordset As ADODB.Recordset    Dim StringaDiConnessione As String    StringaDiConnessione = "DRIVER={Microsoft Access Driver (*.mdb)};DBQ=" & NOMEDB    Set OggettoConnessione = New ADODB.Connection    OggettoConnessione.Open StringaDiConnessione    Set OggettoRecordset = New ADODB.Recordset    OggettoRecordset.Open "SELECT * from BANCA_ASS_H7469", OggettoConnessione, adOpenKeyset, adLockOptimistic     CONT = Sheets("H7469").Cells(65536, 1).End(xlUp).Row + 1     CONTA_RISP = 0    CONTA1 = 0    I = 0     Do While Not OggettoRecordset.EOF          ID = OggettoRecordset("PROVA18")        Set FOUND_ID = Sheets("H7469").Columns("R:R").Find(ID, LookAt:=xlWhole)        If Not FOUND_ID Is Nothing Then         Else             ELENCO.Range("A" & CONT).Value = OggettoRecordset("FIL")            ELENCO.Range("B" & CONT).Value = OggettoRecordset("TIP")            ELENCO.Range("C" & CONT).Value = OggettoRecordset("SOC")            ELENCO.Range("D" & CONT).Value = OggettoRecordset("PROD")            ELENCO.Range("E" & CONT).Value = OggettoRecordset("SOTTOSCRITTORE")            ELENCO.Range("F" & CONT).Value = OggettoRecordset("COPE")            ELENCO.Range("G" & CONT).Value = OggettoRecordset("NPROPOSTA")            ELENCO.Range("H" & CONT).Value = OggettoRecordset("DA")            ELENCO.Range("I" & CONT).Value = OggettoRecordset("IMPORTO")            ELENCO.Range("J" & CONT).Value = OggettoRecordset("RET")            ELENCO.Range("K" & CONT).Value = OggettoRecordset("DP")            ELENCO.Range("L" & CONT).Value = OggettoRecordset("STATO")            ELENCO.Range("M" & CONT).Value = OggettoRecordset("TIPO")            ELENCO.Range("N" & CONT).Value = OggettoRecordset("DAL")            ELENCO.Range("O" & CONT).Value = OggettoRecordset("AL")            ELENCO.Range("P" & CONT).Value = OggettoRecordset("TIP1")            ELENCO.Range("Q" & CONT).Value = OggettoRecordset("PROD1")             ELENCO.Range("R" & CONT).Value = OggettoRecordset("PROVA18")             CONTA1 = CONTA1 + 1            CONT = CONT + 1         End If         CONTA_RISP = CONTA_RISP + 1         DoEvents         CARICA_DATI_.TextBox152.Value = CONTA_RISP        CARICA_DATI_.TextBox155.Value = CONTA1         OggettoRecordset.MoveNext         I = I + 1         CARICA_DATI_.ProgressBar1.Value = (I / OggettoRecordset.RecordCount) * 100     Loop     OggettoRecordset.Close    OggettoRecordset.Open "SELECT Count(FIL) As Cnt FROM BANCA_ASS_H7469", StringaDiConnessione, adOpenKeyset, adLockOptimistic, adCmdText    Sheets("H7469").Range("D1") = OggettoRecordset!cnt     OggettoRecordset.Close    Set OggettoRecordset = Nothing    OggettoConnessione.Close    Set OggettoConnessione = Nothing     ActiveWorkbook.Save     Unload CARICA_DATI_     Application.ScreenUpdating = True  End Sub

I use this code to import fdata from mdb to excel sheet , but is very very slow, is possible to speed,the copy in sheet, other way are welcome, tks.

Global Const gPROVADatabasePath = "\GCD01F4500DATIPUBBLICAPianificazioneNuova CartellaREPORTREPORT.MDB"

Sub IMPORTA_H7469()

    Dim NOMEDB As String
    Dim CONT As String
    Dim I As Long
    Dim FOUND_ID
    Dim CONTA1 As String
    Dim CONTA_RISP As String

    Application.ScreenUpdating = False

    Set ELENCO = Worksheets("H7469")
    NOMEDB = gPROVADatabasePath
    Dim OggettoConnessione As ADODB.Connection
    Dim OggettoRecordset As ADODB.Recordset
    Dim StringaDiConnessione As String
    StringaDiConnessione = "DRIVER={Microsoft Access Driver (*.mdb)};DBQ=" & NOMEDB
    Set OggettoConnessione = New ADODB.Connection
    OggettoConnessione.Open StringaDiConnessione
    Set OggettoRecordset = New ADODB.Recordset
    OggettoRecordset.Open "SELECT * from BANCA_ASS_H7469", OggettoConnessione, adOpenKeyset, adLockOptimistic

    CONT = Sheets("H7469").Cells(65536, 1).End(xlUp).Row + 1

    CONTA_RISP = 0
    CONTA1 = 0
    I = 0

    Do While Not OggettoRecordset.EOF

        ID = OggettoRecordset("PROVA18")
        Set FOUND_ID = Sheets("H7469").Columns("R:R").Find(ID, LookAt:=xlWhole)
        If Not FOUND_ID Is Nothing Then


            ELENCO.Range("A" & CONT).Value = OggettoRecordset("FIL")
            ELENCO.Range("B" & CONT).Value = OggettoRecordset("TIP")
            ELENCO.Range("C" & CONT).Value = OggettoRecordset("SOC")
            ELENCO.Range("D" & CONT).Value = OggettoRecordset("PROD")
            ELENCO.Range("E" & CONT).Value = OggettoRecordset("SOTTOSCRITTORE")
            ELENCO.Range("F" & CONT).Value = OggettoRecordset("COPE")
            ELENCO.Range("G" & CONT).Value = OggettoRecordset("NPROPOSTA")
            ELENCO.Range("H" & CONT).Value = OggettoRecordset("DA")
            ELENCO.Range("I" & CONT).Value = OggettoRecordset("IMPORTO")
            ELENCO.Range("J" & CONT).Value = OggettoRecordset("RET")
            ELENCO.Range("K" & CONT).Value = OggettoRecordset("DP")
            ELENCO.Range("L" & CONT).Value = OggettoRecordset("STATO")
            ELENCO.Range("M" & CONT).Value = OggettoRecordset("TIPO")
            ELENCO.Range("N" & CONT).Value = OggettoRecordset("DAL")
            ELENCO.Range("O" & CONT).Value = OggettoRecordset("AL")
            ELENCO.Range("P" & CONT).Value = OggettoRecordset("TIP1")
            ELENCO.Range("Q" & CONT).Value = OggettoRecordset("PROD1")

            ELENCO.Range("R" & CONT).Value = OggettoRecordset("PROVA18")

            CONTA1 = CONTA1 + 1
            CONT = CONT + 1

        End If



        CARICA_DATI_.TextBox152.Value = CONTA_RISP
        CARICA_DATI_.TextBox155.Value = CONTA1


        I = I + 1

        CARICA_DATI_.ProgressBar1.Value = (I / OggettoRecordset.RecordCount) * 100


    OggettoRecordset.Open "SELECT Count(FIL) As Cnt FROM BANCA_ASS_H7469", StringaDiConnessione, adOpenKeyset, adLockOptimistic, adCmdText
    Sheets("H7469").Range("D1") = OggettoRecordset!cnt

    Set OggettoRecordset = Nothing
    Set OggettoConnessione = Nothing


    Unload CARICA_DATI_

    Application.ScreenUpdating = True

End Sub

Accessing DBase Tables (.DBF) = VERY SLOW
Can anyone tell me how to speed things up in VB reading a FoxPro table .DBF.
This is the code I have but it takes forever to run!!!!!
Note: My .DBF file is not in a container and is very large!

'Public declaration are in a module!
Public g_objTandemConn As ADODB.Connection
Public g_objRS As ADODB.Recordset
Dim lcSQL As String
'Create SQL filter string
"WHERE TRIM(WEAVINSP.PIECE_NUM) = '" + Trim(txtNewPieceN.Text) + "'"
'Set a reference to the ADO recordset object
Set g_objRS = New ADODB.Recordset
'Open the recordset object
g_objRS.Open lcSQL, g_objFoxProConn, adOpenForwardOnly, _
adLockReadOnly, adCmdText

Thanks in advance


Slow Access To FoxPro Tables
I have used the following code to read foxpro tables.

        Set myADOCon = New adodb.Connection
        myADOCon.ConnectionString = "Driver={Microsoft FoxPro VFP Driver (*.dbf)};UID=;PWD=;SourceDB=" & VersaProHomeDir & ";SourceType=DBF;Exclusive=No;BackgroundFetch=Yes;Collate=Machine;Null=Yes;Deleted=Yes;"
        Set myADORS = myADOCon.Execute("SELECT JENUMBER FROM docgen WHERE Companyid = '" & VersaProCompanyID & "'")

The problem I have is that it takes about 8 seconds on my Pent3, 1Ghz machine to do the .Open, line of code.
Is anyone else doing this faster?


Import Tables
How I can import tables to an Access 2000 database using Visual Basic?

Tables Not Seen In Import
Hello All

I have a problem. Looks like I am making some mistake.

I am working on converting a old DOS based system to windows based system.

When I do the backup from the old system.I have files of this type

SQL Script file

I have to get the data from the old system backup into SQL SERVER 2000. The backup is in 2 floppies.
I have tried using the DTS to import the DBF visual fox pro tables through Enterprise Manager. When I come to the last part of copying tables it shows no tables from the data source the space is empty.

I am sure I am going wrong some where. Please guide me.

Thanks In Advance.

Thanks & Regards

Import Tables From A Mdb To Other
i need to import all tables present into one mdb from another mdb using VB code can any one help me

Import Tables Between MDB Using VB
I need to write code to import tables from one mdb to another can any help regarding this

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?

Word 2003 SLOW Versus Word 2002/XP
We have a small application that refreshes a whole load of data in a word document with fresh data out of the database.

On our dev machines, in Word 2002/XP this takes under a minute. In 2003 it takes about 2 mins. For standard users on our LAN it takes up to 7 minutes in 2003. I am assuming that for the standard users, if I can get it running as fast in 2003 as it does in 2002 that would mean it should be around a couple of minutes.

The program simply selects text, deletes it, then pastes in the new information. I'm assuming there is a setting or something in 2003 that needs to be changed, or a different way of doing it.

The program normally connects to Word by referencing the olb file, but I have tried doing it via late binding with no difference.

Has anyone else ever heard of or experienced this problem?

Any help greatly appreciated.

Can't really post the code, but I can post a sample of *how* it does it if need be....


Renaming Tables During Import

I have created an import specification in Access to automatically import an Excel spreadsheet once a week. The users just have to click a button to import the file and it changes the datatypes, etc.

How can I get the users to be prompted to name the table? I'm using the transfertext method call. I have tried using the Rename method after the transfertext, but I need to supply a new name for the table, i.e., hard-code it in.

Is there any way I can have some user interaction at this point? Otherwise, the file will be saved to the same table each week over-writing the previous weeks data.



How To Import Txt To Access Tables

i want to import a txt - File to a access tabel.

the shape of the file is:

"PFSTAMM" = type (there are different typs)
"FONDART..." = table name
ENDE = end

the constitution__________________________________

;AI Fonds
;AI Genussscheine
;AI Notes
;Aktien Blue Chips
;Aktien Mid-/Small-Caps
;Aktien Neue Märkte
;Aktien Wachstumsmärkte
;Anl. High Yields
;Anl. Langläufer
;Anl. Sonstiges
;Anl. Staatsanleihen
;Anl. Unternehmen
;Anl. Wandelanleihen
;Anl. gemischt
;Anl. kurz-/mittelfristig
;Automobil & Zubehör
;Banken u. Versicherungen
;Blue Chips

and so on

how i can do that


Which Is Better - Linked Tables Or Import Module?
I have several Excel files that are kicked off by Windows Scheduler off and on all day - these files grab other text files and parse and sort out the data. I want to use that data from the Excel files in a central Access database. Which is more efficient - linking the tables to the database and refreshing them as needed or importing the data into Access as needed? What will happen if the Excel file is doing a new data grab at the time I refresh a link to it or try to import data from it? Will Access come back empty handed? I am limited to VBA only. Thanks for any insight!

Import Tables With New Field Structure...
Hi Guys,

I have a database that my app uses to store records and my friend has given me a database with his own database records to add..

The data contained in his database is similar to mine but his field names are different as he manually made his database and adds the records directly into access and I would like to get the data into my database without having to manually add them all.

Is there a way I can do this and reorganise the new data coming in and put it in the fields in my database where I want?

There are a couple of complications if this is possible that I can see and would like to know if I can do this and if someone can help or advise?

Im in VB6 using acces database and the 2 tables cane be in the same database or seperate.

Thanks in advance for any thoughts.

How To Import SQL Server Tables To MS Access(DTS)

          How to Import SQL server Tables to MS Access(DTS) through Coding in Visual Basic

Import And Export Access Tables (SOLVED)
Can anyone tell me how to solve this with VB and MS access ?:
Step 1 : Let the user choose the databse file(.mdb)- already done
step2 : Search in the chosen database file some names of tables with criteria .(not done)
step 3: Create an acsess database file and import those tablesfrom step 2 into the new file.

Please tell me what to do or tell me the way to do it . Which object do i have to use ?

Thanks for reading.

How To Import HTML Files Contain Tables Into CSV Format ?
How to import HTML files contain tables into CSV format ?
I've got some HTML files contain with tables , then i'd like to import them into CSV format but not success .. Thanks much for your help Guys ..

Auto Import Tables From ODBC Connection
I want to be able to automatically import all tables that relate to an ODBC connection. I deally this would be done on click of a button on an access form.

Is this possible ?

Using BCP Utility How To Import/Export Tables From Sql Server?

I want to to export/import my sql server tables to text format using bcp can go about doing it? where can i get source code from ??

Please help!


Excel And Word Code Integration
Hi All,

the title might be a bit misleading but, i am looking for a way to do this within VBA:

1. once i click a button on an excel spreadsheet, a specific word document opens (Like C:DocsAdi.rtf).
2. i need to copy everything in that document to the excel spreadsheet so select all and copy.
3. close the word document.
4. paste the information into the excel spreadsheet.

i know the answers for 2 and 4 are:


4. activesheet.paste

i'm just having problems filling in the rest.

Please help.

Excel Pagesetup Slow On Fast Machine, Fast On Slow One - Why?
I have some code that produces diagram in Excel adds them as new forms and in the end sets up the page for printing. I need to run this on two machines. Both NT4 SP6, Excel2000, Laser Jet 2100TN.
While on one machine(actually the one with the slower CPU and less RAM) the code executes with lightning speed (few secs for 100 diagrams) on the other machine it will take 10 - 20 seconds PER DIAGRAM. The problem lies with the page setup code where each line takes ages to execute. (example: chart.pagesetup..TopMargin = Application.CentimetersToPoints(5) 'will take 2 secs)

Obviously there must be some setting responsible for this. My problem is I seem too blind to find it.

Does anyone know what is causing this behaviour and how to control it? Guesses welcome!


Read Value From Excel (hidden) To Word Vba Code
I am trying to write a VBA set of code (in a Word document) that will read values from an Excel file cell value and use that value in my code...

Below is a really scrappy version of my code...

I can get the code to read values but can't get it to read them.

Public Function AddExcelValue(Value As String, Column As Integer, Row As Integer, _
wkshtName As String, wkbkName As String, fileLoc As String) As Boolean

Dim fileExists As Boolean
Dim myWB As Excel.Workbook
Dim myWorksheet As Worksheet

fileExists = CheckFileExists(fileLoc) ' uses a separate Sub not here...

If fileExists = True Then
Set myWB = GetObject(fileLoc)
Set myWorksheet = myWB.ActiveSheet
myWorksheet.Cells(Column, Row).Value = Value
End If
Set myWB = Nothing

AddExcelValue = True
End Function

Opening Excel File Using VBA Word Code
Hi all,
I am new to VBA.
I need to open an Excel document when i click menu item which i have created for my
I am working on Word VBA, so i need to write the code in Word VBA code window.

Please reply me aeap.

With Regards,

Slow Word VBA
We have some Microsoft Word templates that contain many checkboxes. The
documents created by these templates open very slowly. I have found that
deleting the checkboxes eliminates the slowness, and adding more
proportionally increases the time it takes to open the document.

Does anyone know a more efficient way to add check boxes to a word document?

Also, does anyone know of a way to eliminate the reference to a template?
It seems that all of our template created documents are pulling all of their
code from the templates modules. I would rather have a template that
creates a document with the code imbedded in the document instead of the

Any suggestions will be greatly appreciated.

John Sorbel

Write To Word From VB (to Slow)

I am reading a recordset to word.
The problem i'm having is that it takes to much time.
Each row takes about 1 second to print.
(and i have about 300 rows)

I am reading it into a table with pre defined cells.
I have tried to skip the recordset and just
read other values to word but it is the same
slow performance.

I have also tried to disconnect the virus program
but that didn't help anything.

Anyone with an ide ????

Thanks in advance


Word PrintOut Command Very Slow
Hi. I am printing from a Word doc, with .PrintOut. I am using this code:

With wApp
'Hide Word from user
.Application.Visible = False
'Open a new document from template
.Documents.Add App.Path & "", False, , True
Set wDoc = .ActiveDocument
'Put values in document
wApp.Selection.TypeText AC
wApp.Selection.TypeText IBAN
wDoc.PrintOut '(True) 'Print doc
'Do While wApp.BackgroundPrintingStatus <> 0
' DoEvents
End With
The thing is, the PrintOut command is very slow. I tried background printing too, and the loop (see comments) but still it takes too long. Can this be fixed, or is there a way around this? Thanks!

Copyright 2005-08, All rights reserved