VB code to copy/paste (97)

Topic

New Reply

I am running a macro that takes data in access and exports to excel. however, limitations with excel cause only the first 255 characters to be imported and the rest cut off. i would like to still run my macro from aceess and then throw in a runCode command that consists of the code which copies and pastes the seleced columns and rows from access into selected columns and rows into excel. The entire sheet can be copied and pasted it is irrelevant (no real need to separate the job). I still need the import to open and execute the the excel and access files. i would just like to overwrite the import with copy and pastr codes from access to excel. sample code and lots of detailed info would be a huge help. can’t find info about this on other threads. write if you would like more info.

Reply | Quote

Replies

What you are talking about is OLE Automation (or several other names Microsoft has used in the past or present). It is fairly involved VBA code running in one application (Excel, Word, Access, etc.) that opens and works with another application. In this case you could run code in Excel to get data from Access, or you could run code in Access to paste data into Excel. The problem however is getting the data back from Excel to Access, as I assume you want to update records from what you wrote in the other post. That would involve lots of fairly complex code, and should probably be done in Excel. It sounds like what you are attempting to do is use Excel as a form for editing Access data. If you explore Access forms a bit more, I think you will find it can do just about everything Excel can do (except complex numerical analysis) in a manner quite friendly to users.

Some additional details on the structure of your Access tables and Excel templates would be helpful to understand the nature of the problem more fully. Hope this helps.

Reply | Quote

Hi Wendell,

Well we use excel because it is more viewer friendly.The process goes like this: i run an autoexec from access that gives the user a few options (Enter new data, view open data, view past data, update data, etc.) You see we first use excel to enter data because its needed to generate drop down lists, use macro buttons, and apply formatting procedures to keep the access cleanand it was much simpler using excel than access for these things. When all the data is finished being entered it gets exported into access and added to our database. This works fine for us as of now. The problem arises when queries are run to view the old or current data fields. once the query criterion are slected you can either generate an access report or move it to excel. The benefit of moving it to excelis that I have set up a Word template that allows the user to hit a button and it will take the info from the excel sheet and paste it into a set Word document for our company reports. So excel is mainly acting as a holder and pre-formatter for our Word document. I was unable to format in access to make it look nice in Word. ONCE IT IS EXPORTED TO EXCEL IT WILL NOT BE IMPORTED BACK INTO EXCEL. that should save some trouble. Excel is only used to format for our personal reports and not to affect or update our database, we use access forms for that. What we are exporting to becomes formatted and edited by the user, they just use all the info from the database as a starting point. I was thinking i could run my operation as it currently is and then add 1 step:

(Currently)

1- select criteria for query
2- click command button -choose to export to excel (data gets lost here)
a- Command button executes macro
1- echo NO
2- Open Query
3- Output to a new xls file w/ autostart
4-Close query
3- user manipulates to make sense in report puts it to word or prints out from there

If in my macro after step 3 while xls is open i could enable a macro to take what was genertaed in access (a new output to might be needed for access before xls is opened???) copy the entire queried data and paste the entire queried data then i would be in heaven. I just need the code so i can use a runCode statement in my macro that will do that.

The code should take the data from the query highlight it all and then copy. I can use the output to to open excel. then maybe a 2nd code could be entered that merely pastes what was highlihted and saved. this would do the trick. How does it sound to you? I hope i made sense. ask plenty of questions if you are not clear on what i am talking about. Thank you very much, you’re saving my internship with this help.

Reply | Quote

I agree with Wendell, you need to take a closer look at what Access can actually do. You haven’t mentioned anything that requires Excel at all. User-friendly is the way you build it. All those things can be done in Access with far greater control, including pasting the data into Word. You would ordinarily apply the formatting in Word, not in Access or Excel anyhow.

I suspect you’ve tried working with Access queries and tables but not with its forms, which are very powerful and quite different from forms in Word and Excel.

Reply | Quote

If i could go back and change all this stuff i would but i really dont think it would be possible, its kind of a sunk cost and just need that peice of code to stay afloat a little longer. if i try to change everything now who knows how far back it will push things. hope you understand, is it even possible to do what i was asking?

Reply | Quote

I can’t tell from your posts whether you’re actually running this “macro” (I’m assuming you’re using the term in the VBA rather than the Access sense) from Excel or from Access. This is confusing:[indent]

---

i run an autoexec from access that gives the user a few options (Enter new data, view open data, view past data, update data, etc.) You see we first use excel to enter data ….

---

[/indent] But later you say: [indent]

---

Excel is only used to format for our personal reports and not to affect or update our database, we use access forms for that….

---

[/indent]So are you doing it from Access or from Excel? If it’s from Excel, you might have better luck posting the question in the Excel board or even back in VB/VBA/.Net, but make sure you specify that you are dealing with Excel VBA code and not Access code. However, you also mentioned a Word template and it sounds like you’re merrily bouncing around between applications. I’m having a hard time figuring out what part of your question applies to which application. In which application is each of your steps taking place?

