Code to Export to excell and Format Cells (1 Viewer)

[tucker61]

My code exports a report to excel, and then formats some cells, The cells in column D need to be formatted as a number with 6 characters. When I run the query it looks fine, but when this is exported it is exported as text.

My code is enclosed. I have tried NumberFormat in the code but they don't seem to work What am I doing wrong ?

Sub FormatandQuitExcel(ExcelFile As String, LastCell As Integer, FreezeColumn As Integer)
On Error GoTo Handler
Dim xlApp As Object
Dim wb As Object
Dim ws As Object
Dim i As Integer
    Set xlApp = CreateObject("Excel.Application")
    xlApp.Visible = True
    xlApp.DisplayAlerts = False
    Set wb = xlApp.Workbooks.Open(ExcelFile)
    Set ws = wb.Sheets(1)
    'wb.ActiveWindow.WindowState = xlMaximized
    ws.cells.Font.Name = "Calibri"
    ws.cells.Font.Size = 9
    ws.EnableAutoFilter = False
    'ws.Cells.AutoFilter (1)
    ws.Rows(1).EntireRow.insert
    ws.Rows(2).Font.Bold = True
    ws.Columns.AutoFit
    
    For i = 1 To LastCell
        ws.cells(2, i).Value = StrConv(ws.cells(2, i).Value, vbProperCase)
        ws.cells(2, i).WrapText = True
    Next i
    DoEvents
    
    ws.cells(1, 1).Value = "Version"
    ws.cells(1, 2).Value = "1"
    ws.cells(2, 24).Clear
    ws.cells(2, 25).Clear
    ws.cells(2, 26).Clear
    ws.cells(2, 27).Clear
    ws.cells(2, 28).Clear
    ws.cells(2, 29).Clear
    ws.cells(2, 30).Clear
    ws.cells(2, 31).Clear
    
    ws.Columns(4).select ' Column is selected correctly here. Need this to be formatted as a number to 6 places ie 000000.
    
    wb.Save
    xlApp.Quit
Exit Sub
Handler:
    MsgBox "Error " & Err.Number & ": " & Err.Description, vbExclamation, ""
    Resume Next
End Sub

 

[pbaldy]

A little trick I use is to go into Excel and record a macro doing what I need to do, then copying the resulting VBA into my code. It will need to be adapted to use your ws variable.

 

[tucker61]

Tried that, but struggled to convert with either the RANGE or SELECTION commands . Might try it again over next few days when I have more time.

Sent from my SM-T715 using Tapatalk

 

[pbaldy]

Okay, post back with the code if you're still stuck.

 

[tucker61]

OK, i have had another look at this and i am still having issues.
I think this is to do with the column is formatted as text, so i need to convert to numbers where possible and then format these as "000000".
So to convert from text to numbers, I copied a blank cell, and then added this cell to column "H", which seemed to work OK in excel.

It is only when i insert the code into access i have issues.

I copied the code below from a excel macro.

    Range("H1").Select
    Selection.Copy
    Range(Selection, Selection.End(xlDown)).Select
    Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlAdd, SkipBlanks:= _
        False, Transpose:=False
    Application.CutCopyMode = False
    Selection.NumberFormat = "000000"
    Range("H1").Select

I have changed this slightly to fit into the bottom of my original code to the below.

    ws.cells(1, 4).Copy
    ws.Columns(4).Select ' Column is selected correctly.
    'Range(Selection, Selection.End(xlDown)).Select
    ws.Columns(4).PasteSpecial Paste:=xlPasteAll, Operation:=xlAdd, SkipBlanks:= _
        True, Transpose:=False
   ' Application.CutCopyMode = False
    Selection.NumberFormat = "000000"

I have had to disable the Application.cutcopyMode = false as this does not work in Access.

when running now the debug window opens at

    ws.Columns(4).PasteSpecial Paste:=xlPasteAll, Operation:=xlAdd, SkipBlanks:= _
        True, Transpose:=False

Giving me a error code 1004 - Paste Special Method of range class failed.

Is this due to copying a blank cell ?

Any advice ?

 

[JHB]

I think you only need the below line in your original code:

    ws.Cells(2, 31).Clear
    
    ws.Columns(4).Select ' Column is selected correctly here. Need this to be formatted as a number to 6 places ie 000000.
    [B][COLOR=Red]xlApp.Selection.NumberFormat = "000000"[/COLOR][/B]
    wb.Save

 

[tucker61]

Unfortunately that does not work, I think it's is because the field in access is a text field, so I need to convert to number in excel, before I can change the format.

The fields in access needs to be short text as some of the entries contain alpha..ie
Account 1 = 123456
Account 2 = 123456abc.

Unfortunately I do not have any control over the account reference..

 

[JHB]

Use the VAL function in the query to get only the numbers.

 

[tucker61]

But I need the text exporting as well.

Sent from my SM-T715 using Tapatalk

 

[JHB]

I think you need to make some screen shots of the data you've and what exactly you want.

 

[tucker61]

OK, My Data when it is first exported. is in File Capture.

The Reference field is formatted as Text. So i need to convert this to a number (Capture 2).

Then i need to format the numbers to 6 digits. (Capture 3).

The difficulty seems to be around trying to convert the reference field to numbers when some of the fields are text.

 

[arnelgp]

you can copy the report where your excel is derived.
modify it to the format you want.
and use this report instead of the original on your export.

 

[tucker61]

you can copy the report where your excel is derived.
modify it to the format you want.
and use this report instead of the original on your export.

Thanks, but not really sure how to do that either..

Sent from my SM-T715 using Tapatalk

 

[arnelgp]

Create a public function in your report or in standard module:

Public function fnFormat(p1 as variant) as string
p1=Format(Val( "0" & p1), "000000")
fnFormat=p1
End function

Then on your report, make the control source of the field or textbox you need to format:

=FnFormat([txtboxNameToFormat])