Excel 2002, Win XP, SP-2
I created the attached macro to open a tab delimited file located in the
folder C:/EMP3. It works fine.
However, I've changed the folder name on the C drive to EMP4.
I've edited the macro to replace the reference EMP3 to EMP4, but it does not
run. It seems to hang up at the Refresh BackgroundQuert: = False statement
(at least what's the line the debugger lighlights).
What is the correct way to edit the macro when I change the file location?
John,
As a rule I don't view attachments. So I'm responding without having viewed
your macro. I'm guessing that you either have a PivotTable or QueryTable in
your Excel file.
The directory location of a source file is contained in two places
(Connnection & CommandText). In order to change the source location of the
text file you need to modify these two properties of the PT/QT.
1) PivotCaches(1).Connection = "the_new_connection_string"
2) PivotCaches(1).CommandText = "the_new_query_string"
-OR-
1) QueryTables(1).Connection = "the_new_connection_string"
2) QueryTables(1).CommandText = "the_new_query_string"
You will need to reference the PT/QT appropriately for your situation. Let
me know if you need help here.
Troy
"John Garate" <j.garate@comcast.net> wrote in message
news:%23%23cs0CmLFHA.1180@TK2MSFTNGP14.phx.gbl...
> Excel 2002, Win XP, SP-2
>
> I created the attached macro to open a tab delimited file located in the
> folder C:/EMP3. It works fine.
>
> However, I've changed the folder name on the C drive to EMP4.
>
> I've edited the macro to replace the reference EMP3 to EMP4, but it does
> not
> run. It seems to hang up at the Refresh BackgroundQuert: = False statement
> (at least what's the line the debugger lighlights).
>
> What is the correct way to edit the macro when I change the file location?
>
>
>
>
>
Troy,
I'm not sure what you are telling me. I really just starting to learn to use
visual basic. I don't see the querytables commands you refer to. I've pasted
a copy of the macro into this email.
Sub ImportChurchSchedule()
'
' ImportVisitSchedule Macro
' Macro recorded 2/11/2005 by Jag
'
'
Range("B3").Select
With ActiveSheet.QueryTables.Add(Connection:= _
"TEXT;C:\EMP3\Tab files for Excel\EM Church Schedule.tab",
Destination:=Range( _
"B3"))
.Name = "EM Visit Schedule"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.TextFilePromptOnRefresh = False
.TextFilePlatform = xlWindows
.TextFileStartRow = 1
.TextFileParseType = xlDelimited
.TextFileTextQualifier = xlTextQualifierNone
.TextFileConsecutiveDelimiter = False
.TextFileTabDelimiter = True
.TextFileSemicolonDelimiter = False
.TextFileCommaDelimiter = False
.TextFileSpaceDelimiter = False
.TextFileColumnDataTypes = Array(1, 1, 1, 1)
.Refresh BackgroundQuery:=False
End With
End Sub
Sub FormateDateColumn()
'
' FormateDateColumn Macro
' Macro recorded 2/11/2005 by Jag
'
'
Columns("B:B").Select
Selection.NumberFormat = "mm-dd"
With Selection
.HorizontalAlignment = xlLeft
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.MergeCells = False
End With
Selection.NumberFormat = "mmm-dd"
Range("F14").Select
End Sub
Sub InsertData()
'
' InsertData Macro
' Macro recorded 2/11/2005 by Jag
'
'
Application.Run "ImportVisitSchedule"
Application.Run "SetColumnWidths"
Application.Run "FormateDateColumn"
End Sub
Public Sub Auto_Open()
Application.WindowState = xlMaximized
End Sub
"TroyW" <nospam@yahoo.com> wrote in message
news:OCL07HnLFHA.3296@TK2MSFTNGP15.phx.gbl...
> John,
>
> As a rule I don't view attachments. So I'm responding without having
> viewed your macro. I'm guessing that you either have a PivotTable or
> QueryTable in your Excel file.
>
> The directory location of a source file is contained in two places
> (Connnection & CommandText). In order to change the source location of the
> text file you need to modify these two properties of the PT/QT.
>
> 1) PivotCaches(1).Connection = "the_new_connection_string"
> 2) PivotCaches(1).CommandText = "the_new_query_string"
>
> -OR-
>
> 1) QueryTables(1).Connection = "the_new_connection_string"
> 2) QueryTables(1).CommandText = "the_new_query_string"
>
> You will need to reference the PT/QT appropriately for your situation. Let
> me know if you need help here.
>
> Troy
>
>
> "John Garate" <j.garate@comcast.net> wrote in message
> news:%23%23cs0CmLFHA.1180@TK2MSFTNGP14.phx.gbl...
>> Excel 2002, Win XP, SP-2
>>
>> I created the attached macro to open a tab delimited file located in the
>> folder C:/EMP3. It works fine.
>>
>> However, I've changed the folder name on the C drive to EMP4.
>>
>> I've edited the macro to replace the reference EMP3 to EMP4, but it does
>> not
>> run. It seems to hang up at the Refresh BackgroundQuert: = False
>> statement
>> (at least what's the line the debugger lighlights).
>>
>> What is the correct way to edit the macro when I change the file
>> location?
>>
>>
>>
>>
>>
>
>
John,
Disregard the previous information that I gave. It looks like you used the
MacroRecorder to record importing a file using the "Data | Import External
Data | Import Data..." command from the menubar.
You will need to modify the following line of code in
"ImportChurchSchedule":
From: "TEXT;C:\EMP3\Tab files for Excel\EM Church Schedule.tab",
To: "TEXT;C:\EMP4\Tab files for Excel\EM Church Schedule.tab",
Or you could repeat using the MacroRecorder to record importing the file
from it's new location
Troy
"John Garate" <j.garate@comcast.net> wrote in message
news:ediKVNxLFHA.2420@TK2MSFTNGP12.phx.gbl...
> Troy,
> I'm not sure what you are telling me. I really just starting to learn to
> use
> visual basic. I don't see the querytables commands you refer to. I've
> pasted
> a copy of the macro into this email.
>
>
>
> Sub ImportChurchSchedule()
> '
> ' ImportVisitSchedule Macro
> ' Macro recorded 2/11/2005 by Jag
> '
>
> '
> Range("B3").Select
> With ActiveSheet.QueryTables.Add(Connection:= _
> "TEXT;C:\EMP3\Tab files for Excel\EM Church Schedule.tab",
> Destination:=Range( _
> "B3"))
> .Name = "EM Visit Schedule"
> .FieldNames = True
> .RowNumbers = False
> .FillAdjacentFormulas = False
> .PreserveFormatting = True
> .RefreshOnFileOpen = False
> .RefreshStyle = xlInsertDeleteCells
> .SavePassword = False
> .SaveData = True
> .AdjustColumnWidth = True
> .RefreshPeriod = 0
> .TextFilePromptOnRefresh = False
> .TextFilePlatform = xlWindows
> .TextFileStartRow = 1
> .TextFileParseType = xlDelimited
> .TextFileTextQualifier = xlTextQualifierNone
> .TextFileConsecutiveDelimiter = False
> .TextFileTabDelimiter = True
> .TextFileSemicolonDelimiter = False
> .TextFileCommaDelimiter = False
> .TextFileSpaceDelimiter = False
> .TextFileColumnDataTypes = Array(1, 1, 1, 1)
> .Refresh BackgroundQuery:=False
> End With
> End Sub
> Sub FormateDateColumn()
> '
> ' FormateDateColumn Macro
> ' Macro recorded 2/11/2005 by Jag
> '
>
> '
> Columns("B:B").Select
> Selection.NumberFormat = "mm-dd"
> With Selection
> .HorizontalAlignment = xlLeft
> .VerticalAlignment = xlBottom
> .WrapText = False
> .Orientation = 0
> .AddIndent = False
> .IndentLevel = 0
> .ShrinkToFit = False
> .MergeCells = False
> End With
> Selection.NumberFormat = "mmm-dd"
> Range("F14").Select
> End Sub
> Sub InsertData()
> '
> ' InsertData Macro
> ' Macro recorded 2/11/2005 by Jag
> '
>
> '
> Application.Run "ImportVisitSchedule"
> Application.Run "SetColumnWidths"
> Application.Run "FormateDateColumn"
> End Sub
>
> Public Sub Auto_Open()
> Application.WindowState = xlMaximized
> End Sub
Troy,
I'ts when I change the line of code to "TEXT;C:\EMP4\Tab files for Excel\EM
Church Schedule.tab",
that the macro hangs. The only thing Ive done is change EMP3 to EMP4.
I know I could repeat using the MacroRecorder, but this is a diddicult
process because of the query. I was hoping to just to edit the code.
John
"TroyW" <nospam@yahoo.com> wrote in message
news:eDgT4KzLFHA.3812@TK2MSFTNGP10.phx.gbl...
> John,
>
> Disregard the previous information that I gave. It looks like you used the
> MacroRecorder to record importing a file using the "Data | Import External
> Data | Import Data..." command from the menubar.
>
> You will need to modify the following line of code in
> "ImportChurchSchedule":
>
> From: "TEXT;C:\EMP3\Tab files for Excel\EM Church Schedule.tab",
>
> To: "TEXT;C:\EMP4\Tab files for Excel\EM Church Schedule.tab",
>
> Or you could repeat using the MacroRecorder to record importing the file
> from it's new location
>
> Troy
>
> "John Garate" <j.garate@comcast.net> wrote in message
> news:ediKVNxLFHA.2420@TK2MSFTNGP12.phx.gbl...
>> Troy,
>> I'm not sure what you are telling me. I really just starting to learn to
>> use
>> visual basic. I don't see the querytables commands you refer to. I've
>> pasted
>> a copy of the macro into this email.
>>
>>
>>
>> Sub ImportChurchSchedule()
>> '
>> ' ImportVisitSchedule Macro
>> ' Macro recorded 2/11/2005 by Jag
>> '
>>
>> '
>> Range("B3").Select
>> With ActiveSheet.QueryTables.Add(Connection:= _
>> "TEXT;C:\EMP3\Tab files for Excel\EM Church Schedule.tab",
>> Destination:=Range( _
>> "B3"))
>> .Name = "EM Visit Schedule"
>> .FieldNames = True
>> .RowNumbers = False
>> .FillAdjacentFormulas = False
>> .PreserveFormatting = True
>> .RefreshOnFileOpen = False
>> .RefreshStyle = xlInsertDeleteCells
>> .SavePassword = False
>> .SaveData = True
>> .AdjustColumnWidth = True
>> .RefreshPeriod = 0
>> .TextFilePromptOnRefresh = False
>> .TextFilePlatform = xlWindows
>> .TextFileStartRow = 1
>> .TextFileParseType = xlDelimited
>> .TextFileTextQualifier = xlTextQualifierNone
>> .TextFileConsecutiveDelimiter = False
>> .TextFileTabDelimiter = True
>> .TextFileSemicolonDelimiter = False
>> .TextFileCommaDelimiter = False
>> .TextFileSpaceDelimiter = False
>> .TextFileColumnDataTypes = Array(1, 1, 1, 1)
>> .Refresh BackgroundQuery:=False
>> End With
>> End Sub
>> Sub FormateDateColumn()
>> '
>> ' FormateDateColumn Macro
>> ' Macro recorded 2/11/2005 by Jag
>> '
>>
>> '
>> Columns("B:B").Select
>> Selection.NumberFormat = "mm-dd"
>> With Selection
>> .HorizontalAlignment = xlLeft
>> .VerticalAlignment = xlBottom
>> .WrapText = False
>> .Orientation = 0
>> .AddIndent = False
>> .IndentLevel = 0
>> .ShrinkToFit = False
>> .MergeCells = False
>> End With
>> Selection.NumberFormat = "mmm-dd"
>> Range("F14").Select
>> End Sub
>> Sub InsertData()
>> '
>> ' InsertData Macro
>> ' Macro recorded 2/11/2005 by Jag
>> '
>>
>> '
>> Application.Run "ImportVisitSchedule"
>> Application.Run "SetColumnWidths"
>> Application.Run "FormateDateColumn"
>> End Sub
>>
>> Public Sub Auto_Open()
>> Application.WindowState = xlMaximized
>> End Sub
>
>
John,
I've tested changing: "TEXT;C:\EMP4\" and it works for me. What is the exact
error message that you are receiving?
Troy
"John Garate" <j.garate@comcast.net> wrote in message
news:OON%23yF9LFHA.3988@tk2msftngp13.phx.gbl...
> Troy,
> I'ts when I change the line of code to "TEXT;C:\EMP4\Tab files for
> Excel\EM Church Schedule.tab",
> that the macro hangs. The only thing Ive done is change EMP3 to EMP4.
> I know I could repeat using the MacroRecorder, but this is a diddicult
> process because of the query. I was hoping to just to edit the code.
>
> John
TroyW,
I finally got it working. The problem was that the macro is in a template
and I was changing the macro in a worksheet created by the template. I
should have been changing the macro in the template. I thought the changes
you make to a macro in a worksheet updates the macro in the template.
Thanks for all your help
John
"TroyW" <nospam@yahoo.com> wrote in message
news:uj9zBGHMFHA.2680@TK2MSFTNGP09.phx.gbl...
> John,
>
> I've tested changing: "TEXT;C:\EMP4\" and it works for me. What is the
> exact error message that you are receiving?
>
> Troy
>
> "John Garate" <j.garate@comcast.net> wrote in message
> news:OON%23yF9LFHA.3988@tk2msftngp13.phx.gbl...
>> Troy,
>> I'ts when I change the line of code to "TEXT;C:\EMP4\Tab files for
>> Excel\EM Church Schedule.tab",
>> that the macro hangs. The only thing Ive done is change EMP3 to EMP4.
>> I know I could repeat using the MacroRecorder, but this is a diddicult
>> process because of the query. I was hoping to just to edit the code.
>>
>> John
>
>
John,
Glad you figured it out. Thanks for clearing up the mystery. Cheers.
Troy
"John Garate" <j.garate@comcast.net> wrote in message
news:eNs8CiJMFHA.3452@TK2MSFTNGP10.phx.gbl...
> TroyW,
> I finally got it working. The problem was that the macro is in a template
> and I was changing the macro in a worksheet created by the template. I
> should have been changing the macro in the template. I thought the changes
> you make to a macro in a worksheet updates the macro in the template.
>
> Thanks for all your help
>
>
> John
> "TroyW" <nospam@yahoo.com> wrote in message
> news:uj9zBGHMFHA.2680@TK2MSFTNGP09.phx.gbl...
>> John,
>>
>> I've tested changing: "TEXT;C:\EMP4\" and it works for me. What is the
>> exact error message that you are receiving?
>>
>> Troy
>>
>> "John Garate" <j.garate@comcast.net> wrote in message
>> news:OON%23yF9LFHA.3988@tk2msftngp13.phx.gbl...
>>> Troy,
>>> I'ts when I change the line of code to "TEXT;C:\EMP4\Tab files for
>>> Excel\EM Church Schedule.tab",
>>> that the macro hangs. The only thing Ive done is change EMP3 to EMP4.
>>> I know I could repeat using the MacroRecorder, but this is a diddicult
>>> process because of the query. I was hoping to just to edit the code.
>>>
>>> John
>>
>>
>
>
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks