Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations Chris Miller on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

using excel vba in access. does anyone know?!!

Status
Not open for further replies.

joeythelips

IS-IT--Management
Aug 1, 2001
305
IE
Hi.
I have this bit of vba for excel.
I want to do the exact same thing in access.
I know i can't use this code as it is so if anyone knows how to convert it or where i can get help on converting it please let me know.

'Copy and paste reading file to import
Range("A1").Select
Range(ActiveCell, ActiveCell.End(xlDown).End(xlToRight)).Select
Selection.Copy
Windows("MRSO extract.xls").Activate
Worksheets("Import").Activate
Range("a1").Select
ActiveSheet.Paste
'append readings to reading summary file
Workbooks.Open Filename:="S:\readings\READINGS SUMMARY.xls"
Windows("READINGS SUMMARY.xls").Activate
ActiveCell.SpecialCells(xlLastCell).Select
add1 = ActiveCell.Row
Cells(add1, 1).Select
ActiveCell.Offset(2, 0).Value = readfilename
ActiveCell.Offset(3, 0).Select
ActiveSheet.Paste
ActiveWorkbook.Save
ActiveWorkbook.Close
mrncount
first
Worksheets("Import").Activate
Range("a2").Activate
Range(ActiveCell, ActiveCell.End(xlDown)).Select
Selection.Resize(, Selection.Columns.Count + 1).Select
Selection.Copy
Worksheets("read out").Activate
Range("a1").Select
ActiveSheet.Paste
'remove trailing spaces from customer name
Range(ActiveCell, ActiveCell.End(xlDown)).Select
Selection.Offset(0, 2).Value = "=trim(rc2)"
Range("C1").Select
Range(ActiveCell, ActiveCell.End(xlDown)).Select
Application.CutCopyMode = False
Selection.Copy
Range("B1").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
'reformat date
Worksheets("Import").Activate
Range("h2").Select
Range(ActiveCell, ActiveCell.End(xlDown)).Select
Selection.Offset(0, 1).Value = "=value(trim(rc8))"
Range("i2").Select
Range(ActiveCell, ActiveCell.End(xlDown)).Select
Application.CutCopyMode = False
Selection.Copy
Range("h2").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Range(ActiveCell, ActiveCell.End(xlDown)).Select
Selection.NumberFormat = "dd-mmm-yy"
'replace "block estimate" and "no reading" and date
Worksheets("Import").Activate
theday = Left(readfilename, 2)
themonth = Mid(readfilename, 3, 2)
theyr = Right(readfilename, 2)
thedate = DateSerial(theyr, themonth, theday)
Range("f2").Select
Do Until IsEmpty(ActiveCell.Value) = True
If Application.IsText(ActiveCell.Value) = True Then
ActiveCell.Value = 0
Selection.Offset(0, 1).Value = "E"
Selection.Offset(0, 2).Value = thedate
End If
Selection.Offset(1, 0).Activate
Loop
'copy the rest to read out
Range("c2").Select
Range(ActiveCell, ActiveCell.End(xlDown).End(xlToRight)).Select
Selection.Copy
Worksheets("read out").Activate
Range("c1").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
'remove trailing spaces from read type
Range("i1").Select
Range(ActiveCell, ActiveCell.End(xlDown)).Select
Selection.Value = "=trim(rc7)"
Columns("i:i").Select
Application.CutCopyMode = False
Selection.Copy
Range("g1").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Columns("i:i").Select
Selection.Delete Shift:=xlToLeft
'save csv to A drive
Do Until diskette = vbYes
diskette = MsgBox(prompt:="Is there a diskette in drive A?", Buttons:=vbYesNo + vbQuestion)
Loop
On Error GoTo errhandle
Application.CutCopyMode = False
ActiveWorkbook.SaveAs Filename:="A:\RE" & readfilename & ".csv", FileFormat:=xlCSV, _
CreateBackup:=False
On Error GoTo 0
ActiveSheet.Name = "read out"
last
errhandle:
If Error() = "Cannot access 'A:'." Then
MsgBox Error() + "Insert floppy disk in drive A."
Resume
Else
MsgBox "File not saved."
Resume Next
End If
End Sub
 
Hi again joeythelips

One thread not enough?

What exactly are you trying to do? Without looking carefully at the code (which looks like lots of recorded macros stuck together) and looking at the comments - it looks like you want to import something from Excel and reformat the data a little.

This is what Access excels at and Excel doesn't.

I think you can dump all the code and look at queries to reformat the information.

Looks at queries and if you are finding it difficult, post your requirements here and we can go through the problem step-by-step.


Stew
"Make good use of bad rubbish."
 
Hi,

I'm not sure what u mean by looking at queries to reformat the info.

Joeythelips
 

Hi paddyman

I guess I meant manipulating data.

After importing the data (maybe to a temp table) queries can be used to change that data in whatever way is necessary.

Delete queries - to get rid of unwanted information.
Append quereies - to move information from one location to another.
Update queries - to change 'form' of data (removal of spaces for instance, change nulls to particular values etc)

The basic facilities of the query designer are very powerful and can be enhanced with the use of VBA.

Helpful?


Stew "Make good use of bad rubbish."
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top