VBA Excel Formatting: Need new eyes!!

[Lorlai]

I have the following code that will go through and format my excel spreadsheet:

'Finds the first empty cell in a column
Range("A1").End(xlDown).Offset(1, 0).Select
'the first few rows of the spreadsheet feature info
'about the filters used from Yotta. Not necessary for
'a database. This will detele these rows
Range(ActiveCell.Row & ":" & ActiveCell.Row).Select
Range("1:1", ActiveCell.Row & ":" & ActiveCell.Row).Delete
Range("A1").Select
'Jumps down to the next row Loops through the row until
'there is an empty cell, clearing the color formats and making the
'font black (automatic)
Do
With Selection.Interior
.Pattern = xlNone
.TintAndShade = 0
.PatternTintAndShade = 0
End With
With Selection.Font
.ColorIndex = xlAutomatic
.TintAndShade = 0
End With
'Finds and replaces spaces with an underscore
ActiveCell.Replace What:=" ", Replacement:="_", LookAt:=xlPart, _







SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
ActiveCell.Offset(0, 1).Select
Loop Until IsEmpty(ActiveCell)
'Deletes the last row of data
Range("A" & Cells(Rows.Count, "A").End(xlUp).Row).Select
Selection.Delete Shift:=xlUp

Everything is working just fine in Access, except for the
'Finds and replaces spaces with an underscore
ActiveCell.Replace What:=" ", Replacement:="_", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Instead of only replacing the space in the active cell, the code is changing every space in the spreadsheet with the first active cell replacement. I have run the exact code through Excel vba and had zero problems. Does anyone know why this would be happening throughout my workbook when running it through access?

Thank you!

[June7]

Why run from Access and not from Excel? Did you step debug?

[Lorlai]

Thank you for responding June7. I am using access because I am given the data in excel format and need to transfer it into access. I am trying to make this an automated process and am importing the excel data into an access database. Some formatting needs to go on before it can be entered into a database, however, and rather than having to have the user open excel, hit the macro button, close excel, open access, hit the macro button, and then be done, I am trying to streamline everything through access.

I have stepped through the program, but there isn't any problem that I can see. The code takes the first cell, finds/replaces the first cell's spaces, then suddenly replaces every cell in the sheet before continuing on and doing what it should be doing: each of the cells individually in my specified loop. Any advice?

Thank you!

[Rhino373]

I understand what you're trying to do but you can still do it from Excel. I'm doing this exact same thing in some work I have. I have a database and an Excel.xlsm file in the same path. The Excel file contains the macros to format the various files. Here is an excerpt of code from a piece of my database:

Set objMacroWorkbook = objExcel.workbooks.Open(CurrentProject.Path & "\Format Alignment Workbook.xlsm")
Set objWorkbook = objExcel.workbooks.Open(strFullName)
objExcel.Visible = True
objExcel.Application.Run "'Format Alignment Workbook.xlsm'!Combine"
objMacroWorkbook.Close
DoCmd.TransferText acImportDelim, "Alignment Import Specification", "Alignment Report", strPath & "temp.csv", True

So here I open the Macro workbook then the target workbook then run the macro called "Combine" from the macro workbook. What you don't see here is the macro saves the edited file as a file called "temp.csv". I then tell Access to close the macro workbook since the target workbook was closed by Excel then I run the import.

That one macro workbook contains about 8 macros for various formatting of files. I have objExcel.Visible = True so you can see when Excel opens and everything but you can just omit this to not show the user something is happening.