Hoi beste mensen,
Ik heb hier met beholpen van sommige liefhebbers een macro in elkaar te gezet, maar de punt is ik mis nog een optie.
Ik zal even vertellen wat ik wil zodat jullie ook tips of aandachts punten kunnen geven om de code te veranderen.
Er worden om de zoveel minuten .xls files geexporteerd in map C:temp. Standaard krijgt de file de naam scans_nummeriek dat wordt verhoogd met een.
De .xsl file bestaat uit 7 Sheets. Er zijn in elke sheet cellen waarvan ik de informatie nodig hebt, Alle deze cellen worden gekopierd en geplakt in een nieuw .xls file door de macro.
Het gene dat ik mis is, in sheet6 wil ik de informatie van A1:A9, maar tussen elke waarde dus A1, A2, A3, etc moet er twee kolom komen. Laten we zeggen dat tijdens het plakken van A1 in het nieuw map in kolom K komt, dan moet A2 in kolom N komen.
Alle hulp is welkom en alvast bedankt.:thumb:
Zie code hieronder:
Ik heb hier met beholpen van sommige liefhebbers een macro in elkaar te gezet, maar de punt is ik mis nog een optie.
Ik zal even vertellen wat ik wil zodat jullie ook tips of aandachts punten kunnen geven om de code te veranderen.
Er worden om de zoveel minuten .xls files geexporteerd in map C:temp. Standaard krijgt de file de naam scans_nummeriek dat wordt verhoogd met een.
De .xsl file bestaat uit 7 Sheets. Er zijn in elke sheet cellen waarvan ik de informatie nodig hebt, Alle deze cellen worden gekopierd en geplakt in een nieuw .xls file door de macro.
Het gene dat ik mis is, in sheet6 wil ik de informatie van A1:A9, maar tussen elke waarde dus A1, A2, A3, etc moet er twee kolom komen. Laten we zeggen dat tijdens het plakken van A1 in het nieuw map in kolom K komt, dan moet A2 in kolom N komen.
Alle hulp is welkom en alvast bedankt.:thumb:
Zie code hieronder:
Code:
Option Explicit
Sub GetScans()
Dim strFirstScan As String
Dim strSecondScan As String
Dim strLastScan As String
Dim blnRedo As Boolean
Dim strPath As String
Dim strBase As String
Dim strThisFilename As String
Dim strThisFile As String
Dim strDestFN As String
Dim intDestRowOffset As Integer
Dim intDestColOffset As Integer
Dim n As Integer
On Error GoTo ErrHnd
'stop screen flicker during copy and paste operations
Application.ScreenUpdating = False
'setup name of destination file - use the name of this workbook
strDestFN = ActiveWorkbook.Name
'setup Path to saved scan files - must end with \
strPath = "C:\Temp\"
'setup base name for scanned files (case sensitive)
strBase = "scans_"
'set first row offset for saving data (Offset 0 is row 1)
intDestRowOffset = 1
'get number of scan files
FirstS:
strFirstScan = InputBox("Enter first scan file number (4 digits)" & vbCrLf _
& "or enter 'Q' to quit", "First Scan")
'test that text is a four character string representing a number
blnRedo = False
If Len(strFirstScan) <> 4 Or Not IsNumeric(strFirstScan) Then
If strFirstScan = "Q" Or strFirstScan = "q" Then
Exit Sub
Else
blnRedo = True
End If
End If
'redo first number entry
If blnRedo = True Then GoTo FirstS
SecondS:
strSecondScan = InputBox("Enter Second scan file number (4 digits)" & vbCrLf _
& "or enter 'Q' to quit", "Second Scan")
'test that text is a four character string representing a number
blnRedo = False
If Len(strSecondScan) <> 4 Or Not IsNumeric(strSecondScan) Then
If strSecondScan = "Q" Or strSecondScan = "q" Then
Exit Sub
Else
blnRedo = True
End If
End If
'redo Second number entry
If blnRedo = True Then GoTo SecondS
'open each scan file in turn and copy information
For n = CInt(strFirstScan) To CInt(strSecondScan)
'set destination column offset for first column (0 = "A")
intDestColOffset = 0
'create file name
strThisFilename = strBase & Format(n, "0000") & ".xls"
strThisFile = strPath & strThisFilename
'open this file
Application.Workbooks.Open (strThisFile)
With Workbooks(strThisFilename)
.Worksheets("Sheet1").Range("A1:B1").Copy _
Destination:=Workbooks(strDestFN).Worksheets("Sheet1").Range("A1"). _
Offset(intDestRowOffset, intDestColOffset)
intDestColOffset = intDestColOffset + 2
.Worksheets("Sheet2").Range("B1").Copy _
Destination:=Workbooks(strDestFN).Worksheets("Sheet1").Range("A1"). _
Offset(intDestRowOffset, intDestColOffset)
intDestColOffset = intDestColOffset + 1
.Worksheets("Sheet2").Range("B2").Copy _
Destination:=Workbooks(strDestFN).Worksheets("Sheet1").Range("A1"). _
Offset(intDestRowOffset, intDestColOffset)
intDestColOffset = intDestColOffset + 1
.Worksheets("Sheet3").Range("A1:B1").Copy _
Destination:=Workbooks(strDestFN).Worksheets("Sheet1").Range("A1"). _
Offset(intDestRowOffset, intDestColOffset)
intDestColOffset = intDestColOffset + 2
.Worksheets("Sheet4").Range("B1").Copy _
Destination:=Workbooks(strDestFN).Worksheets("Sheet1").Range("A1"). _
Offset(intDestRowOffset, intDestColOffset)
intDestColOffset = intDestColOffset + 1
.Worksheets("Sheet4").Range("B2").Copy _
Destination:=Workbooks(strDestFN).Worksheets("Sheet1").Range("A1"). _
Offset(intDestRowOffset, intDestColOffset)
intDestColOffset = intDestColOffset + 1
.Worksheets("Sheet5").Range("B1").Copy _
Destination:=Workbooks(strDestFN).Worksheets("Sheet1").Range("A1"). _
Offset(intDestRowOffset, intDestColOffset)
intDestColOffset = intDestColOffset + 1
.Worksheets("Sheet6").Range("A1").Copy _
Destination:=Workbooks(strDestFN).Worksheets("Sheet1").Range("A1"). _
Offset(intDestRowOffset, intDestColOffset)
intDestColOffset = intDestColOffset + 1
.Worksheets("Sheet6").Range("B1").Copy _
Destination:=Workbooks(strDestFN).Worksheets("Sheet1").Range("A1"). _
Offset(intDestRowOffset, intDestColOffset)
intDestColOffset = intDestColOffset + 1
.Worksheets("Sheet6").Range("C1").Copy _
Destination:=Workbooks(strDestFN).Worksheets("Sheet1").Range("A1"). _
Offset(intDestRowOffset, intDestColOffset)
intDestColOffset = intDestColOffset + 1
.Worksheets("Sheet6").Range("A2").Copy _
Destination:=Workbooks(strDestFN).Worksheets("Sheet1").Range("A1"). _
Offset(intDestRowOffset, intDestColOffset)
intDestColOffset = intDestColOffset + 1
.Worksheets("Sheet6").Range("B2").Copy _
Destination:=Workbooks(strDestFN).Worksheets("Sheet1").Range("A1"). _
Offset(intDestRowOffset, intDestColOffset)
intDestColOffset = intDestColOffset + 1
.Worksheets("Sheet6").Range("C2").Copy _
Destination:=Workbooks(strDestFN).Worksheets("Sheet1").Range("A1"). _
Offset(intDestRowOffset, intDestColOffset)
intDestColOffset = intDestColOffset + 1
.Worksheets("Sheet6").Range("A3").Copy _
Destination:=Workbooks(strDestFN).Worksheets("Sheet1").Range("A1"). _
Offset(intDestRowOffset, intDestColOffset)
intDestColOffset = intDestColOffset + 1
.Worksheets("Sheet6").Range("B3").Copy _
Destination:=Workbooks(strDestFN).Worksheets("Sheet1").Range("A1"). _
Offset(intDestRowOffset, intDestColOffset)
intDestColOffset = intDestColOffset + 1
.Worksheets("Sheet6").Range("C3").Copy _
Destination:=Workbooks(strDestFN).Worksheets("Sheet1").Range("A1"). _
Offset(intDestRowOffset, intDestColOffset)
intDestColOffset = intDestColOffset + 1
.Worksheets("Sheet6").Range("A4").Copy _
Destination:=Workbooks(strDestFN).Worksheets("Sheet1").Range("A1"). _
Offset(intDestRowOffset, intDestColOffset)
intDestColOffset = intDestColOffset + 1
.Worksheets("Sheet6").Range("B4").Copy _
Destination:=Workbooks(strDestFN).Worksheets("Sheet1").Range("A1"). _
Offset(intDestRowOffset, intDestColOffset)
intDestColOffset = intDestColOffset + 1
.Worksheets("Sheet6").Range("C4").Copy _
Destination:=Workbooks(strDestFN).Worksheets("Sheet1").Range("A1"). _
Offset(intDestRowOffset, intDestColOffset)
intDestColOffset = intDestColOffset + 1
.Worksheets("Sheet6").Range("A5").Copy _
Destination:=Workbooks(strDestFN).Worksheets("Sheet1").Range("A1"). _
Offset(intDestRowOffset, intDestColOffset)
intDestColOffset = intDestColOffset + 1
.Worksheets("Sheet6").Range("B5").Copy _
Destination:=Workbooks(strDestFN).Worksheets("Sheet1").Range("A1"). _
Offset(intDestRowOffset, intDestColOffset)
intDestColOffset = intDestColOffset + 1
.Worksheets("Sheet6").Range("C5").Copy _
Destination:=Workbooks(strDestFN).Worksheets("Sheet1").Range("A1"). _
Offset(intDestRowOffset, intDestColOffset)
intDestColOffset = intDestColOffset + 1
.Worksheets("Sheet6").Range("A6").Copy _
Destination:=Workbooks(strDestFN).Worksheets("Sheet1").Range("A1"). _
Offset(intDestRowOffset, intDestColOffset)
intDestColOffset = intDestColOffset + 1
.Worksheets("Sheet6").Range("B6").Copy _
Destination:=Workbooks(strDestFN).Worksheets("Sheet1").Range("A1"). _
Offset(intDestRowOffset, intDestColOffset)
intDestColOffset = intDestColOffset + 1
.Worksheets("Sheet6").Range("C6").Copy _
Destination:=Workbooks(strDestFN).Worksheets("Sheet1").Range("A1"). _
Offset(intDestRowOffset, intDestColOffset)
intDestColOffset = intDestColOffset + 1
.Worksheets("Sheet6").Range("A7").Copy _
Destination:=Workbooks(strDestFN).Worksheets("Sheet1").Range("A1"). _
Offset(intDestRowOffset, intDestColOffset)
intDestColOffset = intDestColOffset + 1
.Worksheets("Sheet6").Range("B7").Copy _
Destination:=Workbooks(strDestFN).Worksheets("Sheet1").Range("A1"). _
Offset(intDestRowOffset, intDestColOffset)
intDestColOffset = intDestColOffset + 1
.Worksheets("Sheet6").Range("C7").Copy _
Destination:=Workbooks(strDestFN).Worksheets("Sheet1").Range("A1"). _
Offset(intDestRowOffset, intDestColOffset)
intDestColOffset = intDestColOffset + 1
.Worksheets("Sheet6").Range("A8").Copy _
Destination:=Workbooks(strDestFN).Worksheets("Sheet1").Range("A1"). _
Offset(intDestRowOffset, intDestColOffset)
intDestColOffset = intDestColOffset + 1
.Worksheets("Sheet6").Range("B8").Copy _
Destination:=Workbooks(strDestFN).Worksheets("Sheet1").Range("A1"). _
Offset(intDestRowOffset, intDestColOffset)
intDestColOffset = intDestColOffset + 1
.Worksheets("Sheet6").Range("C8").Copy _
Destination:=Workbooks(strDestFN).Worksheets("Sheet1").Range("A1"). _
Offset(intDestRowOffset, intDestColOffset)
intDestColOffset = intDestColOffset + 1
.Worksheets("Sheet6").Range("A9").Copy _
Destination:=Workbooks(strDestFN).Worksheets("Sheet1").Range("A1"). _
Offset(intDestRowOffset, intDestColOffset)
intDestColOffset = intDestColOffset + 1
.Worksheets("Sheet6").Range("B9").Copy _
Destination:=Workbooks(strDestFN).Worksheets("Sheet1").Range("A1"). _
Offset(intDestRowOffset, intDestColOffset)
intDestColOffset = intDestColOffset + 1
.Worksheets("Sheet6").Range("A1:A9").Copy
Workbooks(strDestFN).Worksheets("Sheet1").Range("A1"). _
Offset(intDestRowOffset, intDestColOffset).PasteSpecial _
Paste:=xlPasteAll, Transpose:=True
End With
'next row
intDestRowOffset = intDestRowOffset + 1
'close current source file
Workbooks(strThisFilename).Close SaveChanges:=False
Next n
'save this Destination workbook
Workbooks(strDestFN).Save
'reinstate screen updating
Application.ScreenUpdating = True
Exit Sub
'error handler
ErrHnd:
Err.Clear
'reinstate screen updating
Application.ScreenUpdating = True
End Sub
Laatst bewerkt: