asked on

Create excel workbook with Column Headers

Access vba 2010
Excel 2010 vba

I need to revise the code below to do the following:
name sheet1 "Import_Data"

Add the following Column Headers:
"SKU" "Product Description" "Target Price" Target gp"

The format the Columns as:

Sku - Text
Product Description = Text
Target Price = Currency
Target gp = Percentage

Public Sub createExcelFile()
    Dim XL As Excel.Application, WB As Excel.Workbook, WKS As Excel.Worksheet
    Dim db As DAO.Database, rec As DAO.Recordset, f As DAO.Field
    Dim i As Integer, j As Integer
 
    Set XL = New Excel.Application
    XL.Visible = True
    Set WB = XL.Workbooks.Add
    Set WKS = WB.Worksheets(1)
 
    WB.SaveAs Filename:="C:\Users\BR1\DESKTOP\Report1.xlsx", _
        FileFormat:=xlOpenXMLWorkbook
 
    
 
    WB.Close SaveChanges:=true
    Set WB = Nothing
    XL.Quit
    Set XL = Nothing
End Sub

Open in new window

Thanks
fordraiders

Shums Faruk

Hi Try below:

Public Sub createExcelFile()
    Dim XL As Excel.Application, WB As Excel.Workbook, WKS As Excel.Worksheet
    Dim db As DAO.Database, rec As DAO.Recordset, f As DAO.Field
    Dim i As Integer, j As Integer
 
    Set XL = New Excel.Application
    XL.Visible = True
    Set WB = XL.Workbooks.Add
    Set WKS = WB.Worksheets("Import_Data")
    WKS.Range("A1").Value = "SKU"
    WKS.Range("B1").Value = "Product Description"
    WKS.Range("C1").Value = "Target Price"
    WKS.Range("D1").Value = "Target gp"

    WKS.Columns("A:B").NumberFormat = "@"
    WKS.Columns("C:C").NumberFormat = "$#,##0.00_);($#,##0.00)"
    WKS.Columns("D:D").NumberFormat = "0.00%"
    
    WB.SaveAs Filename:="C:\Users\BR1\DESKTOP\Report1.xlsx", _
        FileFormat:=xlOpenXMLWorkbook
 
    
 
    WB.Close SaveChanges:=True
    Set WB = Nothing
    XL.Quit
    Set XL = Nothing
End Sub

Open in new window

Fordraiders

ASKER

shums, Getting automation error here:
Set WB = XL.Workbooks.Add
Set WKS = WB.Worksheets("Import_Data")

I need to rename sheet1 to "Import_Data" not create a new worksheet.

Thanks
fordraiders

Shums Faruk

Oh Sorry, I miss read your comment. Try below:

Public Sub createExcelFile()
    Dim XL As Excel.Application, WB As Excel.Workbook, WKS As Excel.Worksheet
    Dim db As DAO.Database, rec As DAO.Recordset, f As DAO.Field
    Dim i As Integer, j As Integer
 
    Set XL = New Excel.Application
    XL.Visible = True
    Set WB = XL.Workbooks.Add
    Set WKS = WB.Worksheets(1)
    WKS.Name = "Import_Data"
    WKS.Range("A1").Value = "SKU"
    WKS.Range("B1").Value = "Product Description"
    WKS.Range("C1").Value = "Target Price"
    WKS.Range("D1").Value = "Target gp"

    WKS.Columns("A:B").NumberFormat = "@"
    WKS.Columns("C:C").NumberFormat = "$#,##0.00_);($#,##0.00)"
    WKS.Columns("D:D").NumberFormat = "0.00%"
    
    WB.SaveAs Filename:="C:\Users\BR1\DESKTOP\Report1.xlsx", _
        FileFormat:=xlOpenXMLWorkbook
 
    
 
    WB.Close SaveChanges:=True
    Set WB = Nothing
    XL.Quit
    Set XL = Nothing
End Sub

Open in new window

Fordraiders

ASKER

Shums, Corrected code..Working now.
Set XL = New Excel.Application
XL.Visible = True

Set WB = XL.Workbooks.Add

WB.Sheets(1).name = "Import_Data"

WB.Sheets(1).Range("A1").Value = "SKU"
WB.Sheets(1).Range("B1").Value = "PRODUCT_DESCRIPTION"
WB.Sheets(1).Range("C1").Value = "QTY"
WB.Sheets(1).Range("D1").Value = "TARGET_PRICE"
WB.Sheets(1).Range("E1").Value = "COMPETITOR_PRICE"
WB.Sheets(1).Range("F1").Value = "TARGET_GP"
WB.Sheets(1).Range("G1").Value = "CURRENT_PRICE"
WB.Sheets(1).Range("H1").Value = "VENDOR_GUIDELINE_GP"
WB.Sheets(1).Range("I1").Value = "APPROVED_PRICE"
WB.Sheets(1).Range("J1").Value = "APPROVED_GP"
WB.Sheets(1).Range("K1").Value = "SEND_TO_LEADER"

