Format issue

[AnnPhil]

Help!!!! I have a shared exel spreadsheet on the network that is set up with Field names and formated the so the number fields have a number format and text is text or general fields. The data is sometimes entered in manually and sometimes the data comes from AutoCad. It seems when coming from Autocad the number fields come in as text or general instead of number format. This becomes a big problem when the data is then imported into a Access table. So i go in a change the excel spreadsheet columns back to number format only to have AutoCad mess things up again. I thought i could protect the spreadsheet so that no formating changes could happen but i found out if you have a shared spreadsheet that option is not available.
Any suggestions would be greatly appreciated.

thanks in advance

 

[shades]

Howdy. Perhaps the easiest solution is to have a short macro to run after every import from AutoCad. You can record the macro, just type the number 1 into a cell, copy, then select all the cells that need to be numbers, and Paste Special (Values, Multiply). This will convert to numbers. As you look at the code, you can try some variations (i.e. you don't need the number 1 in a cell, just in code). If you have more questions, please post.
________
Honda Cr-X Specifications

 

[MGumbrell]

AnnPhil

Can I ask what you are using the interaction between AutoCad and Excel. I am interesed as I am looking to, through the "filter" comand of CAD to import the number found directly into a worksheet for take off purposes and wonder what you are using it for. I am very new to CAD but see a real benifit to being able to carry out a take off directly into excel.

Regards, Matt

 

[AnnPhil]

thanks Shades

I was thinking along the same thing and i created macro but i like your idea about not having a 1 in a cell but in the code instead, i am not good with vba could you tell me how to do this. Here is the code from my macro. I would assume its a simple fix on the line that points to the Range ("H7")....

thanks for your help

Sub Macro2()
Range("H7").Select
Selection.Copy
Columns("A:A").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlMultiply, _
SkipBlanks:=False, Transpose:=False
End Sub

 

[AnnPhil]

AnnPhil

Can I ask what you are using the interaction between AutoCad and Excel. I am interesed as I am looking to, through the "filter" comand of CAD to import the number found directly into a worksheet for take off purposes and wonder what you are using it for. I am very new to CAD but see a real benifit to being able to carry out a take off directly into excel.

Regards, Matt

Hello MaTT

I dont know AutoCad at all, I am sent the file via email in an excel file, the file is then linked to access, but the field is a text in excel and access needs it to be a number field.

 

[Brianwarnock]

I don't see how you can avoid having 1 stored in a cell if you are going to use the copy and paste solution, but Shades may provide the answer. You could do someting like

Sub multby1()
'multipies fields in selected range by 1

For Each c In ActiveWindow.RangeSelection
       
        c.Value = c.Value * 1
   
Next c

End Sub

here you select on the sheet all the cells to be changed and then run the macro, this was written with varying cells in mind , if you have a fixed set of cells then just hard code the range selection.

Brian

 

[neileg]

Hope one of the solutions works.

Just though I'd point out your common misconception. You can't format a cell in Excel, only data. Excel will let you put any old rubbish anywhere because it doesn't support the datatype concept. So you are wasting your time formatting the empty cells in the sheet.

You could probably leave the data as text in Excel and use CDbl() or Val() to treat these as numbers. The problem will arise when you have a mix of text and numbers in the sheet, because Access does depend on datatypes.

 

[Brianwarnock]

As I have no formal EXCEL education I have to assume that Neil is correct, however how come there is a Format Cells otion? Why is you format blank cells as text and then enter a number does it warn you that the number is stored as text,(in this case * 1 does not convert to number format, you have to format the ... cell?)

Brian

 

[neileg]

As I have no formal EXCEL education I have to assume that Neil is correct, however how come there is a Format Cells otion? Why is you format blank cells as text and then enter a number does it warn you that the number is stored as text,(in this case * 1 does not convert to number format, you have to format the ... cell?)
Brian

Mmm... You do have a good point, Brian.

I use Excel everyday, but they keep changing it! I mean what was wrong with the version that was bundled with Windows 2.0!

Seriously, though, what i mean to do was draw the distinction between the datatype in Access and the format in Excel. Yes, they are similar, but not the same.

 

[Brianwarnock]

Thanks Neil, and thanks for understanding my typos , I never could spell but now I also can't type, I'd say life was hard for us old geezers but having spent the afternoon cleaning my pond with my shirt off I guess not.

Brian

 

[unmarkedhelicopter]

Is it a particular column you have a problem with or is it a set of columns ?
Please list them.
You can have a macro run EVERY TIME a workbook is opened so it doesn't matter who or what opened it last time.
From what I recall AutoCAD will just write the file (i.e. a new file every time) so this may be why you are getting your text strings, if this is the case then the book won't contain any code either so you would still be stuffed.
Try to confirm what is happening (format the whole sheet with red cells do an AutoCAD export and see if they are still red, if they are we have a chance.
If not we'd have to write the code into your personal macro workbook and then work out a way to tell it that this is an AutoCAD file. Do you have naming conventions ?

 

[AnnPhil]

Brian, your code works great, now they just select the range and run the macro using a button on the toolbar.

Thanks

 

[Brianwarnock]

This is an old thread! but thanks for the feed back Ann, that is exactly how my users used it.

Brian