replacement with macro stop values from working in formula

[diederik777]

hey everyone,

just a small note, im using a bit of an outdated excel version, excel 2003 to be exact.

for a small assignment i have been tinkering with the excel macro function.
the assignment involves importing different text files into 1 excel sheet and formatting them so they can be used in formula's and graphs.

the files that are delivered however use a . instead of a , as decimal seperator. this is where my problem starts.
ive gotten far enough to get everything working with the macro.
but when i use the replace function with CTRL+H to replace all the . for , everything is fine and the values all work in their formula's.

but as soon as i let me macro function replace these . for , the values all stop working.
as soon as i double click on the cell and proceed to click out of the cell, note: not changing anything, the cell works in my formula again.

the bit of code is use to replace the . with , is as follows:

Range("A2:K2").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.replace What:=".", Replacement:=",", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False

the macro im writing is going to be used by other people, so i'd love to find out what causes this problem so that i can automate the process as much as possible.

if anyone knows a solution, i'd very much appreciate the help.
thanks in advance,
Diederik

edit:
i just figured that a possible solution might be to prompt the same window CTRL+H opens using VB.
if anyone knows the code for this, please let me know. ive been fairly new to VB and mostly figuring things out by recording and looking at what kind of code that generates

[Andrew-R]

Might it just need to recalculate?

Range("A2:K2").Replace ".",",", LookAt:=xlPart
Application.Calculate

[diederik777]

the data that i import is used in the calculations. when i import the data and replace the . with , the formula gives the error DIV/0. it seems as if the values are not seen as numbers untill they are "re-entered" by me focussing on the cell.

the cell where the calculation is in, is on a different sheet and cell. so i did:

 
sheets("grafiek").select
range("J2:J2").select
Application.calculate

using this recalculate application did not solve it, is it correct use of this line?

[RHCPgergo]

I think the values are perceived as text in excel. Try to use the text to columns option (data / text to columns). I think there's a VBA equivalent to it also.

[Andrew-R]

Can you post your workbook with the data imported and the errors showing? I can't replicate the error.

[diederik777]

the files are originaly .txt files which are imported into the sheet with the application.GetOpenFileName.

this file contains callibration data for a certain machine it looks like this

01/03/11      1,95      1,99      1,98      2,02      2,00      2,00      2,00      1,98      1,96      1,96

the user is allowed to select multiple files which are all imported in 1 excel sheet.
using the following code

Dim Dest As Range

ChDrive "C"
ChDir "\documents and settings\user\desktop"
'after implementing change to correct Dir

FileCollection = Application.GetOpenFilename("DobFiles (*.DOB),*.dob", Title:="Open DOB Files only", MultiSelect:=True)
    
If IsArray(FileCollection) = False Then Exit Sub

For Each C In FileCollection
        Set Dest = .Cells(.Rows.Count, 1).End(xlUp).Offset(1, 0)
        With .QueryTables.Add(Connection:="TEXT;" & C, Destination:=Dest)
            .AdjustColumnWidth = False
            .TextFileSpaceDelimiter = True
            .TextFileTabDelimiter = True
            .TextFileConsecutiveDelimiter = True
            .TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1)
            .Refresh BackgroundQuery:=False
            
        End With
        
    Next C
    
End With

following that is a bunch of code to automaticly sort the data to date and format the area. then comes the code to replace the . with , which was mentioned earlier.

Range("A2:K2").Select
    Range(Selection, Selection.End(xlDown)).Select
    'this line is used because the amount of lines added may vary depending on the users demand
    Selection.replace What:=".", Replacement:=",", LookAt:=xlPart, _
    SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
    ReplaceFormat:=False

the data is imported each on a different column, on the next sheet calculations are done on all the values in each column.
but when using the replace macro the values are not used in the graph or formula, but if i leave out the replacement from the macro and replace . with , using the CTRL+H command everything works fine


edit:
i copied the code from a different forum and seemed to do the job fine. but after inspecting i think it might have something to do with this line

 With .QueryTables.Add(Connection:="TEXT;" & C, Destination:=Dest)

[diederik777]

i have solved the problem, it might be a bit of an inefficient way to do it. but i made a seperate macro which opens, edits and saves all the seperate files.

after which this macro continues and therefor skips the replacement step in the above mentioned macro.

this can be marked as solved or deleted, since the problem is already solved. the code i used to format my single files is as follows:

Sub AllFiles()
    Dim folderPath As String
    Dim filename As String
    Dim wb As Workbook
  
    folderPath = "C:\documents and settings\int_lab\desktop\resultaten"
    
    If Right(folderPath, 1) <> "\" Then folderPath = folderPath + "\"
    
    filename = Dir(folderPath & "*.dob")
    Do While filename <> ""
      Application.ScreenUpdating = False
        Set wb = Workbooks.Open(folderPath & filename)
         
            Call MyMacro
                
        filename = Dir
    Loop
  Application.ScreenUpdating = True
  
End Sub

Sub MyMacro()
    
    Range("G12").Select
    Selection.FillRight
    Selection.FillDown
    Cells.replace What:=".", Replacement:=",", LookAt:=xlPart, SearchOrder _
        :=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
        
        Call TrimLText
        
    ActiveWorkbook.Close True
        
End Sub

Sub TrimLText()
' This module will trim extra spaces from LEFT SIDE.
    Dim MyCell As Range
    On Error Resume Next
        Selection.Cells.SpecialCells(xlCellTypeConstants, 23).Select
        For Each MyCell In Selection.Cells
            MyCell.Value = LTrim(MyCell.Value)
        Next
    On Error GoTo 0
End Sub

doing it this way as seen in the above macro's the extra spaces at the start of lines were trimmed as well as the dots replaced to comma's.

after that my original macro which calls up all the single files and imports them in a single sheet was working correctly.

nonetheless, thanks to everyone who tried to help ill be sure to come back here if i have more problems