Excel file save as from Access VBA (1 Viewer)

[vinwin06]

Hi,

I want to export a excel file from access and i want to save the excel file in the name based on my selection available in combo box . I am using the below mentioned code, but i am not getting the desired outout file saved in the path mentioned...

Can you please anyone help me on this;

Private Sub Command0_Click()

Dim objXL As Object
Dim strpath As String
On Error Resume Next

Set objXL = GetObject(, "Excel.Application")
Set objXL = GetObject("F:\DB Practice\File Template.xlsx")

objXL.Application.Visible = True
objXL.Parent.Windows(1).Visible = True


DoCmd.TransferSpreadsheet acExport, , "Vin_Qry_UK_RFT", "F:\DB Practice\File Template.xlsx", True, "RFT"

strpath = ("F:\DB Practice\File Template& _ &combo3.xlsx")

xlsx.SaveAs FileName:=(strpath), FileFormat:=56

end sub

Here combo3 is the value i am getting it from query in a combo box....

 

[pbaldy]

You have to concatenate the combo into the string:

"PartOfPath" & Me.Combo3 & "RestOfPath"

 

[vinwin06]

So other than that my codes are correct, because when i execute step by stpe also i can able to see the path but its not saving the excel file in the new name.

 

[SteveH2508]

I think. xlsx.SaveAs FileName:=(strpath), FileFormat:=56 should be

objXL.SaveAs FileName:=(strpath), FileFormat:=56

 

[pbaldy]

Did you fix how you were building the path?

 

[vinwin06]

Hi Steve,

I got your code working but the problem i faced is i am getting compatibility checked while saving the file. I think the file format = 56 should be something different for excel 2007?

 

[vinwin06]

Hi Pbaldy,

yes i fixed the buliding path but the file saving command is not working.

 

[pbaldy]

I'm not clear what the problem is, but does this help?

http://blogs.office.com/b/microsoft.../use-the-vba-saveas-method-in-excel-2007.aspx

 

[vinwin06]

Private Sub Command0_Click()

Dim blnObjectCreated As Boolean
Dim objXLS As Object 'Excel Application
Dim objWB As Object 'Workbook
Dim strOriginalFileName As String
Dim strNewFileName As String
Dim strRecordsetDataSource As String
strOriginalFileName = _
"F:\DB Practice\book1.xlsx"
strNewFileName = "F:\DB Practice\" _
& Nz(Me!Combo3.Value, "unknown") & ".xlsx"
blnObjectCreated = False
On Error Resume Next
Set objXLS = GetObject(, "Excel.Application")
If Err Then
Err.Clear
Set objXLS = CreateObject("Excel.Application")
If Err Then
MsgBox "Can't get an Excel Application"

Else
blnObjectCreated = True
End If
End If
Set objWB = objXLS.Workbooks.Open(strOriginalFileName)
'do something with the workbook and the worksheets here

Dim db As Database
Dim rs As Recordset
Set dbs = CurrentDb()
strRecordsetDataSource = "Export_region"
Set rs = dbs.OpenRecordset(strRecordsetDataSource, dbOpenDynaset, dbReadOnly)

If rs.RecordCount = 0 Then
MsgBox "No records found"
Else
objWS.Range("Region").CopyFromRecordset rs
End If
rs.Close
Set rs = Nothing
Set db = Nothing

' now save and close it
objWB.SaveAs strNewFileName
objWB.Close False

'if you created this excel instance, close it
If blnObjectCreated = True Then
objXLS.Quit
End If
'distroy the objects
Set objWB = Nothing
Set objXLS = Nothing


End Sub

I have a problem in the one i have highlighted for you.... i dont know the file path and excel path everything works fine now i have a problem the query didnot get the result i wanted.