0

Hi I have the following code, which when run, should create an output excel file with data, retrieved using an query within the macro. However when the output file is created it's a empty file which when clicked on will not open and displays an error message. When the query itself is run via design view it works fine so I don't think its the query part causing the issue. I've also used the coding (which is attached to a button on a dashboard) on other buttons and it works fine. Any Ideas what would cause this?

Dim xls As Excel.Application
Dim wkb As Excel.workbook
Dim wks As Excel.worksheet
Dim strFilePath As String

On Error Resume Next
strFilePath = "C:\xxx\xxx\Monthly MI : Submission Data -    Electronic Submissions.xlsx"
Kill strFilePath

DoCmd.SetWarnings False
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, "qryMonthlyMISubmissionDataElectronicSubmissions", "C:\xxx\xxx\" & "Monthly MI : Submission Data - Electronic Submissions.xlsx", True
DoCmd.SetWarnings True

Set xls = New Excel.Application
xls.Visible = True
Set wkb = xls.Workbooks.Open("C:\xxx\xxx\Monthly MI : Submission Data - Electronic Submissions.xlsx", , True)
wkb.Activate
wkb.ActiveSheet.Columns("A:I").EntireColumn.AutoFit

Set xls = Nothing
Set wkb = Nothing
Darren Bartrup-Cook
  • 18,362
  • 1
  • 23
  • 45
Matt555
  • 5
  • 4
  • Which error message? What have you tried? It would be helpful if you reduce your code to include only the part that is the problem. For example, create a file but only use a example piece of data to see if you still get the problem. See **how to create a [mcve]** as well as [this **checklist**](//codeblog.jonskeet.uk/stack-overflow-question-checklist/) for more information. – ashleedawg Aug 31 '18 at 10:48

1 Answers1

1

You can't use colons (:) in filenames since they are used to specify a drive letter (like c:).

More Information:

ashleedawg
  • 20,365
  • 9
  • 72
  • 105