This is also confusing:[indent]

---

The problem arises when queries are run to view the old or current data fields. once the query criterion are slected you can either generate an access report or move it to excel

---

[/indent] What exactly *is* the problem besides the truncation of memo fields? If your difficulty is in passing a memo field that will be included in a Word mail merge, you could make life much easier for yourself by doing it directly from Access into a tab delimited text file instead of a spreadsheet. Then you could simply use the textfile as a datasource for the mail merge and apply the formatting in Word.

Reply | Quote

I’m as confused as Charlotte here:
[indent]

---

You see we first use excel to enter data because its needed to generate drop down lists, use macro buttons, and apply formatting procedures to keep the access cleanand it was much simpler using excel than access for these things. When all the data is finished being entered it gets exported into access and added to our database.

---

[/indent]I have to differ with you there – it’s much easier to clean up data using combo boxes and lists in Access than it is in Excel – I’ve done both and Access wins hands down every time. The only down side to using Access is that everyone has to have it installed on their PC and have a license for it.
[indent]

---

once the query criterion are slected you can either generate an access report or move it to excel. The benefit … So excel is mainly acting as a holder and pre-formatter for our Word document. I was unable to format in access to make it look nice in Word.

---

[/indent]The challenge in doing stuff directly from Access to Word is that tables are fairly hard to use – however with lots of OLE Automation code you can create pretty sophisticated Word documents on the fly. Another alternative is to simply copy and paste the results of a query in Access directly into Word – at least it works in Office XP where I am now. And yet another is the MS Query tool that can be used with Excel.
[indent]

---

The code should take the data from the query highlight it all and then copy. I can use the output to to open excel. then maybe a 2nd code could be entered that merely pastes what was highlihted and saved.

---

[/indent]What you are talking about would take an experienced VBA/OLE Automation programmer several days to do – unless you have lots of time, I think Charlotte’s suggestion of exporting it to a comma delimited file would be the best bet. You can take that directly into Word, and then do a convert text to table command which lets you present data in a nice form and you have the benefit of Word’s ability to format characters and paragraphs which neither Excel or Access do very well. To automate that task completely however is a major development too, so you would have to trust your users to do some of the work, just as you would with Excel. One final choice would be to export an Access report to an RTF document, and then edit it in Excel, but RTF documents aren’t very much fun to work with. If you are trying to do a MailMerge using Excel as the source, that can really get ugly – Princess has been trying to do that for several months in a series of recent posts.

So a couple of questions – are you trying to do mail merges? And have you tried exporting to a delimited text file? Hope this helps you.

Reply | Quote

Actually, if you export to the Word merge format, which is tab delimited text, you can use the text file as a table in Word without having to convert it from text. That’s always my preference, since it doesn’t mess up if there happens to be a comma within a field. You can also open the text file directly in Excel without any problems.

Reply | Quote

Hi guys,

So i see this OLE automation is pretty much out of the question. Well I’ve discussed the issue with my boss and he said that the only reason we sometimes export our query to excel is to send to other people. It is easier to send someone a spread sheet of xls than an access file. They use excel because thats where they know how to add/delete any data they would not someone else to see if they are sending the query results to them. Even if they could just do this in access it still needs to get outputed somewhere. If it is outputed to Word, my concern is that the query has about 20 columns an could have up to 1500 rows. I think that Word would make it almost impossible to format something that size into something easily readable. That is why excel was the only alternative.

Now I changed my output type from excel to a txt file. But, how do i turn that into a word table? I do not know what to do with a txt file when it is needed in Word. I opened the file using excel and performed the text alignment procedure. The problems with this is it creates a “l” (ie dash-line) between each column and row. those “l” dashes are also in the txt file itself. Could i set some option to get rid of those? I am concerned because i do not think it is possible for each user everytime they wanted to print a report to open the txt file in excel and then format each column. Its just too much work and i don’t trust them all to do it properly everytime. Could i have that automatically done as well. Would the .rtf format be any better for me. I need suggestions, comments, opinions, ideas anything you can come up with.

What If i output to access instead of xls or a txt file, and then do all the editing from there, is there a simple way to put this adjusted data nicely into another file (to be printed or sent via email for other viewers)?

Thanks a million.

Ed

Reply | Quote

I played with it some and found that if i put the query output to a table and then publish it to word i retain all my data. but its just too much stuff for a word doc. everything is all over the place because i have 20+ rows and 100’s of columns. how can i take what was published to word and make it viewer freindly. right now only 5 column headings are on the first page and then all the data is listed under it. about 20 pages later the 6-10 columns start. Can i edit it so that all the columns go sequentially order per row. So that every report is listed under itself not across 5 pages.?

