E
Excellion - ExcelForums.com
Hi all, this is related to my last post about changing all data link
paths throughout a workbook. I have a spreadsheet that pulls data
from a few hundred data files (.lua) and this data was recently
moved. With some help from people here, I was able to write a macro
to replace the links to the data with the new links.
Sub ChangeConx()
Dim intInc As Integer, intInc1 As Integer
For intInc = 1 To ThisWorkbook.Sheets.Count
With ThisWorkbook.Sheets(intInc)
For intInc1 = 1 To .QueryTables.Count
With .QueryTables(intInc1)
.Connection = Replace(.Connection,
"TEXT;Z:\blah\", "TEXT;C:\blah\")
End With
Next
End With
Next
End Sub
Now the problem I have is that this data should be imported as
Delimited using 'Other' as '=' (equal sign). When I changed the
..Connection links, I guess that it destroyed the data where each
instance of a data link, the data is supposed to be delimited with =.
Now when I refresh the data, it comes into the workbook without any
delimiting.
Is there a way to specify universally in a workbook that ALL imported
data is delimited by '='? Or can I write a macro that searches for
how data is delimited and replace it - much like looking for
..Connection and replacing the path? Maybe it is .Delimited? I have no
clue
This is some tricky stuff and I don't know where to start. Any help
would be really appreciated!
Thanks
paths throughout a workbook. I have a spreadsheet that pulls data
from a few hundred data files (.lua) and this data was recently
moved. With some help from people here, I was able to write a macro
to replace the links to the data with the new links.
Sub ChangeConx()
Dim intInc As Integer, intInc1 As Integer
For intInc = 1 To ThisWorkbook.Sheets.Count
With ThisWorkbook.Sheets(intInc)
For intInc1 = 1 To .QueryTables.Count
With .QueryTables(intInc1)
.Connection = Replace(.Connection,
"TEXT;Z:\blah\", "TEXT;C:\blah\")
End With
Next
End With
Next
End Sub
Now the problem I have is that this data should be imported as
Delimited using 'Other' as '=' (equal sign). When I changed the
..Connection links, I guess that it destroyed the data where each
instance of a data link, the data is supposed to be delimited with =.
Now when I refresh the data, it comes into the workbook without any
delimiting.
Is there a way to specify universally in a workbook that ALL imported
data is delimited by '='? Or can I write a macro that searches for
how data is delimited and replace it - much like looking for
..Connection and replacing the path? Maybe it is .Delimited? I have no
clue
This is some tricky stuff and I don't know where to start. Any help
would be really appreciated!
Thanks