473,385 Members | 1,861 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,385 software developers and data experts.

Access Excel Automation Formatting Problem

Hi there
Have a database, where front-end interface allows user to select a ms
access database. From there, standard tables are linked. Routine,
that creates a spreadsheet, for each table a separate sheet within
spreadsheet is created. Particular fields are selected.

User requires fields to be auto-fitted. Problem, is that some tables
have more than 1200> rows. Code generates error message. Is there a
way of getting around this problem. See ***

Code -->

Public Function Export_Excel_10(dblocation As Variant)
On Error GoTo Err_Export_Excel_10

'***AIM-Testing
'CALL Export_Excel_10("T:\TechCentral\techCENTRAL\StdSpe cs\WWL\A0000.MDB")

Dim x1 As Excel.Application
Dim excelwbkXL As Object
Dim excelwksXL As Object
Dim counter As Integer, row As Integer
Dim strSQL1 As String, strSQL4 As String
Dim strSQL4A As String, strSQL4B As String, strSQL4C As String,
strSQL4D As String
Dim strSQL4E As String, strSQL4F As String, strSQL4G As String,
strSQL4H As String
Dim strPath_Security_WkGrp As String, strPath_Security_User As String
Dim strPath_Security_Pwd As String, strPath As String
Dim cnt As New ADODB.Connection, rst As New ADODB.Recordset
Dim D As DAO.Database, R As DAO.Recordset, s As String
Dim I As Integer
Dim strTable As Integer
Dim posLONG_DESCR As Integer, posMAIN_SIZE As Integer, posRUN_SIZE As
Integer, posBRAN_SIZE As Integer
Dim posSCHEDULE As Integer, posRATING As Integer, posSHORT_DESC As
Integer, posCATALOG As Integer

'***AIM-Column Position of fields
posLONG_DESCR = 1
posMAIN_SIZE = 2
posRUN_SIZE = 3
posBRAN_SIZE = 4
posSCHEDULE = 5
posRATING = 6
posSHORT_DESC = 7
posCATALOG = 8

'***AIM-Default Settings
dblocation = "C:\WWL.PipeSpec\book1.xls"
strPath = "C:\WWL.PipeSpec\PipeSpec.mdb"
strPath_Security_WkGrp = "C:\WWL.PipeSpec\WorkGroup\wwl_sys1.mda"
strPath_Security_User = ""
strPath_Security_Pwd = ""
strTable = 0

'***AIM-Open the Excel spreadsheet.
Set x1 = CreateObject("Excel.application")
Set excelwbkXL = x1.Workbooks.Open(dblocation)

'***AIM-Display Excel and give user control of Excel
x1.Visible = True
x1.UserControl = True

'***AIM-Set the string to the path of the Working database
Set cnt = New ADODB.Connection
With cnt
.Provider = "Microsoft.Jet.OLEDB.4.0"
.CursorLocation = adUseClient
'.Properties("Jet OLEDB:Database Password") = "" --> password
.Properties("Jet OLEDB:System Database") =
strPath_Security_WkGrp
.Open strPath, strPath_Security_User, strPath_Security_Pwd
End With

s = "SELECT Table_Name FROM STANDARD_TABLES"
Set D = CurrentDb
Set R = D.OpenRecordset(s)
'***AIM-LOOP THROUGH RECORDS IN PathTextTech
Do Until R.EOF

If R!table_name = "Pass" Or R!table_name = "Timeout" Then
'do nothing
ElseIf R!table_name <> "pumpa" Then
strSQL4A = "SELECT [LONG_DESCR], [MAIN_SIZE], [RUN_SIZE],
[BRAN_SIZE], [SCHEDULE], [RATING], [SHORT_DESC], [CATALOG] from " &
R!table_name
strSQL4 = strSQL4A & " ORDER BY [LONG_DESCR], [MAIN_SIZE],
[RUN_SIZE], [BRAN_SIZE], [SCHEDULE], [RATING], [SHORT_DESC], [CATALOG]
" & ";"

ElseIf R!table_name = "pumpa" Then
strSQL4A = "SELECT [LONG_DESCR], [LONG_DESCR], [CATALOG]
from " & R!table_name
strSQL4 = strSQL4A & " ORDER BY [LONG_DESCR], [CATALOG] "
& ";"
End If

strTable = strTable + 1

Set rst = New ADODB.Recordset
rst.Open strSQL4, cnt

