Replies

Okay, I realize that you don’t want an input mask, but now I’m asking you….What does this code do that an input mask doesn’t?

For example, if I use as my input mask LLLLL (or ?????), then the user must enter (at least) five letters. (AAAAA will require (aaaaa is entry optional) five letters or numbers, excluding symbols.) Isn’t that what you’re after? To prevent the user from entering anything but exactly what you want? What is the benefit to doing this in code?

Just curious,

Cecilia

Wouldn’t an input mask restrict what the user can enter?

Do you mean a validation rule then? Are you looking for code that checks the last character entered and tests to see if it’s a valid entry?

Sorry, sign me
~~Confused

Do you mean like an input mask?

If so, these are from Help:

L = Letter (A to Z, entry required).
? = Letter (A to Z, entry optional).

You can enter the appropriate mask in the textbox’s Input Mask property.

Hi Hans,

Yes, I did do that. I think the reference is Excel 8.0. Mostly, it works very nicely Every so often I get an automation error and have to shut down Access and start over. It seems that bits of Excel get stuck here & there, but I can live with that.

Thanks again, both of you!

Cecilia

Of course! Here’s my code:

Set rst = db.OpenRecordset(strSQL, dbOpenSnapshot)
‘Open the New Template
Set xlBook = Excel.Application.Workbooks.Open(strExternalFile)
Set xlSheet = xlBook.Worksheets(“Access Data”)

‘Paste the Recordset
With xlSheet
For lngColumn = 1 To rst.Fields.Count
.Cells(1, lngColumn).Value = rst.Fields(lngColumn – 1).Name
Next lngColumn
.Range(“A2”).CopyFromRecordset rst
End With

That’s funny, because CopyFromRecordset is working just fine in Access.

I’m already going from Access to Excel, at this point I can’t turn back now.

In any event, your code had what I needed:

For icols = 0 To rs.Fields.Count – 1
ws.Cells(1, icols + 1).Value = rs.Fields(icols).Name
Next

I just changed the reference from ws to xlApp and it works fine.

Thanks!

Cecilia

Actually, I just checked the code from your original post & it works just fine:

Function SubC(txt As String) As String
Dim x As Integer
Dim y As Integer

‘Test for “-” in string
‘If “-” Exists, Return String to space prior to “-“, i.e., ABL- is ABL
‘If “-” does not exist, Get entire Trimmed String

For x = 1 To Len(Trim(txt))

If Mid(txt, x, 1) = “-” Or Mid(txt, x, 1) = “*” Then ‘ Does “-” exist
y = x ‘Set Y = to placeholder of “-”
x = Len(txt) ‘Set X to end of loop
SubC = Left(txt, y – 1) ‘Return SubC Value
Exit For
Else:
SubC = Left(txt, x) ‘ If “-” does not exist, return trimmed txt

End If
Next x

End Function

This is what happens when you’re in flat mode and everyone else is threaded, your mind becomes so boggled that you forget where you’re replying….

How about

If instr(strMyString,chr(42))>0 then strMyString=Left$(strMyString,instr(strMyString,chr(42))-1)

THere’s a list of Character Codes in Access Help.

Cecilia

Oooh! I thought I had tried that, but evidently I had not. It works very nicely.

Thanks, Hans!

One back at you:

You could always use chr(42) instead of “*”

If instr(strMyString,chr(42))>0 then strMyString=Left$(strMyString,instr(strMyString,chr(42))-1)

Look up Character Codes in Access Help.

Access doesn’t like this line:
Set xlBook = xlApp.Workbooks.Open strExternalFile

It thinks it doesn’t need a file name at the end. I can’t seem to find a way to open a specific book that way, which _should_ be the way to do it?

Long explanation:

Basically, what I’m trying to do is this: my co has given me four weeks to design a reporting databases and build a gazillion reports from a transactional database that was built years ago and there are few people left that know a heck of a lot about it. The people in the dept that I am doing this for already have excel spreadsheets with their end reports, but to use them they use this horrible manual process of moving the data from iSQL and other stuff and massaging it this way and that. Since I don’t think I can both design an entire reporting database and all the reports in just four weeks, I’m going with the “good enough for now” approach and designing the reporting database and just filling the excel sheets until I have time to build proper Access reports.

So basically, my routine is supposed to copy an existing excel spreadsheet (sort of a template), name it a particular way, then fill it with appropriate data. This has to happen for about 12 separate spreadsheets (so far). Since the reports are already in the workbooks, I’m done. All I have to do is figure out how to save the data when the excel workbook isn’t passed to the user….

Cecilia

Actually, if all he wants is to truncate, this should work quickly and easily, then there’s only two lines of code…

If instr(strMyString,”-“)>0 then strMyString=Left$(strMyString,instr(strMyString,”-“)-1)
If instr(strMyString,”*”)>0 then strMyString=Left$(strMyString,instr(strMyString,”*”)-1)

Cecilia
(Sign me: jealous of those with nifty built in functions like Replace)

Please tell me…

This is available in 97???

Cecilia

How about
If Mid(txt, x, 1) = “-” or Mid(txt,x,1)=”*”

Here is what I do if I want to strip a character out of a string, you might want to modify it….

intPos = InStr(strMyString, “_”)
Do Until intPos = 0
strMyString = Left$(strMyString, intPos – 1) & ” ” & Mid$(strMyString, intPos + 1)
intPos = InStr(strMyString, “_”)
Loop

Hee hee, I thought I had too! (guess not enough, though) Just goes to show you that REBOOT really does work! *g*