' NOW FORMAT COLUMNS
WB.Sheets(1).Columns("A:B").NumberFormat = "@" ' TEXT
' WB.Sheets(1).Columns("C:C").NumberFormat = "@"
WB.Sheets(1).Columns("D:D").NumberFormat = "$#,##0.00_);($#,##0.00)"
WB.Sheets(1).Columns("E:E").NumberFormat = "$#,##0.00_);($#,##0.00)"
WB.Sheets(1).Columns("F:F").NumberFormat = "0.00%"
WB.Sheets(1).Columns("G:G").NumberFormat = "$#,##0.00_);($#,##0.00)"
WB.Sheets(1).Columns("H:H").NumberFormat = "0.00%"
WB.Sheets(1).Columns("I:I").NumberFormat = "0.00%"
WB.Sheets(1).Columns("J:J").NumberFormat = "$#,##0.00_);($#,##0.00)"
WB.Sheets(1).Columns("K:K").NumberFormat = "0.00%"

I added more columns. Go this to work.

fordraiders

Shums Faruk

Have you tried the given code with additional column settings?
Below is inclusive of additional columns:

Public Sub createExcelFile()
    Dim XL As Excel.Application, WB As Excel.Workbook, WKS As Excel.Worksheet
    Dim db As DAO.Database, rec As DAO.Recordset, f As DAO.Field
    Dim i As Integer, j As Integer
 
    Set XL = New Excel.Application
    XL.Visible = True
    Set WB = XL.Workbooks.Add
    Set WKS = WB.Worksheets(1)
    WKS.Name = "Import_Data"
    WKS.Range("A1").Value = "SKU"
    WKS.Range("B1").Value = "PRODUCT_DESCRIPTION"
    WKS.Range("C1").Value = "QTY"
    WKS.Range("D1").Value = "TARGET_PRICE"
    WKS.Range("E1").Value = "COMPETITOR_PRICE"
    WKS.Range("F1").Value = "TARGET_GP"
    WKS.Range("G1").Value = "CURRENT_PRICE"
    WKS.Range("H1").Value = "VENDOR_GUIDELINE_GP"
    WKS.Range("I1").Value = "APPROVED_PRICE"
    WKS.Range("J1").Value = "APPROVED_GP"
    WKS.Range("K1").Value = "SEND_TO_LEADER"
    

    WKS.Columns("A:C").NumberFormat = "@" 'TEXT - SKU/PRODUCT_DESCRIPTION/QTY
    WKS.Columns("D:E").NumberFormat = "$#,##0.00_);($#,##0.00)" 'CURRENCY - TARGET_PRICE/COMPETITOR_PRICE
    WKS.Columns("F:F").NumberFormat = "0.00%" 'PERCENTAGE - TARGET_GP
    WKS.Columns("G:G").NumberFormat = "$#,##0.00_);($#,##0.00)" 'CURRENCY - CURRENT_PRICE
    WKS.Columns("H:H").NumberFormat = "0.00%" 'PERCENTAGE - VENDOR_GUIDELINE_GP
    WKS.Columns("I:I").NumberFormat = "$#,##0.00_);($#,##0.00)" 'CURRENCY - APPROVED_PRICE
    WKS.Columns("J:J").NumberFormat = "0.00%" 'PERCENTAGE - APPROVED_GP
    WKS.Columns("K:K").NumberFormat = "@" 'TEXT - SEND_TO_LEADER
    
    WB.SaveAs Filename:="C:\Users\BR1\DESKTOP\Report1.xlsx", _
        FileFormat:=xlOpenXMLWorkbook
 
    
 
    WB.Close SaveChanges:=True
    Set WB = Nothing
    XL.Quit
    Set XL = Nothing
End Sub

Open in new window

Fordraiders

ASKER

Shums,
I'm not adding a sheet-renaming the Sheet1.

The code breaks here:
Set WB = XL.Workbooks.Add
Set WKS = WB.Worksheets(1)
WKS.Name = "Import_Data"
WKS.Range("A1").Value = "SKU" <------------ Error...wrong syntax.

ASKER CERTIFIED SOLUTION

Shums Faruk

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Fordraiders

ASKER

Thanks very much for the help !