Replies

ok so in my access macro i added the line runcode. i set the function name = Import(). But when i run the program i get “the expression you entered has a function name access can’t find. am i missing something in the set function name? i copy and pasted your code into a module and named it Import. I don’t see whats wrong, do you?

Hey try to learn them all and whatever you can’t figure out someone here will probaly know…. hehe.

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

Thanks Kevin,

I am not to familiar with VB and i really need this code. Do you think you could give me some more details on what EXACTLY to do. for example where does this code get entered within each option button? If it is the code per each option button if you could write out the full sample code then i can just paste and fix it for the other buttons. my problem is i can’t really tell what you are doing so i am completly lost. Thank you very much, this would solve a huge problem for us.

Ed

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.?

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

what i actually need though is a AND/OR over an entire form. I have a form with 6 or so list boxes. i want to select 1 piece of info from each list box and then run my query based on my selections. I have that part programmed. i would like to add 2 check boxes to eah list box. 1 for AND and 1 for OR statements. I would the user to be able to select which list box values must be (AND) contained in the query and which ones are may be conatined (OR). It should let the user have full control on which dcriteria ia mandatory and which is optional. Make sense? this code does it within te listbox correct? i need one to set AND/OR properties for comparable listboxes against eachother not within 1 listbox. hope i didnt confuse you.

forgetting the code might be a problem:

This simple code will pull the selected values from a list box, separate them by ” AND “, and append them to a string variable.

Dim x As Integer
Dim i As Integer
Dim strCriteria As String

For x = 0 To List1.ListCount – 1
If List1.Selected(x) Then
i = i + 1
strCriteria = strCriteria & x
If i < List1.SelCount Then
strCriteria = strCriteria & " AND "
End If
End If
Next x

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?

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.

unmamunka you’ve been a huge help everything works but like always theres 1 more thing. I think the autofit will suffice for now and i tried to auto fit the rows too with the line “.Rows.AutoFit”

Sub AutoWidth()
ChooseAll
TopAlign
With Selection
.EntireRow.Select
.EntireColumn.Select
.Rows.AutoFit // my addition
End With
End Sub

this line will only auto fit the first row and not the rest, i tried “EntireColumns.Select” above it but that didn’t work. Got the solution for this one too, thank you.

Ed

Also this sounds really dumb but how do i save the code so that when this file is overwritten the code does not get lost? i can’t remember what to do.

This is almost ideally what i would like but i need a little bit more control upon investigation. Can I indivdually and manully set the width of each column/row. So no autofit, I would like column A width = 5, column B width =10.2, row 1 length= 7.7 etc. is that allowed. thanks alot, this one worked but if i had full control it would be alot easier to read. thanks again.

Ed

I would love to do that but let me explain how this monster ive created works.

You enter data into a template on excel. This template is formatted to be exported perfectly to an access database which is constantly updated. Occasionally, someone will run a query based on a specific job# and all the data will come up, it is then exported to another excel template that allows the user to edit previous information. We do not allow edits to be done to the database itself because format restrictions are alot harder in access than in excel, and we use drop down list etc from excel. So when someone wants to edit a job with 10 rows and 20 columns they have to do it from excel. If the everything is broken up into 255 blocks then i will need to create sn insane amount of extra columns to store potential data. sometimes one field will have a few thousand characters. i do not want alot of empty columns sitting around for all the jobs that are brief. That’s why i think the copy and paste will be better because it will keep the size down and prevent me from going back and editing the templates and column titles in order ot handle the import/exports properly. What are your thoughts, does my suggestion make sense, i take it you know alot more on the subject than I. Thank you.

Ed

Would it be possible to do this:

Run my macro and then insert coding that will cut and paste certain columns to excel and thus overwrite the imported truncated data. You can copy and paste from access to excel without losing data so in theory this might work, correct? Could someone show me how?