Advertisement

04.21.2008 at 10:16AM PDT, ID: 23340276
[x]
Attachment Details

Attempting to FORMAT Excel Spreadsheet within VBA Export Code, references???

Asked by Trey31 in Access Coding/Macros, Microsoft Office Suite, Microsoft Excel Spreadsheet Software

Tags: Microsoft, Access, 2003, EXPORT Query and FORMAT in VB Code

Greetings to all experts,
I have an issue that I would love to resolve and move on with. The main goal is to format an excel spreadsheet within the export code. I have a formatted sheet I can upload for vb translation or:
This is what I would like to write in code:
> 85% view mode
>Freeze the header row
>Only include borders where there is information
>Text in RED
>Font - Tahoma 8
> Preset column widths....

I have been using the docmd.transferspreadsheet method to export because of the amount of text in my memo box and to escape truncation. I would like to be able to export my query as text and get rid of those unsightly "Shift+Enter" box characters that appear. Here is my code so far:
Start Free Trial 
1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:
17:
18:
19:
20:
21:
22:
23:
24:
25:
26:
27:
28:
29:
30:
31:
32:
33:
34:

Private Sub FileOutput_Click()
 On Error GoTo Err_FileOutput_Click
 
   Dim sDestDir As String
   Dim sSourceFile As String
    
    sDestDir = "C:\Foldername\"
    sSourceFile = "\\Servername\root\....'Templatelocation'.xls"
   
    stDocName = "Report"
    DoCmd.SetWarnings False
 
    DoCmd.OpenQuery stDocName, acNormal
    
    'Make dest dir if required
    If Dir(sDestDir, vbDirectory) = "" Then
        MkDir sDestDir
    End If
    
    DoCmd.TransferSpreadsheet acExport, "Report", "\\Servername\root\....'Templatelocation'.xls", True, ""
 
    DoCmd.Close acQuery, "Report"    
    FileCopy "\\Servername\root\....'Templatelocation'.xls", "C:\Foldername\" & "Report" & Format(Now, "mmmyy") & ".xls"    
    
    DoCmd.SetWarnings False
    Shell "explorer.exe C:\Foldername\", vbNormalFocus
 
    DoCmd.SetWarnings False
    
    Err_FileOutput_Click:
    MsgBox Err.Description
    Resume Exit_FileOutput_Click
    
    End Sub
[+][-]04.22.2008 at 04:51AM PDT, ID: 21409969

View this solution now by starting your 7-day free trial. Setting up your free trial is quick, easy, and secure. We will return you to this solution, unlocked, when you're done.

 

About this solution

Zones: Access Coding/Macros, Microsoft Office Suite, Microsoft Excel Spreadsheet Software
Tags: Microsoft, Access, 2003, EXPORT Query and FORMAT in VB Code
Sign Up Now!
Solution Provided By: frankytee
Participating Experts: 1
Solution Grade: A
 
 
[+][-]04.22.2008 at 04:53AM PDT, ID: 21409989

At Experts Exchange, members can ask their questions to thousands of technology professionals, also known as Experts. Experts compete and collaborate to answer those questions by leaving comments like this one.

Start your 7-day free trial to view this Expert Comment or ask the Experts your question.

 
[+][-]04.23.2008 at 01:04PM PDT, ID: 21424763

Often, when Experts are collaborating with members who have asked questions, they will request additional information about the problem. Askers respond with an author comment like this one.

Start your 7-day free trial to view this Author Comment or ask the Experts your question.

 
[+][-]06.04.2008 at 04:40PM PDT, ID: 21715251

Experts Exchange has a courteous staff of administrators who help members get the most out of the website by means of administrative comments like this one.

Start your 7-day free trial to view this Administrative Comment or ask the Experts your question.

 
[+][-]06.08.2008 at 07:45PM PDT, ID: 21740777

Experts Exchange has a courteous staff of administrators who help members get the most out of the website by means of administrative comments like this one.

Start your 7-day free trial to view this Administrative Comment or ask the Experts your question.

 
 
Loading Advertisement...
20080716-EE-VQP-32 / EE_QW_2_20070628