Import text file data into excel using excel macro without removing preceding zero's from the cells in excel
0
I have this code for importing text file data into excel.
While importing I want to set number format to text. Using this code I am not able to do this. It is removing preceding zero's from the cells in excel.
I want prevent from removing preceding zero's through this code only.
Sub Test()
Dim xWb As Workbook
Dim xToBook As Workbook
Dim xStrPath As String
Dim xformat As Object
Dim xFileDialog As FileDialog
Dim xFile As String
Dim xFiles As New Collection
Dim I As Long
Set xFileDialog = Application.FileDialog(msoFileDialogFolderPicker)
xFileDialog.AllowMultiSelect = False
xFileDialog.Title = "Select a folder [Kutools for Excel]"
If xFileDialog.Show = -1 Then
xStrPath = xFileDialog.SelectedItems(1)
End If
If xStrPath = "" Then Exit Sub
If Right(xStrPath, 1) <> "" Then xStrPath = xStrPath & ""
xFile = Dir(xStrPath & "*.txt")
If xFile = "" Then
MsgBox "No files found", vbInformation, "Kutools for Excel"
Exit Sub
End If
Do While xFile <> ""
xFiles.Add xFile, xFile
xFile = Dir()
Loop
Set xToBook = ThisWorkbook
If xFiles.Count > 0 Then
For I = 1 To xFiles.Count
Set xWb = Workbooks.Open(xStrPath & xFiles.Item(I))
Worksheets(1).NumberFormat = "@"
xWb.Worksheets(1).Copy after:=xToBook.Sheets(xToBook.Sheets.Count)
On Error Resume Next
ActiveSheet.Name = xWb.Name
On Error GoTo 0
xWb.Close False
Next
End If
End Sub
microsoft-excel vba
edited Feb 15 at 12:52
Máté Juhász
14.6k63452
asked Feb 15 at 12:30
poojapooja
1
add a comment |
0
I have this code for importing text file data into excel.
While importing I want to set number format to text. Using this code I am not able to do this. It is removing preceding zero's from the cells in excel.
I want prevent from removing preceding zero's through this code only.
Sub Test()
Dim xWb As Workbook
Dim xToBook As Workbook
Dim xStrPath As String
Dim xformat As Object
Dim xFileDialog As FileDialog
Dim xFile As String
Dim xFiles As New Collection
Dim I As Long
Set xFileDialog = Application.FileDialog(msoFileDialogFolderPicker)
xFileDialog.AllowMultiSelect = False
xFileDialog.Title = "Select a folder [Kutools for Excel]"
If xFileDialog.Show = -1 Then
xStrPath = xFileDialog.SelectedItems(1)
End If
If xStrPath = "" Then Exit Sub
If Right(xStrPath, 1) <> "" Then xStrPath = xStrPath & ""
xFile = Dir(xStrPath & "*.txt")
If xFile = "" Then
MsgBox "No files found", vbInformation, "Kutools for Excel"
Exit Sub
End If
Do While xFile <> ""
xFiles.Add xFile, xFile
xFile = Dir()
Loop
Set xToBook = ThisWorkbook
If xFiles.Count > 0 Then
For I = 1 To xFiles.Count
Set xWb = Workbooks.Open(xStrPath & xFiles.Item(I))
Worksheets(1).NumberFormat = "@"
xWb.Worksheets(1).Copy after:=xToBook.Sheets(xToBook.Sheets.Count)
On Error Resume Next
ActiveSheet.Name = xWb.Name
On Error GoTo 0
xWb.Close False
Next
End If
End Sub
microsoft-excel vba
edited Feb 15 at 12:52
Máté Juhász
14.6k63452
asked Feb 15 at 12:30
poojapooja
1
2
80% of your code is not relevant to your question, please keep only what's relevant. Your title says you want to import from text file, but code implies you copy from another workbook, please clarify.
– Máté Juhász
Feb 15 at 12:55
Have you searched for this? What have you tried? There's even a support.office.com entry on this.
– BruceWayne
Feb 15 at 15:08
add a comment |
0
0
0
I have this code for importing text file data into excel.
While importing I want to set number format to text. Using this code I am not able to do this. It is removing preceding zero's from the cells in excel.
I want prevent from removing preceding zero's through this code only.
Sub Test()
Dim xWb As Workbook
Dim xToBook As Workbook
Dim xStrPath As String
Dim xformat As Object
Dim xFileDialog As FileDialog
Dim xFile As String
Dim xFiles As New Collection
Dim I As Long
Set xFileDialog = Application.FileDialog(msoFileDialogFolderPicker)
xFileDialog.AllowMultiSelect = False
xFileDialog.Title = "Select a folder [Kutools for Excel]"
If xFileDialog.Show = -1 Then
xStrPath = xFileDialog.SelectedItems(1)
End If
If xStrPath = "" Then Exit Sub
If Right(xStrPath, 1) <> "" Then xStrPath = xStrPath & ""
xFile = Dir(xStrPath & "*.txt")
If xFile = "" Then
MsgBox "No files found", vbInformation, "Kutools for Excel"
Exit Sub
End If
Do While xFile <> ""
xFiles.Add xFile, xFile
xFile = Dir()
Loop
Set xToBook = ThisWorkbook
If xFiles.Count > 0 Then
For I = 1 To xFiles.Count
Set xWb = Workbooks.Open(xStrPath & xFiles.Item(I))
Worksheets(1).NumberFormat = "@"
xWb.Worksheets(1).Copy after:=xToBook.Sheets(xToBook.Sheets.Count)
On Error Resume Next
ActiveSheet.Name = xWb.Name
On Error GoTo 0
xWb.Close False
Next
End If
End Sub
microsoft-excel vba
edited Feb 15 at 12:52
Máté Juhász
14.6k63452
asked Feb 15 at 12:30
poojapooja
1
I have this code for importing text file data into excel.
While importing I want to set number format to text. Using this code I am not able to do this. It is removing preceding zero's from the cells in excel.
I want prevent from removing preceding zero's through this code only.
Sub Test()
Dim xWb As Workbook
Dim xToBook As Workbook
Dim xStrPath As String
Dim xformat As Object
Dim xFileDialog As FileDialog
Dim xFile As String
Dim xFiles As New Collection
Dim I As Long
Set xFileDialog = Application.FileDialog(msoFileDialogFolderPicker)
xFileDialog.AllowMultiSelect = False
xFileDialog.Title = "Select a folder [Kutools for Excel]"
If xFileDialog.Show = -1 Then
xStrPath = xFileDialog.SelectedItems(1)
End If
If xStrPath = "" Then Exit Sub
If Right(xStrPath, 1) <> "" Then xStrPath = xStrPath & ""
xFile = Dir(xStrPath & "*.txt")
If xFile = "" Then
MsgBox "No files found", vbInformation, "Kutools for Excel"
Exit Sub
End If
Do While xFile <> ""
xFiles.Add xFile, xFile
xFile = Dir()
Loop
Set xToBook = ThisWorkbook
If xFiles.Count > 0 Then
For I = 1 To xFiles.Count
Set xWb = Workbooks.Open(xStrPath & xFiles.Item(I))
Worksheets(1).NumberFormat = "@"
xWb.Worksheets(1).Copy after:=xToBook.Sheets(xToBook.Sheets.Count)
On Error Resume Next
ActiveSheet.Name = xWb.Name
On Error GoTo 0
xWb.Close False
Next
End If
End Sub
microsoft-excel vba
microsoft-excel vba
edited Feb 15 at 12:52
Máté Juhász
14.6k63452
asked Feb 15 at 12:30
poojapooja
1
edited Feb 15 at 12:52
Máté Juhász
14.6k63452
asked Feb 15 at 12:30
poojapooja
1
edited Feb 15 at 12:52
Máté Juhász
14.6k63452
edited Feb 15 at 12:52
Máté Juhász
14.6k63452
edited Feb 15 at 12:52
Máté Juhász
14.6k63452
14.6k63452
asked Feb 15 at 12:30
poojapooja
1
asked Feb 15 at 12:30
poojapooja
1
asked Feb 15 at 12:30
poojapooja
1
1
2
80% of your code is not relevant to your question, please keep only what's relevant. Your title says you want to import from text file, but code implies you copy from another workbook, please clarify.
– Máté Juhász
Feb 15 at 12:55
Have you searched for this? What have you tried? There's even a support.office.com entry on this.
– BruceWayne
Feb 15 at 15:08
add a comment |
2
80% of your code is not relevant to your question, please keep only what's relevant. Your title says you want to import from text file, but code implies you copy from another workbook, please clarify.
– Máté Juhász
Feb 15 at 12:55
Have you searched for this? What have you tried? There's even a support.office.com entry on this.
– BruceWayne
Feb 15 at 15:08
2
2
80% of your code is not relevant to your question, please keep only what's relevant. Your title says you want to import from text file, but code implies you copy from another workbook, please clarify.
– Máté Juhász
Feb 15 at 12:55
80% of your code is not relevant to your question, please keep only what's relevant. Your title says you want to import from text file, but code implies you copy from another workbook, please clarify.
– Máté Juhász
Feb 15 at 12:55
Have you searched for this? What have you tried? There's even a support.office.com entry on this.
– BruceWayne
Feb 15 at 15:08
Have you searched for this? What have you tried? There's even a support.office.com entry on this.
– BruceWayne
Feb 15 at 15:08
add a comment |
0
active
oldest
votes
Your Answer
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "3"
};
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%2fsuperuser.com%2fquestions%2f1406080%2fimport-text-file-data-into-excel-using-excel-macro-without-removing-preceding-ze%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
0
active
oldest
votes
0
active
oldest
votes
active
oldest
votes
active
oldest
votes
draft saved
draft discarded
Thanks for contributing an answer to Super User!
- 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%2fsuperuser.com%2fquestions%2f1406080%2fimport-text-file-data-into-excel-using-excel-macro-without-removing-preceding-ze%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
2
80% of your code is not relevant to your question, please keep only what's relevant. Your title says you want to import from text file, but code implies you copy from another workbook, please clarify.
– Máté Juhász
Feb 15 at 12:55
Have you searched for this? What have you tried? There's even a support.office.com entry on this.
– BruceWayne
Feb 15 at 15:08