Access VBA - transfer data to multiple excel sheets and format them

[ketangarg86]

Hi,

I have some questions. Could you please help.

1. I am using the below to transfer data from Access query to multiple worksheets in the same excel workbook (xlsx) which is working fine. But the name in the excel workbook comes as _1_Opportunity__Focus_ and _1a_Opportunity__Non_focus_ instead of 1-Opportunity (Focus) and 1a-Opportunity (Non-focus). Please help.

DoCmd.TransferSpreadsheet acExport, , "Stage 1", FolderSelection, True, "1-Opportunity (Focus)"
DoCmd.TransferSpreadsheet acExport, , "Stage 1a", FolderSelection, True, "1a-Opportunity (Non-focus)"



2. Can I transfer data from access to a .xlsm excel file. What would be command then.

3. Once I have transferred the data to the excel file, I want to format the excel file from within the same access vba which I used to transfer the data. I can record the macro in another excel file and copy the code to the macro but having difficulty how to do that.

Please help.

[ranman256]

You cant have spaces in tab name. Name cant start with a number.

To 'edit' excel files you need to start a session to control by vba.
REMEMBER TO ADD EXCEL.OBJECT LIBRARY in vbe menu, TOOLS, REFERENCES.

Private Sub EditXL()
Dim xl As excel.Application
Dim sSht As String
Dim colSheets As New Collection
Set xl = CreateObject("excel.application")
With xl
   .Visible = True
   .Workbooks.Open pvFile
    
           'record macro in Excel and put here.. remember to put dot in from of xl objects
       .activeworksheet.select
   .ActiveWorkbook.Close False
   .Quit
End With
Set xl = Nothing
end sub

[ketangarg86]

Thanks dude. I am getting an error at the below. I put dot in front of xl object. I tried even removing the dot from in front of columns but still the same error. I am using Access 2013. Could you please help.

.Sheets("1-Opportunity (Focus)").Activate
.Columns("I:I").ColumnWidth = "26"