Reply | Quote

We’re learning some things as we dig deeper that suggest Excel may indeed be the right answer for users to play with the data – we didn’t know you were dealing with 20+ columns and 1500 rows before. Have you tried running the query you plan to use and then do a copy and pasting it into Excel? I think if you do that, you may find that Excel will take more than 255 characters. Once you start manipulating it, you may be in trouble however, assuming that those memo fields are involved in the manipulation.

Another question is whether you want to print out a paper copy of the report – I would guess from what you indicated about Word that you do. That makes the problem even uglier, and makes Word less attractive, as you have a limit of 32 columns in a Word97 table (that’s from memory as I no longer have or use Office97 at this client), and it doesn’t do a very nice job of printing multi-page tables that cross the right margin. Excel on the other hand will let you print over and down or down and over, making it more attractive. Also you can shrink the print to fit across one page, though it may become unreadable.

Another thing to try is to save the query results as a CSV (comma separated values) file from Access, where you specify a text type of file, with a comma delimiter and Quotes as the text separator in the dialog box (I think that’s what the | symbol you found in the text file in your previous post was about so be sure to specify the comma). I believe that will also let you load more than 255 characters into an Excel cell, though I haven’t tested that either. Let us know how you get on.

Reply | Quote

Ok wendell here we go:

A normal copy/paste will not hold more than 255 in excel. However, if you do a paste special and choose either unicode, text or CSV as the data type then it will take it ALL in. The Biff5 paste is the best looking option because it allows easier row/column auto resizing but that option takes only the dreaded 255.

You see some fields have 400 or so charcetrs while others have 5 or so, auto fitting is nice to have. I made a macro button that autosizes all the cells on any data but when the paste special is used with anything other than Biff5 my macro just makes each column extra long and the row extra thin so the data-cells get extremly elongated. I am thinking i can just change my macro from an autosizing of the rows/columns to a manual set width (ie column sizes) macro, then wrap the text and auto size only the rows. Should work in theory (haha…heard that one before).

So now the part that needs some work is creating something that after the query creates a new table in access will copy the table and then paste special into an excel file. I could keep the generated query table open and do the output to excel to get the appropriate files open. So all i would need is some kind of code that says:

active table in access, select all, copy
active table in excel, paste special, unicode or the other ones

I could open/close the table, my query, the excel file for output with a simple macro command in access, it is just those few operations from access that i need. if only there were a macro recorder in access. I realize this willl probbaly take OLE code but can it be that bad since i can do most of the opening/closing files from an access macr, right? well what do you think, can it be done?

ps- checked out your web-site but no development yet

Reply | Quote

Well, if you insist here’s a link that will give you some idea of what the OLE code should look like. Q129304 Note that it covers three version of Access, so look for the 97 code as it is different from either of the others. The example that transfers the entire recordset is probably what you want to focus on.

Note that it uses a recordset, which you probably haven’t encountered, but if you can formulate the query into something that you can make a SQL statement out of, it may not be too difficult to put the code together. And the Excel code will look much like what you can generate by using the macro recorder, though it has to be wrapped in the OLE syntax. Actually you may not have to do too much formatting in Excel if you create a workbook template and always start with that template when you open Excel – you’ll need to tinker with the line that says:
Set Sheet = CreateObject(“Excel.Sheet”)
to get it to use the template. You might want to start with the simple single cell example just to make sure you can set a cell to text of more than 255 characters. (FYI, creating an Excel document is much easier in Office2K/XP as the TransferSpreadsheet command is more robust and will do most of the work for you.) Good Luck

Reply | Quote

To change a *tab*-delimited file into a Word table, open it in Word. That’s all that’s required. I have no idea what you mean by the character you mentioned. The only reason I can think of for that to be there is if you included some non-printing characters (like carriage returns) in your fields.

Word will handle 20 columns in a table without difficulty, but you would need to merge the data into a mail merge document to get the formatting you want. As for 1500 rows, that isn’t a report, it’s an encyclopedia! Do they really want to print out a spreadsheet that big? In that case, definitely use Excel because the user can control the page breaks and the size of what is printed on each page. I still recommend a tab-delimited file (Word merge format) rather than comma-delimited because Excel will open it without ever getting any of the columns in the wrong position, which can happen with commas.

You could write some fairly simple code for Excel to select A1 (or whatever position is the upper left corner), then Ctrl+Shift+End to select the entire range to the bottom right corner of the data. After that you could use use AutoFit to format the column widths appropriately. Watch out for that memo field though.

Reply | Quote

I found this to do the trick nicely, thanks for the help

Public Function TrnsToXls()
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel97, “Find All Issues by Selected
Criteria2”, “C:WINNTProfilesEdward SantevecchiDesktopFind All Issues By Selected Criteria2.xls”
End Function

Reply | Quote