Access to Excel Question Has Me Stumped

[IHeartA2K]

I am a total VBA noobie and have been forced to learn it this week. I love the challenge, but am at a point where my brain is fried and I am totally stumped.

I have my form that outputs to excel. The spreadsheet is then formatted within access using some excel automation stuff. The logic I am assuming is that I can output the data to a file then open up the file and have access format it. I would like the end-user to be able to define the name of the spreadsheet and have my code make the automation changes that I have coded.

Can anyone help me out? Here is a snippet of my code:

Dim stDocName As String
stDocName = "FormG037GMth"
DoCmd.OutputTo acForm, stDocName, acFormatXLS


Set ExcelApp = CreateObject("Excel.Application")

With ExcelApp.Application
.Visible = False
.Workbooks.Open CurrentProject.Path & "\FormG037GMth.xls"
.Worksheets.Add
.Worksheets("Sheet1").Activate

'excel formatting code

 

[Bat17]

Not sure what your question is!
Here is some code to play with though. You will need to set a reference to Excel, in any module go Tools>refereces and select MS Excel

Sub DelXlSheet()
Dim xlApp As Excel.Application
Dim wk As Excel.Workbook
Dim xlSheet As Excel.Worksheet

Set xlApp = New Excel.Application
Set wk = xlApp.Workbooks.Open("C:\MyFile.xls")
xlApp.DisplayAlerts = False
    wk.Sheets("Sheet2").Delete
xlApp.DisplayAlerts = True
xlApp.Worksheets.Add.Move After:=xlApp.Worksheets(xlApp.Worksheets.Count)
xlApp.Sheets(xlApp.Worksheets.Count).Name = "newSheet"
wk.Sheets("newSheet").Range("A1") = "First Header"
wk.Sheets("newSheet").Range("B1") = "Second Header"
wk.Sheets("newSheet").Range("C1") = "Third Header"
With wk.Sheets("newSheet").Range("A1:C1")
    .Font.Bold = True
    .Font.ColorIndex = 3
    .Interior.ColorIndex = 37
    .Interior.Pattern = xlSolid
    .HorizontalAlignment = xlCenter
    .Borders(xlEdgeLeft).LineStyle = xlContinuous
    .Borders(xlEdgeLeft).Weight = xlMedium
    .Borders(xlEdgeLeft).ColorIndex = xlAutomatic
    
    .Borders(xlEdgeBottom).LineStyle = xlContinuous
    .Borders(xlEdgeBottom).Weight = xlMedium
    .Borders(xlEdgeBottom).ColorIndex = xlAutomatic
    
    .Borders(xlEdgeTop).LineStyle = xlContinuous
    .Borders(xlEdgeTop).Weight = xlMedium
    .Borders(xlEdgeTop).ColorIndex = xlAutomatic
    
    .Borders(xlEdgeRight).LineStyle = xlContinuous
    .Borders(xlEdgeRight).Weight = xlMedium
    .Borders(xlEdgeRight).ColorIndex = xlAutomatic
    

    .Borders(xlInsideVertical).LineStyle = xlContinuous
    .Borders(xlInsideVertical).Weight = xlThin
    .Borders(xlInsideVertical).ColorIndex = xlAutomatic
    
    

End With
wk.Sheets("newSheet").Columns("A:C").EntireColumn.AutoFit
        

'Save with current name
wk.Save
xlApp.Visible = True
'wk.Close
xlApp.UserControl = True
'xlApp.Quit
Set wk = Nothing
Set xlApp = Nothing
End Sub

HTH

Peter

 

[mcirvine]

if you just want to rename the sheet, prompt the user for a sheetname then add this

Set ws = Worksheets(1) 'first worksheet

ws.Name = userinput

 

[IHeartA2K]

Thanks for the help guys and sorry I wasn't more clear (long day). I have a good grasp on how to edit excel from access. What I would like to have happen is when a user clicks on the excel form button he/she is able to name the workbook whatever they want. My problem is that in my code I have this line: .Workbooks.Open CurrentProject.Path & "\FormG037GMth.xls" - which points to the oupputted workbook that needs to be formatted. I would like it to point to whatever file the person outputted.

 

[Bat17]

If you are just letting them choose a file name but saving to a fixed path then you could just pop up an input box and use that for the OutputTo OutputFile Argument and opening the XLS file.

If you want to give them more choice then search this formum and you will find code to let you use the open file dialog box.

HTH

Peter

 

[IHeartA2K]

I am a little confused. The OutputTo opens up the "Output To" dialog box. Right now, if they type in any other name than "FormG037GMth.xls" the .Workbooks.Open CurrentProject.Path & "\FormG037GMth.xls" line of code will not reference the new filename they typed, and they will get an error.

 

[Bat17]

Something like this should do it but you will probably want to add more error checking to the file name!

Dim strFileName As String

strFileName = InputBox("Enter File Name", "File Name")

If strFileName = "" Then
    MsgBox "No Name Given"
    Exit Sub
End If

DoCmd.OutputTo acForm, stDocName, acFormatXLS, strFileName

With ExcelApp.Application
.Visible = False
.Workbooks.Open CurrentProject.path & "\" & strFileName
.Worksheets.Add
.Worksheets("Sheet1").Activate

HTH

Peter

 

[IHeartA2K]

Peter,
That works great. I can tweak the code from here. I appreciate your help!

 

[Bat17]

Glad you have it going now. I think the hardest thing with Access is knowing what it can do rather than knowing how to do it

Peter