Hi all,
Here's a real difficult problem for you Access experts to solve! I've created a database that has a number of reports. These are exported out to Excel spreadsheets on the press of a button using the OutputTo command (Note: The report is exported NOT the query behind the report - I need the report layout). After export I then do various formatting by linking into Excel using macro code and a reference to the Microsoft Excel Object Library. With me so far? Good! This formatting comprises of realigning columns and changing titles into bold font etc. Many of the reports have date fields and this is where the problem occurs. I want to be able to sort in date order on these fields immediately after opening the files. However when they are first exported, the date fields do not begin as date format, instead they remain as text until you click inside each of the cells with the mouse. There is a process known as "Convert Text to Columns" which can be selected from the Data menu. In Excel, if I select one of the date columns and then run Text To Columns on it, all the values immediately become proper dates instead of text values. You still understand what I'm talking about? Ok so I thought if this works in Excel why not record a macro, grab the code and use it in Access to convert the dates automatically after the file is created...so Ive done this and set up the code. In the following example as a test Ive set it up to convert the 7th column (column G) which is a date field...
But the problem is when I run the code, instead of working like it should, the following error appears:
Error 1004 - Microsoft Excel can convert only one column at a time. The range can be many rows tall but no more than one column wide. Try again by selecting cells in one column only.
This is extremely confusing considering I have not selected multiple columns at all (only column G). So my question is what am I doing wrong? Has anyone ever needed to do this themselves or know an alternatie solution to fixing the dates? Please please let me know!
Here's a real difficult problem for you Access experts to solve! I've created a database that has a number of reports. These are exported out to Excel spreadsheets on the press of a button using the OutputTo command (Note: The report is exported NOT the query behind the report - I need the report layout). After export I then do various formatting by linking into Excel using macro code and a reference to the Microsoft Excel Object Library. With me so far? Good! This formatting comprises of realigning columns and changing titles into bold font etc. Many of the reports have date fields and this is where the problem occurs. I want to be able to sort in date order on these fields immediately after opening the files. However when they are first exported, the date fields do not begin as date format, instead they remain as text until you click inside each of the cells with the mouse. There is a process known as "Convert Text to Columns" which can be selected from the Data menu. In Excel, if I select one of the date columns and then run Text To Columns on it, all the values immediately become proper dates instead of text values. You still understand what I'm talking about? Ok so I thought if this works in Excel why not record a macro, grab the code and use it in Access to convert the dates automatically after the file is created...so Ive done this and set up the code. In the following example as a test Ive set it up to convert the 7th column (column G) which is a date field...
Code:
Dim oxlApp As Excel.Application
Dim oxlWorkbook As Workbook
Dim oxlWorksheet As Worksheet
'Start a new excel application in the background
Set oxlApp = New Excel.Application
'Open the workbook passed as the parameter in workbook object
Set oxlWorkbook = oxlApp.Workbooks.Open(strFileName)
'Open Sheet1 of the workbook in a worksheet object
Set oxlWorksheet = oxlWorkbook.Sheets("Sheet1")
'Select date column
oxlWorksheet.Columns("G:G").Select
'Run Text To Columns on it to fix dates
oxlApp.Selection.TextToColumns Destination:=Range("G1"), DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, Semicolon:=False, Comma:=False, space:=False, Other:=False, FieldInfo:=Array(1, 1)
'Save the workbook
oxlWorkbook.Save
'Close the workbook and quit the Excel application
oxlWorkbook.Close False
oxlApp.Quit
'Clear vars
Set oxlWorksheet = Nothing
Set oxlWorkbook = Nothing
Set oxlApp = NothingBut the problem is when I run the code, instead of working like it should, the following error appears:
Error 1004 - Microsoft Excel can convert only one column at a time. The range can be many rows tall but no more than one column wide. Try again by selecting cells in one column only.
This is extremely confusing considering I have not selected multiple columns at all (only column G). So my question is what am I doing wrong? Has anyone ever needed to do this themselves or know an alternatie solution to fixing the dates? Please please let me know!
.Select create a Convert_To_Text function using the snippet below. I've not had you declared problem using this.