H
HaoHoaMastercard
Hi guys,
I am a newbe to Excel VBA, and so-so for general Excel use. I've been trying
to import tabulated text data into Excel for further processing but the
problem is that some of the "fields" in the text data row are empty, then
the summary row at the bottom do not always line up with the tabulated
values. The real problem is that these data files can get pretty big, some
with 5,000 pages long.
There are a couple of different ways to import these files. Using Excel
import wizard, I ended up with rows that have numbers chopped off because of
the number locations. The other method employs Notepad to copy into
clipboard before importing. This method uses space delimited and I could
avoid numbers getting chopped off, but because of the empty fields, the
cells get shifted. So, depends on the extent of the problem, I use both
methods to minimize the "damage". Still, I would have to fix the known
issues after importing.
Back to the "Notepad" method, there are some required manual steps in order
to get the file copied to clipboard (manually open Notepad, open the text
file, highlight, copy) before I could do importing.
I am wondering if you all know any ways to put all that into VBA code so
that the whole thing could get done simply by clicking on one button?
(assumming that I will have to supply the text file location beforehand to
the VBA code so that it would open the correct file).
The example VBA code I use for manual importing using Notepad is:
NotepadFile = Shell("C:\Windows\Notepad.exe", 4)
MsgBox "Use Notepad window to open table1.txt" & vbCrLf & _
"then copy the entire content to clipboard." & vbCrLf & vbCrLf & _
"When done, click OK to proceed with importing data file"
' (some action taken here: locate data file, open, CTRL-A to hightlight,
CTRL-C to copy, close Notepad)
Application.ScreenUpdating = False
Worksheets.Add(After:=Worksheets("Control Panel")).Name = "Table1"
Worksheets("Table1").Activate
ActiveSheet.Paste
.......
Thanks in advance.
DP
I am a newbe to Excel VBA, and so-so for general Excel use. I've been trying
to import tabulated text data into Excel for further processing but the
problem is that some of the "fields" in the text data row are empty, then
the summary row at the bottom do not always line up with the tabulated
values. The real problem is that these data files can get pretty big, some
with 5,000 pages long.
There are a couple of different ways to import these files. Using Excel
import wizard, I ended up with rows that have numbers chopped off because of
the number locations. The other method employs Notepad to copy into
clipboard before importing. This method uses space delimited and I could
avoid numbers getting chopped off, but because of the empty fields, the
cells get shifted. So, depends on the extent of the problem, I use both
methods to minimize the "damage". Still, I would have to fix the known
issues after importing.
Back to the "Notepad" method, there are some required manual steps in order
to get the file copied to clipboard (manually open Notepad, open the text
file, highlight, copy) before I could do importing.
I am wondering if you all know any ways to put all that into VBA code so
that the whole thing could get done simply by clicking on one button?
(assumming that I will have to supply the text file location beforehand to
the VBA code so that it would open the correct file).
The example VBA code I use for manual importing using Notepad is:
NotepadFile = Shell("C:\Windows\Notepad.exe", 4)
MsgBox "Use Notepad window to open table1.txt" & vbCrLf & _
"then copy the entire content to clipboard." & vbCrLf & vbCrLf & _
"When done, click OK to proceed with importing data file"
' (some action taken here: locate data file, open, CTRL-A to hightlight,
CTRL-C to copy, close Notepad)
Application.ScreenUpdating = False
Worksheets.Add(After:=Worksheets("Control Panel")).Name = "Table1"
Worksheets("Table1").Activate
ActiveSheet.Paste
.......
Thanks in advance.
DP