 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
MS Access Forum / Importing / Linking / August 2005Export to Excel not replacing spreadsheet
I am using following method to export a table to Excel: DoCmd.TransferSpreadsheet acExport, , "tblReport", "C:\CAIMPKC\CA_Imports.xls" But it is not replacing the existing file, it just adds to it causing the file to double/tripple, in size. It is also not formatting it, i looks like a csv export instead of an Excel formatted one. Please help :( I really need help with this issue...can someone please help? :( > I am using following method to export a table to Excel: > DoCmd.TransferSpreadsheet acExport, , "tblReport", "C:\CAIMPKC\CA_Imports.xls" [quoted text clipped - 4 lines] > > Please help :( Hi Belinda, I'm not sure what you mean by "not formatting it". When you export a table or query to Excel, the resulting worksheet gets whatever is your default worksheet formatting. When you say that exporting "just adds to it", do you mean that the records you export are appended to those already in the worksheet, or that a new worksheet is created, or what? Either way, you can simply delete the existing file before creating a new one: Dim strFileSpec as String strFileSpec = "C:\CAIMPKC\CA_Imports.xls" If Len(Dir(strFileSpec)) > 0 Then 'File exists Kill strFileSpec End If DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel8, _ strFileSpec >I really need help with this issue...can someone please help? :( > [quoted text clipped - 4 lines] >> file to double/tripple, in size. It is also not formatting it, i looks like >> a csv export instead of an Excel formatted one. -- John Nurick [Microsoft Access MVP] Please respond in the newgroup and not by email. Thank you for replying John! "Not formatting it" what I mean is, the resulting Excel spreadsheet looks as if it was saved as a CSV or text file--no format, columns all bunched together, no header row formatting, and so on. I've never had this happened before....they always looked as if I just copied and pasted, neatly formatted. Thanks for the tip on the Kill file. I read that as I was searching the forums and implemented that change. (just fyi...what it was doing was appending to the existing file, it was not creating a new tab). > Hi Belinda, > [quoted text clipped - 29 lines] > > Please respond in the newgroup and not by email. Belinda, I'm still not sure I understand what you're seeing "columns all bunched together": does this mean that data from all the fields in the table ends up in the first column of the worksheet? I've never seen that. Do you just mean that the columns are still at their default widths and not adjusted to match the widest data value? If so, this is normal. As for "no header row formatting", again I'm not sure what you mean. Normally when you export a table or query to Excel there is no speical formatting of the header row. >Thank you for replying John! > [quoted text clipped - 40 lines] >> >> Please respond in the newgroup and not by email. -- John Nurick [Microsoft Access MVP] Please respond in the newgroup and not by email. John, If you copy and paste from Access to Excel, Excel displays your data in a nice format: header row is clearly identified (bold, background color), gridlines, font colors etc. If I export a query via a macro using the "Output to" action, once again, the resulting Excel spreadsheet looks all nice and formatted. Now, when I export it using VBA the spreadsheet is not formatted. The columns are still at their default widths, etc. I need it to look as when using the "Output to" action. > Belinda, > [quoted text clipped - 57 lines] > > Please respond in the newgroup and not by email. Use DoCmd.OutputTo, which gives the same effects as the OutputTo macro. TransferSpreadsheet works differently and produces the unformatted worksheet you've been seeing - though it's possible to write VBA code that opens the resulting spreadsheet in Excel and formats it any way you like. >John, >If you copy and paste from Access to Excel, Excel displays your data in a [quoted text clipped - 69 lines] >> >> Please respond in the newgroup and not by email. -- John Nurick [Microsoft Access MVP] Please respond in the newgroup and not by email. That's exactly what I needed. Thank you John!! You saved my project! Already keyed it and it works great :) Sometimes not knowing what to search for in the Access/VBA help is what kills you. > Use DoCmd.OutputTo, which gives the same effects as the OutputTo macro. > [quoted text clipped - 83 lines] > > Please respond in the newgroup and not by email. |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.