Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Creating an Excel Workbook in Access/VBA - CSV to XLS conversion
The following is a copy of my post in AccessMonster, but I've had no response there. I hope there is someone in this newsgroup with enough familiarity with Access to give me a suggestion. When I open the workbook resulting from this code, I have a single column with the comma-seperated values in it, rather than a matrix of an equal number of rows and columns. "I am exporting queries to a 4 sheet XL workbook. The transfer spreadsheet method puts a ' in front of all of my numbers, necessitating a formatting procedure to remove them and properly format the fields. That's fine when there are a manageable number of cells to be formatted, but I have one sheet that has a matrix on the order of 175X175 cells that causes my StripXLFormats function (shamelessly pilfered from this newsgroup) to take upwards of 15 minutes to complete. This is unacceptable, so I'm trying to work around the problem with this one sheet by exporting it first, via TransferText to a CSV file, obviating the need to strip the ' from each cell and reformatting cell by cell. I then want to open it in Excel and save it as an Excel workbook. Then I can export the remaining 3 queries as Excel worksheets and format them as needed. DoCmd.TransferText acExportDelim, , strQuery, strPath & strCSVFile, True, "" Set xlObj = CreateObject("excel.application") With xlObj Set WkBk = .Workbooks.Open(strPath & strCSVFile) WkBk.SaveAs Filename:=strPath & strFile Set WkSht = WkBk.ActiveSheet WkSht.Name = strSheet WkBk.Save DoEvents WkBk.Close SaveChanges:=True DoEvents DoCmd.SetWarnings True DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "qryPEExport", strPath & strFile, True, "PEExport" StripXLFormats strPath & strFile, "PEExport" DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "qryCurveExp", strPath & strFile, True, "Curve" StripXLFormats strPath & strFile, "Curve" DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "qryPEFactorsExp", strPath & strFile, True, "PE_Factors" StripXLFormats strPath & strFile, "PE_Factors" Set WkBk = .Workbooks.Open(strPath & strFile) Set WkSht = WkBk.Sheets(strSheet) With WkSht ..Move after:=Worksheets("PE_Factors") ..Range("B2").Select ..Range(Selection, Selection.End(xlDown)).Select ..Range(Selection, Selection.End(xlToRight)).Select Selection.Style = "Currency" End With Screen.MousePointer = 0 ..Save ..Quit End With" -- ragtopcaddy ------------------------------------------------------------------------ ragtopcaddy's Profile: http://www.excelforum.com/member.php...o&userid=25838 View this thread: http://www.excelforum.com/showthread...hreadid=535577 |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Access to Excel check box conversion | Excel Discussion (Misc queries) | |||
Creating Excel Worksheets from Access Database | Excel Discussion (Misc queries) | |||
Creating a Query in Excel based on an Access Qry... | Excel Discussion (Misc queries) | |||
Creating a Product DATABASE in ACCESS or EXCEL and then retrieving | Excel Discussion (Misc queries) | |||
Excel to Access *.MDB conversion | Excel Discussion (Misc queries) |