'***AIM-Add new sheet
Set excelwksXL = excelwbkXL.Worksheets.Add

'***AIM-Spreadsheet - sheet name
excelwksXL.NAME = R!table_name

If R!table_name <> "pumpa" Then

'***AIM-Make the column headers.
For I = 1 To rst.Fields.Count - 1

'***AIM-Fields
If rst.Fields(I).NAME = "LONG_DESCR" Then
excelwksXL.Cells(1, posLONG_DESCR).Value =
rst.Fields(I).NAME
ElseIf rst.Fields(I).NAME = "MAIN_SIZE" Then
excelwksXL.Cells(1, posMAIN_SIZE).Value =
rst.Fields(I).NAME
ElseIf rst.Fields(I).NAME = "RUN_SIZE" Then
excelwksXL.Cells(1, posRUN_SIZE).Value =
rst.Fields(I).NAME
ElseIf rst.Fields(I).NAME = "BRAN_SIZE" Then
excelwksXL.Cells(1, posBRAN_SIZE).Value =
rst.Fields(I).NAME
ElseIf rst.Fields(I).NAME = "SCHEDULE" Then
excelwksXL.Cells(1, posSCHEDULE).Value =
rst.Fields(I).NAME
ElseIf rst.Fields(I).NAME = "RATING" Then
excelwksXL.Cells(1, posRATING).Value =
rst.Fields(I).NAME
ElseIf rst.Fields(I).NAME = "SHORT_DESC" Then
excelwksXL.Cells(1, posSHORT_DESC).Value =
rst.Fields(I).NAME
ElseIf rst.Fields(I).NAME = "CATALOG" Then
excelwksXL.Cells(1, posCATALOG).Value =
rst.Fields(I).NAME
End If

Next I

'***AIM-Get data from the database and insert
'***AIM-it into the spreadsheet.
row = 2
Do While Not rst.EOF
For I = 1 To rst.Fields.Count - 1
'***AIM-Fields
If rst.Fields(I).NAME = "LONG_DESCR" Then
excelwksXL.Cells(row, posLONG_DESCR) =
rst.Fields(I).Value
ElseIf rst.Fields(I).NAME = "MAIN_SIZE" Then
excelwksXL.Cells(row, posMAIN_SIZE) =
rst.Fields(I).Value
ElseIf rst.Fields(I).NAME = "RUN_SIZE" Then
excelwksXL.Cells(row, posRUN_SIZE) =
rst.Fields(I).Value
ElseIf rst.Fields(I).NAME = "BRAN_SIZE" Then
excelwksXL.Cells(row, posBRAN_SIZE) =
rst.Fields(I).Value
ElseIf rst.Fields(I).NAME = "SCHEDULE" Then
excelwksXL.Cells(row, posSCHEDULE) =
rst.Fields(I).Value
ElseIf rst.Fields(I).NAME = "RATING" Then
excelwksXL.Cells(row, posRATING) =
rst.Fields(I).Value
ElseIf rst.Fields(I).NAME = "SHORT_DESC" Then
excelwksXL.Cells(row, posSHORT_DESC) =
rst.Fields(I).Value
ElseIf rst.Fields(I).NAME = "CATALOG" Then
excelwksXL.Cells(row, posCATALOG) =
rst.Fields(I).Value
End If

Next I
row = row + 1
rst.MoveNext
Loop

ElseIf R!table_name = "pumpa" Then

'***AIM-Make the column headers.
For I = 1 To rst.Fields.Count - 1

'***AIM-Fields
If rst.Fields(I).NAME = "LONG_DESCR" Then
excelwksXL.Cells(1, 2).Value = rst.Fields(I).NAME
ElseIf rst.Fields(I).NAME = "CATALOG" Then
excelwksXL.Cells(1, 3).Value = rst.Fields(I).NAME
End If

Next I

'***AIM-Get data from the database and insert
'***AIM-it into the spreadsheet.
row = 2
Do While Not rst.EOF
For I = 1 To rst.Fields.Count - 1
'***AIM-Fields
If rst.Fields(I).NAME = "LONG_DESCR" Then
excelwksXL.Cells(row, 2) = rst.Fields(I).Value
ElseIf rst.Fields(I).NAME = "CATALOG" Then
excelwksXL.Cells(row, 3) = rst.Fields(I).Value
End If

Next I
row = row + 1
rst.MoveNext
Loop

