Importing from textfile with macro Announcing the arrival of Valued Associate #679: Cesar Manara Planned maintenance scheduled April 17/18, 2019 at 00:00UTC (8:00pm US/Eastern) The Ask Question Wizard is Live! Data science time! April 2019 and salary with experience Should we burninate the [wrap] tag?Website search on ExcelNeed to restructure data received from web into excelReferencing a cell value in VBA that contains “%”Macro works in Word 2013, but not in 2010Excel VB for importing data from foldersexcel replace function in access vbaIssue extracting pattern from URL in VBARun time error 1004 excel cannot find the text file to refresh this external rangeExcel macro doesn't work after sometimeCell reference to a powerquery
When to stop saving and start investing?
Sorting numerically
Is the Standard Deduction better than Itemized when both are the same amount?
What LEGO pieces have "real-world" functionality?
If 'B is more likely given A', then 'A is more likely given B'
The logistics of corpse disposal
List *all* the tuples!
How does a Death Domain cleric's Touch of Death feature work with Touch-range spells delivered by familiars?
How do I stop a creek from eroding my steep embankment?
ListPlot join points by nearest neighbor rather than order
What is the musical term for a note that continously plays through a melody?
Can inflation occur in a positive-sum game currency system such as the Stack Exchange reputation system?
Stars Make Stars
Why aren't air breathing engines used as small first stages
If Jon Snow became King of the Seven Kingdoms what would his regnal number be?
Bonus calculation: Am I making a mountain out of a molehill?
How to recreate this effect in Photoshop?
Are my PIs rude or am I just being too sensitive?
How discoverable are IPv6 addresses and AAAA names by potential attackers?
Why one of virtual NICs called bond0?
Is above average number of years spent on PhD considered a red flag in future academia or industry positions?
What are 'alternative tunings' of a guitar and why would you use them? Doesn't it make it more difficult to play?
G-Code for resetting to 100% speed
Why does Python start at index -1 when indexing a list from the end?
Importing from textfile with macro
Announcing the arrival of Valued Associate #679: Cesar Manara
Planned maintenance scheduled April 17/18, 2019 at 00:00UTC (8:00pm US/Eastern)
The Ask Question Wizard is Live!
Data science time! April 2019 and salary with experience
Should we burninate the [wrap] tag?Website search on ExcelNeed to restructure data received from web into excelReferencing a cell value in VBA that contains “%”Macro works in Word 2013, but not in 2010Excel VB for importing data from foldersexcel replace function in access vbaIssue extracting pattern from URL in VBARun time error 1004 excel cannot find the text file to refresh this external rangeExcel macro doesn't work after sometimeCell reference to a powerquery
.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty,.everyoneloves__bot-mid-leaderboard:empty height:90px;width:728px;box-sizing:border-box;
1
I have an issue where i want to import value from a textfile into my excel-document which it does but when i run macro over and over to refresh said values with new ones it just moves it over and adds more, it needs to delete values and replace them, i didnt write this code myself so i cant understand the issue with it so here i am pleading for help
Sub ImportSaldo()
Call Shell("C:importGetFromFTP.bat", vbNormalFocus)
Dim fileName As String, folder As String
folder = "c:import"
fileName = "Saldot.txt"
ActiveCell.Offset(0, 0).Range("A1").Select
With ActiveSheet.QueryTables _
.Add(Connection:="TEXT;" & folder & fileName, Destination:=ActiveCell)
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.TextFilePromptOnRefresh = False
.TextFilePlatform = 850
.TextFileStartRow = 1
.TextFileParseType = xlDelimited
.TextFileTextQualifier = xlTextQualifierDoubleQuote
.TextFileConsecutiveDelimiter = False
.TextFileTabDelimiter = False
.TextFileSemicolonDelimiter = True
.TextFileCommaDelimiter = False
.TextFileSpaceDelimiter = False
.TextFileColumnDataTypes = Array(1, 1, 1, 1)
.TextFileTrailingMinusNumbers = True
.Refresh BackgroundQuery:=False
End With
End Sub
excel vba
asked Mar 22 at 8:23
Kenny BerglundKenny Berglund
61
add a comment |
1
I have an issue where i want to import value from a textfile into my excel-document which it does but when i run macro over and over to refresh said values with new ones it just moves it over and adds more, it needs to delete values and replace them, i didnt write this code myself so i cant understand the issue with it so here i am pleading for help
Sub ImportSaldo()
Call Shell("C:importGetFromFTP.bat", vbNormalFocus)
Dim fileName As String, folder As String
folder = "c:import"
fileName = "Saldot.txt"
ActiveCell.Offset(0, 0).Range("A1").Select
With ActiveSheet.QueryTables _
.Add(Connection:="TEXT;" & folder & fileName, Destination:=ActiveCell)
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.TextFilePromptOnRefresh = False
.TextFilePlatform = 850
.TextFileStartRow = 1
.TextFileParseType = xlDelimited
.TextFileTextQualifier = xlTextQualifierDoubleQuote
.TextFileConsecutiveDelimiter = False
.TextFileTabDelimiter = False
.TextFileSemicolonDelimiter = True
.TextFileCommaDelimiter = False
.TextFileSpaceDelimiter = False
.TextFileColumnDataTypes = Array(1, 1, 1, 1)
.TextFileTrailingMinusNumbers = True
.Refresh BackgroundQuery:=False
End With
End Sub
excel vba
asked Mar 22 at 8:23
Kenny BerglundKenny Berglund
61
1
ActiveCell.Offset(0, 0).Range("A1").Selectis your problem here. If you want the existing code to be forced to load the data to A1, remove that line and changeDestination:=ActiveCellon your.Addline toDestination:=Range("A1").
– CLR
Mar 22 at 9:03
150 Opening BINARY mode data connection for saldot.txt (188178 Bytes). > Replace Existing File with Temp File:I/O Error 226 Transfer complete. 188 178 bytes transferred. 183,77 KB/sec. ftp: 188178 bytes received in 0,31Seconds 603,13Kbytes/sec. ftp> I/O Error and crashed Excel when i did your change.
– Kenny Berglund
Mar 22 at 11:39
add a comment |
1
1
1
I have an issue where i want to import value from a textfile into my excel-document which it does but when i run macro over and over to refresh said values with new ones it just moves it over and adds more, it needs to delete values and replace them, i didnt write this code myself so i cant understand the issue with it so here i am pleading for help
Sub ImportSaldo()
Call Shell("C:importGetFromFTP.bat", vbNormalFocus)
Dim fileName As String, folder As String
folder = "c:import"
fileName = "Saldot.txt"
ActiveCell.Offset(0, 0).Range("A1").Select
With ActiveSheet.QueryTables _
.Add(Connection:="TEXT;" & folder & fileName, Destination:=ActiveCell)
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.TextFilePromptOnRefresh = False
.TextFilePlatform = 850
.TextFileStartRow = 1
.TextFileParseType = xlDelimited
.TextFileTextQualifier = xlTextQualifierDoubleQuote
.TextFileConsecutiveDelimiter = False
.TextFileTabDelimiter = False
.TextFileSemicolonDelimiter = True
.TextFileCommaDelimiter = False
.TextFileSpaceDelimiter = False
.TextFileColumnDataTypes = Array(1, 1, 1, 1)
.TextFileTrailingMinusNumbers = True
.Refresh BackgroundQuery:=False
End With
End Sub
excel vba
asked Mar 22 at 8:23
Kenny BerglundKenny Berglund
61
I have an issue where i want to import value from a textfile into my excel-document which it does but when i run macro over and over to refresh said values with new ones it just moves it over and adds more, it needs to delete values and replace them, i didnt write this code myself so i cant understand the issue with it so here i am pleading for help
Sub ImportSaldo()
Call Shell("C:importGetFromFTP.bat", vbNormalFocus)
Dim fileName As String, folder As String
folder = "c:import"
fileName = "Saldot.txt"
ActiveCell.Offset(0, 0).Range("A1").Select
With ActiveSheet.QueryTables _
.Add(Connection:="TEXT;" & folder & fileName, Destination:=ActiveCell)
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.TextFilePromptOnRefresh = False
.TextFilePlatform = 850
.TextFileStartRow = 1
.TextFileParseType = xlDelimited
.TextFileTextQualifier = xlTextQualifierDoubleQuote
.TextFileConsecutiveDelimiter = False
.TextFileTabDelimiter = False
.TextFileSemicolonDelimiter = True
.TextFileCommaDelimiter = False
.TextFileSpaceDelimiter = False
.TextFileColumnDataTypes = Array(1, 1, 1, 1)
.TextFileTrailingMinusNumbers = True
.Refresh BackgroundQuery:=False
End With
End Sub
excel vba
excel vba
asked Mar 22 at 8:23
Kenny BerglundKenny Berglund
61
asked Mar 22 at 8:23
Kenny BerglundKenny Berglund
61
asked Mar 22 at 8:23
Kenny BerglundKenny Berglund
61
asked Mar 22 at 8:23
Kenny BerglundKenny Berglund
61
asked Mar 22 at 8:23
Kenny BerglundKenny Berglund
61
61
1
ActiveCell.Offset(0, 0).Range("A1").Selectis your problem here. If you want the existing code to be forced to load the data to A1, remove that line and changeDestination:=ActiveCellon your.Addline toDestination:=Range("A1").
– CLR
Mar 22 at 9:03
150 Opening BINARY mode data connection for saldot.txt (188178 Bytes). > Replace Existing File with Temp File:I/O Error 226 Transfer complete. 188 178 bytes transferred. 183,77 KB/sec. ftp: 188178 bytes received in 0,31Seconds 603,13Kbytes/sec. ftp> I/O Error and crashed Excel when i did your change.
– Kenny Berglund
Mar 22 at 11:39
add a comment |
1
ActiveCell.Offset(0, 0).Range("A1").Selectis your problem here. If you want the existing code to be forced to load the data to A1, remove that line and changeDestination:=ActiveCellon your.Addline toDestination:=Range("A1").
– CLR
Mar 22 at 9:03
150 Opening BINARY mode data connection for saldot.txt (188178 Bytes). > Replace Existing File with Temp File:I/O Error 226 Transfer complete. 188 178 bytes transferred. 183,77 KB/sec. ftp: 188178 bytes received in 0,31Seconds 603,13Kbytes/sec. ftp> I/O Error and crashed Excel when i did your change.
– Kenny Berglund
Mar 22 at 11:39
1
1
ActiveCell.Offset(0, 0).Range("A1").Select is your problem here. If you want the existing code to be forced to load the data to A1, remove that line and change Destination:=ActiveCell on your .Add line to Destination:=Range("A1").– CLR
Mar 22 at 9:03
ActiveCell.Offset(0, 0).Range("A1").Select is your problem here. If you want the existing code to be forced to load the data to A1, remove that line and change Destination:=ActiveCell on your .Add line to Destination:=Range("A1").– CLR
Mar 22 at 9:03
150 Opening BINARY mode data connection for saldot.txt (188178 Bytes). > Replace Existing File with Temp File:I/O Error 226 Transfer complete. 188 178 bytes transferred. 183,77 KB/sec. ftp: 188178 bytes received in 0,31Seconds 603,13Kbytes/sec. ftp> I/O Error and crashed Excel when i did your change.
– Kenny Berglund
Mar 22 at 11:39
150 Opening BINARY mode data connection for saldot.txt (188178 Bytes). > Replace Existing File with Temp File:I/O Error 226 Transfer complete. 188 178 bytes transferred. 183,77 KB/sec. ftp: 188178 bytes received in 0,31Seconds 603,13Kbytes/sec. ftp> I/O Error and crashed Excel when i did your change.
– Kenny Berglund
Mar 22 at 11:39
add a comment |
1 Answer
1
active
oldest
votes
0
.RefreshOnFileOpen = False change this to True
Once the file is opened it should clear the existing values in the ActiveSheet.
answered Mar 22 at 18:52
user3922728user3922728
13
This didnt work either, it just moves previous data to the right
– Kenny Berglund
Mar 25 at 7:44
Update LIne 4: ActiveSheet.Range("A1").Activate Comment out existing with an ' 'ActiveCell.Offset(0, 0).Range("A1").Select ~ you can always remove it to make the code active again. Happy to look at the file if you continue to have issues.
– user3922728
Mar 25 at 16:48
Still doing the same thing, thanks for the help but ill just manually update my warehouse stock-value file until later notice
– Kenny Berglund
Mar 26 at 14:46
add a comment |
Your Answer
StackExchange.ifUsing("editor", function ()
StackExchange.using("externalEditor", function ()
StackExchange.using("snippets", function ()
StackExchange.snippets.init();
);
);
, "code-snippets");
StackExchange.ready(function()
var channelOptions =
tags: "".split(" "),
id: "1"
;
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function()
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled)
StackExchange.using("snippets", function()
createEditor();
);
else
createEditor();
);
function createEditor()
StackExchange.prepareEditor(
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader:
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
,
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
);
);
draft saved
draft discarded
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f55295522%2fimporting-from-textfile-with-macro%23new-answer', 'question_page');
);
Post as a guest
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
0
.RefreshOnFileOpen = False change this to True
Once the file is opened it should clear the existing values in the ActiveSheet.
answered Mar 22 at 18:52
user3922728user3922728
13
This didnt work either, it just moves previous data to the right
– Kenny Berglund
Mar 25 at 7:44
Update LIne 4: ActiveSheet.Range("A1").Activate Comment out existing with an ' 'ActiveCell.Offset(0, 0).Range("A1").Select ~ you can always remove it to make the code active again. Happy to look at the file if you continue to have issues.
– user3922728
Mar 25 at 16:48
Still doing the same thing, thanks for the help but ill just manually update my warehouse stock-value file until later notice
– Kenny Berglund
Mar 26 at 14:46
add a comment |
0
.RefreshOnFileOpen = False change this to True
Once the file is opened it should clear the existing values in the ActiveSheet.
answered Mar 22 at 18:52
user3922728user3922728
13
This didnt work either, it just moves previous data to the right
– Kenny Berglund
Mar 25 at 7:44
Update LIne 4: ActiveSheet.Range("A1").Activate Comment out existing with an ' 'ActiveCell.Offset(0, 0).Range("A1").Select ~ you can always remove it to make the code active again. Happy to look at the file if you continue to have issues.
– user3922728
Mar 25 at 16:48
Still doing the same thing, thanks for the help but ill just manually update my warehouse stock-value file until later notice
– Kenny Berglund
Mar 26 at 14:46
add a comment |
0
0
0
.RefreshOnFileOpen = False change this to True
Once the file is opened it should clear the existing values in the ActiveSheet.
answered Mar 22 at 18:52
user3922728user3922728
13
.RefreshOnFileOpen = False change this to True
Once the file is opened it should clear the existing values in the ActiveSheet.
answered Mar 22 at 18:52
user3922728user3922728
13
answered Mar 22 at 18:52
user3922728user3922728
13
answered Mar 22 at 18:52
user3922728user3922728
13
answered Mar 22 at 18:52
user3922728user3922728
13
13
This didnt work either, it just moves previous data to the right
– Kenny Berglund
Mar 25 at 7:44
Update LIne 4: ActiveSheet.Range("A1").Activate Comment out existing with an ' 'ActiveCell.Offset(0, 0).Range("A1").Select ~ you can always remove it to make the code active again. Happy to look at the file if you continue to have issues.
– user3922728
Mar 25 at 16:48
Still doing the same thing, thanks for the help but ill just manually update my warehouse stock-value file until later notice
– Kenny Berglund
Mar 26 at 14:46
add a comment |
This didnt work either, it just moves previous data to the right
– Kenny Berglund
Mar 25 at 7:44
Update LIne 4: ActiveSheet.Range("A1").Activate Comment out existing with an ' 'ActiveCell.Offset(0, 0).Range("A1").Select ~ you can always remove it to make the code active again. Happy to look at the file if you continue to have issues.
– user3922728
Mar 25 at 16:48
Still doing the same thing, thanks for the help but ill just manually update my warehouse stock-value file until later notice
– Kenny Berglund
Mar 26 at 14:46
This didnt work either, it just moves previous data to the right
– Kenny Berglund
Mar 25 at 7:44
This didnt work either, it just moves previous data to the right
– Kenny Berglund
Mar 25 at 7:44
Update LIne 4: ActiveSheet.Range("A1").Activate Comment out existing with an ' 'ActiveCell.Offset(0, 0).Range("A1").Select ~ you can always remove it to make the code active again. Happy to look at the file if you continue to have issues.
– user3922728
Mar 25 at 16:48
Update LIne 4: ActiveSheet.Range("A1").Activate Comment out existing with an ' 'ActiveCell.Offset(0, 0).Range("A1").Select ~ you can always remove it to make the code active again. Happy to look at the file if you continue to have issues.
– user3922728
Mar 25 at 16:48
Still doing the same thing, thanks for the help but ill just manually update my warehouse stock-value file until later notice
– Kenny Berglund
Mar 26 at 14:46
Still doing the same thing, thanks for the help but ill just manually update my warehouse stock-value file until later notice
– Kenny Berglund
Mar 26 at 14:46
add a comment |
draft saved
draft discarded
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
draft saved
draft discarded
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f55295522%2fimporting-from-textfile-with-macro%23new-answer', 'question_page');
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
1
ActiveCell.Offset(0, 0).Range("A1").Selectis your problem here. If you want the existing code to be forced to load the data to A1, remove that line and changeDestination:=ActiveCellon your.Addline toDestination:=Range("A1").– CLR
Mar 22 at 9:03
150 Opening BINARY mode data connection for saldot.txt (188178 Bytes). > Replace Existing File with Temp File:I/O Error 226 Transfer complete. 188 178 bytes transferred. 183,77 KB/sec. ftp: 188178 bytes received in 0,31Seconds 603,13Kbytes/sec. ftp> I/O Error and crashed Excel when i did your change.
– Kenny Berglund
Mar 22 at 11:39