End If
************************************************** *****************
'***AIM-Need to disable, can't handle large number of rows
(2000>)
'***AIM-Formatting
'excelwksXL.range(excelwksXL.Cells(1, 1), _
'excelwksXL.Cells(1, row)).Select
'x1.Selection.EntireColumn.AutoFit
'x1.Selection.Columns.AutoFit
'excelwksXL.range(excelwksXL.Cells(1, 1), excelwksXL.Cells(2,
row)).Select
'excelwksXL.range(excelwksXL.Cells(1, 1), excelwksXL.Cells(8,
200)).Select
'x1.Selection.EntireColumn.AutoFit
'***AIM-Print Setup properties
'x1.ActiveSheet.PageSetup.Zoom = 70
'x1.ActiveSheet.PageSetup.Orientation = xlLandscape
'x1.ActiveSheet.PageSetup.PrintArea = "$A$1:" & "$H" & "$" &
row
'x1.ActiveSheet.PageSetup.PaperSize = xlPaperA4
'x1.ActiveSheet.PageSetup.Sort = column1

************************************************** **

R.MoveNext
Loop
R.Close
D.Close

excelwksXL.range(excelwksXL.Cells(1, 1), _
excelwksXL.Cells(1, row)).Select
x1.Selection.EntireColumn.AutoFit
Set excelwbkXL = Nothing
Set excelwksXL = Nothing

'***AIM-End Message
msgbox "Transfered over " & strTable & " tables out of 31 in
total.", vbInformation

Exit_Export_Excel_10:
Exit Function

Err_Export_Excel_10:
msgbox ERR.Description
Resume Exit_Export_Excel_10

End Function
Nov 13 '05 #1
0 2906

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

5
by: Guy Incognito | last post by:
Hello, I've written an asp.net application that creates Excel documents. It works by creating an excel document in XML format. But I wonder if I'm reinventing the wheel. I know that there are...
3
by: Otie | last post by:
I am trying to copy the cell contents in an MSFLXGRD control (using VB5) into Excel, retaining the foreground colors of the text and numbers. I have tried using the Clipboard.SetText...
12
by: Cheval | last post by:
Has anyone had any problems with inter-office automation between MS Word and MS Access in Office 2003? I have recently installed office 2003 in a new folder and have left the older office 2000...
8
by: Colleyville Alan | last post by:
I have been working on an Access app that takes info from a file and writes it to a spreadsheet on a form, simultaneously saving the spreadsheet to Excel. I got the idea that the same concept...
11
by: Mr. Smith | last post by:
Hello all, My code can successfully open, write to, format and save several worksheets in a workbook then save it by a given name, close and quit excel. My problem is that if I try and do it...
8
by: Mike MacSween | last post by:
tblCourses one to many to tblEvents. A course may have an intro workshop (a type of event), a mid course workshop, a final exam. Or any combination. Or something different in the future. At...
17
by: Mansi | last post by:
I need to do some research on how to use excel automation from c#. Does anyone know of any good books related to this subject? Thanks. Mansi
12
by: D. Shane Fowlkes | last post by:
This most likely belongs in another forum but I thought I'd start here. I have a COM Object written in VB6. The DLL will access MS Excel and use it's Object Library to write a customized report...
3
by: Jennyfer Barco | last post by:
Hello, I have a question, how can I open Microsoft Excel from .NET. I only need to open a new file in Excel and paste some information and set the Microsoft Excel as the enabled aplication, so the...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
by: ryjfgjl | last post by:
In our work, we often need to import Excel data into databases (such as MySQL, SQL Server, Oracle) for data analysis and processing. Usually, we use database tools like Navicat or the Excel import...
0
by: ryjfgjl | last post by:
If we have dozens or hundreds of excel to import into the database, if we use the excel import function provided by database editors such as navicat, it will be extremely tedious and time-consuming...
0
by: emmanuelkatto | last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud. Please let me know. Thanks! Emmanuel
0
BarryA
by: BarryA | last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
1
by: nemocccc | last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
1
by: Sonnysonu | last post by:
This is the data of csv file 1 2 3 1 2 3 1 2 3 1 2 3 2 3 2 3 3 the lengths should be different i have to store the data by column-wise with in the specific length. suppose the i have to...
0
by: Hystou | last post by:
There are some requirements for setting up RAID: 1. The motherboard and BIOS support RAID configuration. 2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.